How to return a json array from a query?

Question

In a postgres 10 database, there are three tables:

main:

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

substances

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

link

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

How to make query to return a json object like this?

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

Answer 1

Use the aggregate function jsonb_agg() twice to build json arrays on two levels:

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;

The (formatted) result:

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