PostgreSQL 返回结果集为 JSON 数组？

PostgreSQL return result set as JSON array?

提问by engineerX

I would like to have PostgreSQL return the result of a query as one JSON array. Given

我想让 PostgreSQL 将查询结果作为一个 JSON 数组返回。给定的

create table t (a int primary key, b text);

insert into t values (1, 'value1');
insert into t values (2, 'value2');
insert into t values (3, 'value3');

I would like something similar to

我想要类似的东西

[{"a":1,"b":"value1"},{"a":2,"b":"value2"},{"a":3,"b":"value3"}]

or

或者

{"a":[1,2,3], "b":["value1","value2","value3"]}

(actually it would be more useful to know both). I have tried some things like

（实际上，了解两者会更有用）。我尝试过一些类似的事情

select row_to_json(row) from (select * from t) row;
select array_agg(row) from (select * from t) row;
select array_to_string(array_agg(row), '') from (select * from t) row;

And I feel I am close, but not there really. Should I be looking at other documentation except for 9.15. JSON Functions and Operators?

我觉得我很接近，但不是真的。我是否应该查看除9.15之外的其他文档？JSON 函数和运算符?

By the way, I am not sure about my idea. Is this a usual design decision? My thinking is that I could, of course, take the result (for example) of the first of the above 3 queries and manipulate it slightly in the application before serving it to the client, but if PostgreSQL can create the final JSON object directly, it would be simpler, because I still have not included any dependency on any JSON library in my application.

顺便说一句，我不确定我的想法。这是一个通常的设计决定吗？我的想法是，当然，我可以将上述 3 个查询中的第一个查询的结果（例如）在应用程序中稍微操作一下，然后再将其提供给客户端，但是如果 PostgreSQL 可以直接创建最终的 JSON 对象，它会更简单，因为我仍然没有在我的应用程序中包含对任何 JSON 库的任何依赖。

回答by jpmc26

TL;DR

TL; 博士

SELECT json_agg(t) FROM t

for a JSON array of objects, and

对于 JSON 对象数组，以及

SELECT
    json_build_object(
        'a', json_agg(t.a),
        'b', json_agg(t.b)
    )
FROM t

for a JSON object of arrays.

对于数组的 JSON 对象。

List of objects

对象列表

This section describes how to generate a JSON array of objects, with each row being converted to a single object. The result looks like this:

本节介绍如何生成对象的 JSON 数组，并将每一行转换为单个对象。结果如下所示：

[{"a":1,"b":"value1"},{"a":2,"b":"value2"},{"a":3,"b":"value3"}]

9.3 and up

9.3 及以上

The json_aggfunction produces this result out of the box. It automatically figures out how to convert its input into JSON and aggregates it into an array.

该json_agg函数开箱即用地生成此结果。它会自动计算出如何将其输入转换为 JSON 并将其聚合为一个数组。

SELECT json_agg(t) FROM t

There is no jsonb(introduced in 9.4) version of json_agg. You can either aggregate the rows into an array and then convert them:

没有jsonb（在 9.4 中引入）版本的json_agg. 您可以将行聚合到一个数组中，然后将它们转换：

SELECT to_jsonb(array_agg(t)) FROM t

or combine json_aggwith a cast:

或结合json_agg演员阵容：

SELECT json_agg(t)::jsonb FROM t

My testing suggests that aggregating them into an array first is a little faster. I suspect that this is because the cast has to parse the entire JSON result.

我的测试表明，首先将它们聚合到一个数组中会更快一些。我怀疑这是因为演员必须解析整个 JSON 结果。

9.2

9.2

9.2 does not have the json_aggor to_jsonfunctions, so you need to use the older array_to_json:

9.2 没有json_agg或to_json功能，所以你需要使用旧的array_to_json：

SELECT array_to_json(array_agg(t)) FROM t

You can optionally include a row_to_jsoncall in the query:

您可以选择row_to_json在查询中包含一个调用：

SELECT array_to_json(array_agg(row_to_json(t))) FROM t

This converts each row to a JSON object, aggregates the JSON objects as an array, and then converts the array to a JSON array.

这会将每一行转换为 JSON 对象，将 JSON 对象聚合为数组，然后将该数组转换为 JSON 数组。

I wasn't able to discern any significant performance difference between the two.

我无法辨别两者之间的任何显着性能差异。

Object of lists

列表对象

This section describes how to generate a JSON object, with each key being a column in the table and each value being an array of the values of the column. It's the result that looks like this:

本节介绍如何生成 JSON 对象，每个键是表中的一列，每个值是该列值的数组。结果如下所示：

{"a":[1,2,3], "b":["value1","value2","value3"]}

9.5 and up

9.5 及以上

We can leverage the json_build_objectfunction:

我们可以利用这个json_build_object函数：

SELECT
    json_build_object(
        'a', json_agg(t.a),
        'b', json_agg(t.b)
    )
FROM t

You can also aggregate the columns, creating a single row, and then convert that into an object:

您还可以聚合列，创建单行，然后将其转换为对象：

SELECT to_json(r)
FROM (
    SELECT
        json_agg(t.a) AS a,
        json_agg(t.b) AS b
    FROM t
) r

Note that aliasing the arrays is absolutely required to ensure that the object has the desired names.

请注意，绝对需要对数组进行别名以确保对象具有所需的名称。

Which one is clearer is a matter of opinion. If using the json_build_objectfunction, I highly recommend putting one key/value pair on a line to improve readability.

哪个更清楚是见仁见智了。如果使用该json_build_object函数，我强烈建议在一行中放置一个键/值对以提高可读性。

You could also use array_aggin place of json_agg, but my testing indicates that json_aggis slightly faster.

您也可以使用array_agg代替json_agg，但我的测试表明它json_agg稍快一些。

There is no jsonbversion of the json_build_objectfunction. You can aggregate into a single row and convert:

没有jsonb该json_build_object功能的版本。您可以聚合成一行并转换：

SELECT to_jsonb(r)
FROM (
    SELECT
        array_agg(t.a) AS a,
        array_agg(t.b) AS b
    FROM t
) r

Unlike the other queries for this kind of result, array_aggseems to be a little faster when using to_jsonb. I suspect this is due to overhead parsing and validating the JSON result of json_agg.

与此类结果的其他查询不同，array_agg使用to_jsonb. 我怀疑这是由于开销解析和验证json_agg.

Or you can use an explicit cast:

或者您可以使用显式转换：

SELECT
    json_build_object(
        'a', json_agg(t.a),
        'b', json_agg(t.b)
    )::jsonb
FROM t

The to_jsonbversion allows you to avoid the cast and is faster, according to my testing; again, I suspect this is due to overhead of parsing and validating the result.

to_jsonb根据我的测试，该版本允许您避免强制转换并且速度更快；再次，我怀疑这是由于解析和验证结果的开销。

9.4 and 9.3

9.4 和 9.3

The json_build_objectfunction was new to 9.5, so you have to aggregate and convert to an object in previous versions:

该json_build_object函数是 9.5 的新功能，因此您必须在以前的版本中聚合并转换为对象：

SELECT to_json(r)
FROM (
    SELECT
        json_agg(t.a) AS a,
        json_agg(t.b) AS b
    FROM t
) r

or

或者

SELECT to_jsonb(r)
FROM (
    SELECT
        array_agg(t.a) AS a,
        array_agg(t.b) AS b
    FROM t
) r

depending on whether you want jsonor jsonb.

取决于您是否想要json或jsonb。

(9.3 does not have jsonb.)

（9.3没有jsonb。）

9.2

9.2

In 9.2, not even to_jsonexists. You must use row_to_json:

在 9.2 中，甚至不to_json存在。您必须使用row_to_json：

SELECT row_to_json(r)
FROM (
    SELECT
        array_agg(t.a) AS a,
        array_agg(t.b) AS b
    FROM t
) r

Documentation

文档

Find the documentation for the JSON functions in JSON functions.

查找在JSON功能的文档JSON功能。

json_aggis on the aggregate functionspage.

json_agg位于聚合函数页面上。

Design

设计

If performance is important, ensure you benchmark your queries against your own schema and data, rather than trust my testing.

如果性能很重要，请确保根据自己的架构和数据对查询进行基准测试，而不是相信我的测试。

Whether it's a good design or not really depends on your specific application. In terms of maintainability, I don't see any particular problem. It simplifies your app code and means there's less to maintain in that portion of the app. If PG can give you exactly the result you need out of the box, the only reason I can think of to not use it would be performance considerations. Don't reinvent the wheel and all.

它是否是一个好的设计实际上取决于您的特定应用程序。在可维护性方面，我没有看到任何特别的问题。它简化了您的应用程序代码，意味着在应用程序的该部分中需要维护的内容更少。如果 PG 可以为您提供开箱即用的确切结果，那么我能想到的不使用它的唯一原因就是性能方面的考虑。不要重新发明轮子等等。

Nulls

空值

Aggregate functions typically give back NULLwhen they operate over zero rows. If this is a possibility, you might want to use COALESCEto avoid them. A couple of examples:

聚合函数通常NULL在它们对零行进行操作时返回。如果这是可能的，您可能想使用它COALESCE来避免它们。几个例子：

SELECT COALESCE(json_agg(t), '[]'::json) FROM t

Or

或者

SELECT to_jsonb(COALESCE(array_agg(t), ARRAY[]::t[])) FROM t

_{Credit to Hannes Landeholmfor pointing this out}

_{感谢汉内斯·Landeholm的指出了这一点}

回答by Himanshu sharma

Also if you want selected field from table and aggregated then as array .

此外，如果您想从表中选择字段并聚合为 array 。

SELECT json_agg(json_build_object('data_a',a,
                                  'data_b',b,
))  from t;

The result will come .

结果会来的。

 [{'data_a':1,'data_b':'value1'}
  {'data_a':2,'data_b':'value2'}]