PostgreSQL返回结果集为JSON数组? [英] PostgreSQL return result set as JSON array?

问题描述

我希望PostgreSQL将查询结果作为一个JSON数组返回.给定

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

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

(实际上，同时了解两者会更有用).我已经尝试过类似的东西

(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?

顺便说一下，我不确定我的想法.这是通常的设计决定吗?我的想法是，我当然可以获取上述3个查询中的第一个查询的结果(例如)，并在将其提供给客户端之前先在应用程序中对其进行一些操作，但是如果PostgreSQL可以直接创建最终的JSON对象，这样会更简单，因为我仍然没有在我的应用程序中包括对任何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.

推荐答案

TL; DR

SELECT json_agg(t) FROM t

用于对象的JSON数组，和

for a JSON array of objects, and

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

用于数组的JSON对象.

for a JSON object of arrays.

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

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:

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

9.3及以上

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

9.3 and up

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

SELECT json_agg(t) FROM t

没有json_agg的jsonb版本(在9.4中引入).您可以将行聚合到一个数组中，然后将其转换:

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:

SELECT to_jsonb(array_agg(t)) FROM t

或将json_agg与演员表相结合:

SELECT json_agg(t)::jsonb FROM t

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

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.

9.2没有json_agg或to_json函数，因此您需要使用较旧的array_to_json:

9.2 does not have the json_agg or to_json functions, so you need to use the older array_to_json:

SELECT array_to_json(array_agg(t)) FROM t

您可以选择在查询中包含row_to_json调用:

You can optionally include a row_to_json call in the query:

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

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

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

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

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

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

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:

{"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

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

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.

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

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

您也可以使用array_agg代替json_agg，但是我的测试表明json_agg稍微快一点.

You could also use array_agg in place of json_agg, but my testing indicates that json_agg is slightly faster.

没有json_build_object功能的jsonb版本.您可以将其汇总为一行并进行转换:

There is no jsonb version of the json_build_object function. You can aggregate into a single row and convert:

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

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

Unlike the other queries for this kind of result, array_agg seems 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.

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

Or you can use an explicit cast:

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

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

The to_jsonb version 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.

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

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

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中，甚至不存在to_json.您必须使用row_to_json:

In 9.2, not even to_json exists. You must use 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在集合函数页面.

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

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

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

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.

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

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

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

或

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

_向 Hannes Landeholm 授予

_{Credit to Hannes Landeholm for pointing this out}

这篇关于PostgreSQL返回结果集为JSON数组?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！