Select table as json array

Question

I have three tables in PostgreSQL: A, B, C.
I want to get a row from table A with a specific id, plus all records from tables B and C with matching id as aggregated JSON.

For example:

Table A                     Table B                Table C
---------------------------------------------------------------
id / colum1  / colum2       id/  colum 1          id / column1
1  someValue, somValue      1   someVal1           1   someVal1
                            1   someVal2           1   someVal2

The expected output for id = 1 would be:

a.column1   a.column2       ARRAY_JSON_B              ARRAY_JSON_C       
------------------------------------------------------------------------------
someValue    someValue   [{colum1:'someVal1'},       [{colum1:'someVal1'},
                         {colum1:'someVal2'}]        {colum1:'someVal2'}]

score 2 · Answer 1 · answered Nov 03 '15 at 19:19

2

select
        a.*,
        to_json(array(select b from b where b.id = a.id)) array_json_b,
        to_json(array(select c from c where c.id = a.id)) array_json_c
    from a
        where
            a.id = 1;

I hope your Postgresql version is 9.3 or higher. There is a clever function to_json which can convert anything to json. So we take an array of all related rows from b and convert it. Same with c.

answered Nov 03 '15 at 19:19

stas.yaranov

1,797
10
17

Yes, It worked for me!! You save me hours finding and trying! Thank You!! – Jose Hermosilla Rodrigo Nov 03 '15 at 19:27

Erwin Brandstetter · Accepted Answer · 2022-05-05T02:47:19.820

^{This requires Postgres 9.3 or later.}

Simple case

I suggest to use the simpler json_agg() that's meant for this purpose, in LATERAL joins:

SELECT *
FROM   a
LEFT JOIN LATERAL (SELECT json_agg(b) AS array_json_b FROM b WHERE id = a.id) b ON true
LEFT JOIN LATERAL (SELECT json_agg(c) AS array_json_c FROM c WHERE id = a.id) c ON true
WHERE  id = 1;

LEFT JOIN LATERAL ... ON true keeps rows in the result that have no match on the left side of the join. Details:

What is the difference between LATERAL JOIN and a subquery in PostgreSQL?

Subtle difference: This query returns NULL where no match is found in b or c, @stas' query with correlated subqueries returns an empty array instead. May or may not be important.

Actual answer

Your example in the question excludes the redundant id column in b and c from the result - which makes sense. To achieve this, you can't use @stas' simple correlated subquery. While it would still work for a single column instead of the whole row, it would lose the column name and produce a simple array. Also, it would not work for more than one column.

Use json_object_agg() for a single selected column (which also allows to chose the tag name freely):

SELECT *
FROM   a
LEFT   JOIN LATERAL (
   SELECT json_object_agg('colum1', colum1) AS array_json_b
   FROM   b WHERE id = a.id
   ) b ON true
LEFT   JOIN LATERAL (
   SELECT json_object_agg('colum1', colum1) AS array_json_c
   FROM   c WHERE id = a.id
   ) c ON true
WHERE  id = 1;

Or use a subselect for any selection (col1 and col2 in this example):

SELECT *
FROM   a
LEFT   JOIN LATERAL (
   SELECT json_agg(x) AS array_json_b
   FROM  (SELECT col1, col2 FROM b WHERE id = a.id) x
   ) b ON true
LEFT   JOIN LATERAL (
   SELECT json_agg(x) AS array_json_c
   FROM  (SELECT col1, col2 FROM c WHERE id = a.id) x
   ) c ON true
WHERE  id = 1;

Select table as json array

2 Answers2

Simple case

Actual answer