Joining tables and creating a json out of the joined information

Question

Is there a way to join 2 tables with one query to DB in a way when records from one table are put as an array value in a 'new' column in another table?

(It's clear how to do it with 2 queries to both tables and processing results in code, but is there a way to use only one SELECT with joining the tables "during" the query?)

So, here is a simple example:

Table 1:

id	value
1	v1
2	v2

Table 2:

id	id_t1	value
1	1	v3
2	1	v4
3	2	v5

As a query result of selecting all the values from Table 1 joined with Table 2 there should be the next array of objects (to make the example more general id_t1 from Table 2 is filtered from the joined results):

[
  {
    id: 1,
    value: v1,
    newColumnForJoinedValuesFromTable2: [ { id: 1, value: v3 }, { id: 2, value: v4} ]
  },
  {
    id: 2,
    value: v2,
    newColumnForJoinedValuesFromTable2: [ { id: 3, value: v5 } ]
  }
]

Answer 1

You can achieve your json by stacking twice the following functions:

• JSON_BUILD_OBJECT, to build your jsons, given <key,value> pairs
• JSON_AGG, to aggregate your arrays

WITH tab2_agg AS (
    SELECT id_t1, 
           JSON_AGG(
               JSON_BUILD_OBJECT('id'   , id_, 
                                 'value', value_)
           ) AS tab2_json
    FROM tab2
    GROUP BY id_t1
)
SELECT JSON_AGG(
           JSON_BUILD_OBJECT('id'                                ,       id_,
                             'value'                             ,    value_,
                             'newColumnForJoinedValuesFromTable2', tab2_json)
       ) AS your_json
FROM       tab1
INNER JOIN tab2_agg
        ON tab1.id_ = tab2_agg.id_t1

Check the demo here.