sql - postgresql 1 到 n 关系到 json

Question

长话短说，我如何使用 1 到 n 选择数据来构建 json，如示例所示:

SELECT table1.id AS id1,table2.id AS id2,t_id,label 
FROM table1 LEFT JOIN table2 ON table2.t_id = table1.id 

result 
|id1|id2|t_id|label|
+---+---+----+-----+
|1  | 1 | 1  | a   |
|   | 2 | 1  | b   |
|   | 3 | 1  | c   |
|   | 4 | 1  | d   |
|2  | 5 | 2  | x   |
|   | 6 | 2  | y   |

变成这个

 SELECT table1.id, build_json(table2.id,table2.label) AS json_data 
 FROM table1 JOIN table2 ON table2.t_id = table1.id  
 GROUP BY table1.id

|id1|json_data
+--+-----------------
|1 |{"1":"a","2":"b","3":"c","4":"d"}
|2 |{"5":"x","6":"y"}

我猜最好的开始是从列构建数组

用 Hstore 代替 json 也行

Answer 1

你的表格结构有点奇怪(看起来更像报告而不是表格)，所以我在这里看到两个任务:

用正确的 id1 替换空值。你可以这样做

with cte1 as (
    select
        sum(case when id1 is null then 0 else 1 end) over (order by t_id) as id1_partition,
        id1, id2, label
    from Table1  
), cte2 as (
    select
        first_value(id1) over(partition by id1_partition) as id1,
        id2, label
    from cte1
)
select *
from cte2

现在您必须聚合数据到json。据我所知，PostgreSQL 中没有这样的函数，因此您必须手动连接数据:

with cte1 as (
    select
        sum(case when id1 is null then 0 else 1 end) over (order by t_id) as id1_partition,
        id1, id2, label
    from Table1  
), cte2 as (
    select
        first_value(id1) over(partition by id1_partition) as id1,
        id2, label
    from cte1
)
select
   id1,
   ('{' || string_agg('"' || id2 || '":' || to_json(label), ',') || '}')::json as json_data
from cte2
group by id1

sql fiddle demo

如果你想转换成hstore:

with cte1 as (
    select
        sum(case when id1 is null then 0 else 1 end) over (order by t_id) as id1_partition,
        id1, id2, label
    from Table1  
), cte2 as (
    select
        first_value(id1) over(partition by id1_partition) as id1,
        id2, label
    from cte1
)
select
    c.id1, hstore(array_agg(c.id2)::text[], array_agg(c.label)::text[])
from cte2 as c
group by c.id1

sql fiddle demo