Question 90005 in PostgreSQL Question posted in
The official documentation can be found here.

Joining tables and creating a json out of the joined information – Postgresql

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 } ]
  }
]
Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    Use json_agg(json_build_object(...)) and group by.

    select json_agg(to_json(t)) as json_result from
(                
 select t1.id, t1.value,
   json_agg(json_build_object('id',t2.id,'value',t2.value)) as "JoinedValues" 
 from t1 join t2 on t2.id_t1 = t1.id
 group by t1.id, t1.value
) as t;

    See demo

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search