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?

You can use `UNION ALL` to combine the results of two selects. But selecting every column and row from two tables doesn't sound like a good idea. — Jakub Kania, Jan 09 '16 at 12:35
I'm not going to select all rows in reality, but for ease of reading I left that part out... — OliverJ90, Jan 09 '16 at 12:37
You also left out which part of the food chain formats the output as JSON array, because Postgres does not do that for a plain `SELECT`. You can make Postgres output JSON, but then your unnamed piece of software will nest that another time. — Erwin Brandstetter, Jan 09 '16 at 18:09

score 1 · Answer 1 · answered Jan 09 '16 at 13:34

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)

POSTGRESQL multiple select, multiple row arrays possible?

1 Answers1