json - postgresql 组列到 json 包含数组

Question

表名e

id | name | cate | link  
---+------+------+---------
1  | a    |  A   | link1
2  | a    |  B   | link2
3  | a    |  B   | link3
4  | b    |  B   | link4
5  | c    |  A   | link5
6  | d    |  A   | link6
7  | e    |  B   | link7

我想要结果:

name | A                        | B
------+------+-------------------+------------
a    |  {id: 1, link: 'link1'}  | [{id: 2, link: 'link2'}, {id: 3, link: 'link3'}]
b    |                          | [{id: 4, link: 'link4'}]
c    |  {id: 5, link: 'link5'}  |
d    |  {id: 6, link: 'link6'}  |
e    |                          | [{id: 7, link: 'link7'}]

cate字段值只有A,B,C,D。但值是 B 必须是一个数组

我的实验失败了

select name, format('{%s}', string_agg(format('"id": "%s", "name": "%s", "link":"%s"', id, name, link), ','))::json as A from elements where cate = 'A' group by name;
select name, string_to_array(format('[link: "%s", id: "%s", name: "%s"]', link, id, name)) as B from elements where cate = 'B' group by name;
select name, format('{%s}', string_agg(format('"id": "%s", "name": "%s", "link":"%s"', id, name, link), ','))::json as C from elements where cate = 'C' group by name;
select name, format('{%s}', string_agg(format('"id": "%s", "name": "%s", "link":"%s"', id, name, link), ','))::json as D from elements where cate = 'D' group by name;

如果加入其他表p

id | e_id | role_id 
---+------+----------
1  | 1    |  100
2  | 3    |  101
3  | 4    |  102
4  | 5    |  103

结果:

name   | checked | A                                     | B
-------+---------+---------------------------------------+-----------------------------------------------------------------
a      |   true  | {id: 1, link: 'link1', checked: true} | [{id: 2, link: 'link2'}, {id: 3, link: 'link3', checked: true}]
b      |   true  |                                       | [{id: 4, link: 'link4', checked: true}]
c      |   true  | {id: 5, link: 'link5', checked: true} |
d      |         | {id: 6, link: 'link6'}                |
e      |         |                                       | [{id: 7, link: 'link7'}]

Answer 1

首先不要使用format函数来创建json字段，PostgreSQL拥有解析和创建json字段所需的所有函数。您没有发布示例 ddl 和数据，但您的查询应如下所示:

select name, 
   json_agg(case cate when 'A' then json_build_object('id',id,'link',link) end) A, 
   json_agg(case cate when 'B' then json_build_object('id',id,'link',link) end) B 
from data group by name