amazon-redshift - 在 Redshift 中将表格数据转换为 JSON

Question

我无法弄清楚如何将表格数据转换为 JSON 格式并将其存储在 Redshift 的另一个表中。例如，我有一个包含四列的 "DEMO" 表:pid,stid,item_id,trans_id。

对于 pid,stid,item_id 的每个组合，都存在许多 trans_id。

pid  stid  item_id  trans_id :

1 , AB  , P1 , T1  
1 , AB  , P1 , T2  
1 , AB  , P1 , T3     
1 , AB  , P1 , T4   
2 , ABC , P2 , T5  
2 , ABC , P2 , T6  
2 , ABC , P2 , T7  
2 , ABC , P2 , T8

我想将此数据存储在另一个名为 "SAMPLE" 的表中:

pid  stid  item_id      trans_id

1 , AB  , P1 , {"key1":T1, "key2":"T2" "key2":"T3" "key2":"T4"}    
2 , ABC , P2 , {"key1":T5, "key2":"T6" "key2":"T7" "key2":"T8"}

我无法弄清楚如何将数据从 "DEMO" 加载到 "SAMPLE" 中，仅针对列 "trans_id" 在 Redshift 中使用 SQL 查询。我不想使用任何中间文件。

Answer 1

LISTAGG 聚合函数允许您在组内连接文本值。它允许有效构造 JSON 对象:

SELECT
 pid
,stid
,item_id
,'{'||listagg(
    '"key'||row_number::varchar||'":'||trans_id::varchar
    ,',') within group (order by row_number)
 ||'}'
FROM (
    SELECT *, row_number() over (partition by pid,stid,item_id order by trans_id)
    FROM "DEMO"
)
GROUP BY 1,2,3;

作为旁注，在这种特殊情况下，一组交易 ID 可能会更好，您将能够轻松请求特定订单的元素，而无需使用 keyN 键:

WITH tran_arrays as (
    SELECT
     pid
    ,stid
    ,item_id
    ,listagg(trans_id::varchar,',') within group (order by trans_id) as tran_array
    FROM "DEMO"
    GROUP BY 1,2,3
)
SELECT *
,split_part(tran_array,',',1) as first_element
FROM tran_arrays;