POSTGRESQL多重选择，多行数组可能吗?

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.

其中行对象数组是表1中的每一行，然后是表2中的每一行。

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

我想要的是能够将上面的SELECT语句合并到一个DB查询中，但是要让它返回如下内容:

[
{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.

…这样，我就可以避免对DB的多个查询，同时将每个查询的结果对象转换为一个自定义对象，从DB查询引用到DB查询，就像一个穴居人一样。一个查询，一个干净的结果集，我可以在客户端上处理，知道哪个行来自哪个表。

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

你能用AS来表示这个吗?我如何通过一个查询来实现这一点?

1 个解决方案

#1

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

您基本上需要:-使用组合多个查询与联合所有;-使用json_build_object和json_agg函数构建json数组;

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)

#1