json - 如何从查询中返回一个 json 数组？

Question

在 postgres 10 数据库中，有三个表:

主要:

id | name
-----------
1  | first
2  | second
3  | third
…

物质

id  | name
----------------------
1   | gold
2   | silver
3   | aluminum
…

链接

id  | id_main  | id_substance
---------------------------------
1   | 1        | 1
2   | 1        | 2
3   | 1        | 3
4   | 2        | 1
5   | 3        | 2
6   | 3        | 3

如何查询返回这样的json对象？

[
  {
    "name": "first",
    "substances": ["gold", "silver", "aluminum"]
  },
  {
    "name": "second",
    "substances": ["gold"]
   },
   {
    "name": "third",
    "substances": ["silver", "aluminum"]
   }
]

Answer 1

使用聚合函数jsonb_agg()两次在两个级别上构建 json 数组:

select jsonb_agg(a_row)
from (
    select 
        jsonb_build_object(
            'name', main_name, 
            'substances', jsonb_agg(substances_name)
        ) as a_row
    from (
        select m.name as main_name, s.name as substances_name
        from link l
        left join main m on id_main = m.id
        left join substances s on id_substances = s.id
        ) s
    group by main_name
    ) s;

(格式化的)结果:

[
    {
        "name": "first",
        "substances": ["gold", "silver", "aluminum"]
    },
    {
        "name": "third",
        "substances": ["silver", "aluminum"]
    },
    {
        "name": "second",
        "substances": ["gold"]
    }
]