我正试图将雪花表中的几列转换为嵌套的JSON，并尝试过OBJECT_CONSTRUCT&ARRAY_CONSTRUCT-但是，无法创建嵌套JSON
输入:
| id | product_1 | product_1_purchase_date | product_2 | product_2_purchase_date | 100 | XCTMR | 01/02/2003 | IOPWER | 01/02/2005 |
|---|---|---|---|---|---|
| 200 | AQWYU | 11/20/2016 | XCTMR | 09/09/2021 |
解决方案,与硬编码字段名称,只需要把函数ARRAY_CONSTRUCT和OBJECT_CONSTRUCT放在适当的顺序。
with cte(id,product_1,product_1_purchase_date,product_2,product_2_purchase_date) as
(select * from values
(100,'XCTMR','01/02/2003','IOPWER','01/02/2005'),
(200,'AQWYU','11/20/2016','XCTMR','09/09/2021')
)select
id,
array_construct(object_construct('product_1',
object_construct('name',product_1,'product_1_purchase_date',
product_1_purchase_date)),
object_construct('product_2',
object_construct('name',product_2,'product_2_purchase_date',
product_2_purchase_date)) ) as JSON_COMBINED
from cte;
+-----+-----------------------------------------------+
| ID | JSON_COMBINED |
|-----+-----------------------------------------------|
| 100 | [ |
| | { |
| | "product_1": { |
| | "name": "XCTMR", |
| | "product_1_purchase_date": "01/02/2003" |
| | } |
| | }, |
| | { |
| | "product_2": { |
| | "name": "IOPWER", |
| | "product_2_purchase_date": "01/02/2005" |
| | } |
| | } |
| | ] |
| 200 | [ |
| | { |
| | "product_1": { |
| | "name": "AQWYU", |
| | "product_1_purchase_date": "11/20/2016" |
| | } |
| | }, |
| | { |
| | "product_2": { |
| | "name": "XCTMR", |
| | "product_2_purchase_date": "09/09/2021" |
| | } |
| | } |
| | ] |
+-----+-----------------------------------------------+
下面是更多的动态-
- CTE为纯数据。
- CTE_1正在创建两个伪列，一个用于字段名product_x和另一个作为分组标准后在
OBJECT_AGG我们不能在另一个聚合函数中使用聚合函数，所以我们需要进行多次聚合(这里是两次)。将此CTE中的除以2更改为product_x列的个数。我们还可以在这里添加一个子查询来执行此计算，但出于此解决方案的目的，我将其保留为硬编码。CTE_1UNPIVOT产品的主要工作也是做相关专栏为动态分组行。 - CTE_2是聚合号- 1，输出如下所示(显示截断输出)。最后的查询执行主
ARRAY_AGG.
+-----+-----------+----------------------------------------------+
| ID | RN | JSON_COMBINED_1 |
|-----+-----------+----------------------------------------------|
| 100 | product_1 | { |
| | | "product_1": { |
| | | "PRODUCT_1_PURCHASE_DATE": "01/02/2003", |
| | | "name": "XCTMR" |
| | | } |
| | | } |
| 200 | product_2 | { |
| | | "product_2": { |
| | | "PRODUCT_2_PURCHASE_DATE": "09/09/2021", |
| | | "name": "XCTMR" |
| | | } |
| | | } |
+-----+-----------+----------------------------------------------+
主查询-
with cte(id,product_1,product_1_purchase_date,product_2,product_2_purchase_date) as
(select * from values
(100,'XCTMR','01/02/2003','IOPWER','01/02/2005'),
(200,'AQWYU','11/20/2016','XCTMR','09/09/2021')
),cte_1 as (
select id,
case when regexp_like(product_field,'product_[[:digit:]]+','i')
then 'name' else product_field end p_field,
product_val,
concat('product_',
to_char(ceil(row_number() over (partition by id order by null)/2))) rn
from cte
unpivot (product_val for product_field in (product_1,product_1_purchase_date,
product_2,product_2_purchase_date))
), cte_2 as (
select id,rn,
object_construct(rn,object_agg(p_field,to_variant(product_val))) JSON_COMBINED_1
from cte_1
group by id,rn
) select id, array_agg(JSON_COMBINED_1) JSON_COMBINED
from cte_2
group by id
order by id;
上面查询的最终输出(带动态)-
+-----+------------------------------------------------+
| ID | JSON_COMBINED |
|-----+------------------------------------------------|
| 100 | [ |
| | { |
| | "product_1": { |
| | "PRODUCT_1_PURCHASE_DATE": "01/02/2003", |
| | "name": "XCTMR" |
| | } |
| | }, |
| | { |
| | "product_2": { |
| | "PRODUCT_2_PURCHASE_DATE": "01/02/2005", |
| | "name": "IOPWER" |
| | } |
| | } |
| | ] |
| 200 | [ |
| | { |
| | "product_2": { |
| | "PRODUCT_2_PURCHASE_DATE": "09/09/2021", |
| | "name": "XCTMR" |
| | } |
| | }, |
| | { |
| | "product_1": { |
| | "PRODUCT_1_PURCHASE_DATE": "11/20/2016", |
| | "name": "AQWYU" |
| | } |
| | } |
| | ] |
+-----+------------------------------------------------+
2 Row(s) produced.