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;

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"}]

json_agg函数产生这个开箱即用的结果。它会自动找出如何将其输入转换为JSON并将其聚合到数组中。

SELECT json_agg(t) FROM t

SELECT to_jsonb(array_agg(t)) FROM t

SELECT json_agg(t)::jsonb FROM t

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数组。

我看不出两者之间有什么显著的性能差异。

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

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稍微快一些。

没有json_build_object函数的jsonb版本。您可以聚合到单行中并转换：

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_agg的JSON结果。

也可以使用显式强制转换：

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

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

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

(9.3没有jsonb)

在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 functions中找到JSON函数的文档。

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

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

_{Hannes Landeholm指出了这一点}