POSTGRESQL multiple select, multiple row arrays possible?

Question

Say I'm doing something simple like selecting everything from two tables:

"SELECT * FROM table1; SELECT * FROM table2;"

It will return a results object that looks like:

{rows:[{},{},{} etc...]}

Where the array of row objects is every row from table1, followed by every row from table2.

What I want is to be able to combine the above SELECT statements into one DB query but have it return something like:

[
{table1rows:[{},{},{}]}
,{table2rows:[{},{},{}]}
]

...so that I can avoid multiple queries of the DB while then fudging each query's results object into a custom object I reference from DB query to DB query like a caveman. So one query, one clean results set I can handle on the client knowing which array of rows came from which table.

Can you use AS for this? How would I achieve this with one query?

Answer 1

You basically need to:
- use combine multiple queries with UNION ALL;
- build json array with json_build_object and json_agg functions;

Example SELECT can be the following:

WITH table1(a1,b1) AS ( VALUES
  ('valA1','valB1')
), table2(a2,b2) AS ( VALUES
  ('valA2','valB2')
)
SELECT json_agg(each_table_rows.data) FROM (
  SELECT json_build_object('table1rows',tbl1.*) AS data
  FROM (
    SELECT t1.* FROM table1 t1
  ) tbl1
  UNION ALL
  SELECT json_build_object('table2rows',tbl2.*)
  FROM (
    SELECT t2.* FROM table2 t2
  ) tbl2
) each_table_rows;

Result:

                                           json_agg                                           
----------------------------------------------------------------------------------------------
 [{"table1rows" : {"a1":"valA1","b1":"valB1"}}, {"table2rows" : {"a2":"valA2","b2":"valB2"}}]
(1 row)