在雪花上创建一个新的嵌套JSON专栏

Question

我正在尝试将雪花表中的几列转换为嵌套的JSON，并尝试了Object_Construct＆amp; 但是，无法创建嵌套的JSON

输入

ID	product_1	Product_1	Products_2	PRODUCT_2_PCHERASE_DATE
100	XCTMR	01/02/2003	-	array_construct
product_1_purchase_date		： 2016	XCTMR	09/09/2021

输出：

ID	JSON_COMBIND
100	[{“ PRODECT_1”：{“ NAME”：“ XCTMR”，“ PRODECT_1_PURCHASE_DATE”：“ 01/02/2003”}}，{01/02/2003“}}，{ product_2“：{“ name”：“ iopwer”，“ product_2_purchase_date”：“ 01/02/2005”}}}]
200	[{“ produck_1”：{“ name”：“ aqwyu”：“ aqwyu”，product_1_purchase_date“：” 11/20 /2016“}}，{“ product_2”：{“ name”：“ xctmr”，“ product_2_purchase_date”：“ 09/09/2021”}}]]

Answer 1

解决方案，仅使用硬编码字段名称，只需要放置功能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列的数量。我们还可以在此处添加一个子问题来进行此计算，但是出于该解决方案的目的，我将其保留为硬编码。
  CTE_1还在执行undivot产品相关的列的主要作业中，以进行动态分组。
• 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.