PostgreSQL返回结果集为JSON数组？

Question

我想让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');

我想要类似的东西

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

要么

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

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

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;

而且我觉得我很亲近，但真的不在那儿。我是否应该查看9.15以外的其他文档。JSON函数和运算符？

顺便说一下，我不确定我的想法。这是通常的设计决定吗？我的想法是，我当然可以接受上述3个查询中的第一个查询的结果（例如），并在将其提供给客户端之前先在应用程序中对其进行一些操作，但是如果PostgreSQL可以直接创建最终的JSON对象，这样会更简单，因为我仍未在应用程序中包括对任何JSON库的任何依赖关系。

Answer 1

SELECT json_agg(t) FROM t

用于对象的JSON数组，以及

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

用于数组的JSON对象。

对象清单

本节介绍如何生成对象的JSON数组，并将每一行转换为单个对象。结果看起来像这样：

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

9.3及以上

该json_agg函数立即产生此结果。它会自动找出如何将其输入转换为JSON并将其聚合为一个数组。

SELECT json_agg(t) FROM t

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

SELECT to_jsonb(array_agg(t)) FROM t

或json_agg与演员表结合：

SELECT json_agg(t)::jsonb FROM t

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

9.2

9.2没有json_agg或to_json功能，因此您需要使用较早的版本array_to_json：

SELECT array_to_json(array_agg(t)) FROM t

您可以选择row_to_json在查询中包含呼叫：

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

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

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

清单对象

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

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

9.5及以上

我们可以利用该json_build_object功能：

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

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

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

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

哪一个更清楚是一个意见问题。如果使用该json_build_object功能，强烈建议将一对键/值放在一行上以提高可读性。

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

没有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

与其他针对这种结果的查询不同，使用时array_agg似乎要快一些to_jsonb。我怀疑这是由于开销分析和验证的JSON结果引起的json_agg。

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

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

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

9.4和9.3

该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

要么

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

这取决于你是否愿意json或jsonb。

（9.3没有jsonb。）

9.2

在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

文献资料

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

json_agg在“ 汇总功能”页面上。

设计

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

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

空值

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

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

要么

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