红移嵌套 json 提取

Question

我有一个包含两列的表，一列名为user，一列名为js的 json 列，如下所示：

{"1":{"partner_id":54,"provider_id":13},
"2":{"partner_id":56,"provider_id":8},
"3":{"partner_id":2719,"provider_id":274}}

我想在一列/行中选择所有"provider_id"。所以它应该看起来像这样：

user| provider_ids
0001| 13,8,274
0002| 21,36,57,12

我该怎么做？提前感谢！

Answer 1

您提供的 json 格式并不容易使用。

用于测试目的的板箱工作台：

create table json_test as
select '0001' as usr, '{"1":{"partner_id":54,"provider_id":13},
"2":{"partner_id":56,"provider_id":8},
"3":{"partner_id":2719,"provider_id":274}}'
as json_text
union all
select '0002' as usr, '{"1":{"partner_id":54,"provider_id":21},
"2":{"partner_id":56,"provider_id":36},
"2":{"partner_id":56,"provider_id":57},
"3":{"partner_id":2719,"provider_id":12}}'
as json_text;

查询返回结果：

with NS AS (
select 1 as n union all
select 2 union all
select 3 union all
select 4 union all
select 5 union all
select 6 union all
select 7 union all
select 8 union all
select 9 union all
select 10
)
select usr,
listagg(trim(TRIM(split_part(SPLIT_PART(js.json_text, '},', NS.n),'"provider_id":',2)),'}'),',') within group(order by null) AS t
from NS
join json_test js ON true and NS.n <= REGEXP_COUNT(js.json_text, '\},') + 1
group by usr;

笔记：

1)不要将列命名为"用户"，因为它是保留关键字

2) 在 NS 子查询中添加与最大 JSON 提供程序记录数一样多的虚拟行

3)是的，我知道，这不是非常可读的SQL:D