草庐IT

json - PostgreSQL 返回结果集为 JSON 数组？

coder 2023-04-29 原文

我想让 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 Functions and Operators 之外的其他文档吗？ ?

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

最佳答案

TL; 博士

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 Actor 阵容:
SELECT json_agg(t)::jsonb FROM t

我的测试表明，首先将它们聚合到一个数组中会更快一些。我怀疑这是因为 Actor 必须解析整个 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_agg 的 JSON 结果的开销造成的.

或者您可以使用显式转换:
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 functions 中找到有关 JSON 函数的文档.
json_agg在 aggregate functions 上页。

设计

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

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

空值

聚合函数通常会返回 NULL当它们在零行上操作时。如果这是可能的，您可能需要使用 COALESCE避免它们。几个例子:
SELECT COALESCE(json_agg(t), '[]'::json) FROM t

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

归功于 Hannes Landeholm为 pointing this out

关于json - PostgreSQL 返回结果集为 JSON 数组？，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/24006291/

集为PostgreSQLcodejsonbr

有关json - PostgreSQL 返回结果集为 JSON 数组？的更多相关文章

  1. ruby-on-rails - 在 Ruby 中循环遍历多个数组 - 2

    我有多个ActiveRecord子类Item的实例数组，我需要根据最早的事件循环打印。在这种情况下，我需要打印付款和维护日期，如下所示:ItemAmaintenancerequiredin5daysItemBpaymentrequiredin6daysItemApaymentrequiredin7daysItemBmaintenancerequiredin8days我目前有两个查询，用于查找maintenance和payment项目(非排他性查询)，并输出如下内容:paymentrequiredin...maintenancerequiredin...有什么方法可以改善上述(丑陋的)代

  2. ruby - 多次弹出/移动 ruby​​ 数组 - 2

    我的代码目前看起来像这样numbers=[1,2,3,4,5]defpop_threepop=[]3.times{pop有没有办法在一行中完成pop_three方法中的内容？我基本上想做类似numbers.slice(0,3)的事情，但要删除切片中的数组项。嗯...嗯，我想我刚刚意识到我可以试试slice! 最佳答案 是numbers.pop(3)或者numbers.shift(3)如果你想要另一边。 关于ruby-多次弹出/移动ruby​​数组，我们在StackOverflow上找到一

  3. ruby - 将数组的内容转换为 int - 2

    我需要读入一个包含数字列表的文件。此代码读取文件并将其放入二维数组中。现在我需要获取数组中所有数字的平均值，但我需要将数组的内容更改为int。有什么想法可以将to_i方法放在哪里吗？ClassTerraindefinitializefile_name@input=IO.readlines(file_name)#readinfile@size=@input[0].to_i@land=[@size]x=1whilex 最佳答案 只需将数组映射为整数:@land边注如果你想得到一条线的平均值，你可以这样做:values=@input[x]

  4. ruby - 为什么 4.1%2 使用 Ruby 返回 0.0999999999999996？但是 4.2%2==0.2 - 2

    为什么4.1%2返回0.0999999999999996？但是4.2%2==0.2。 最佳答案 参见此处:WhatEveryProgrammerShouldKnowAboutFloating-PointArithmetic实数是无限的。计算机使用的位数有限(今天是32位、64位)。因此计算机进行的浮点运算不能代表所有的实数。0.1是这些数字之一。请注意，这不是与Ruby相关的问题，而是与所有编程语言相关的问题，因为它来自计算机表示实数的方式。 关于ruby-为什么4.1%2使用Ruby返

  5. ruby - 通过 erb 模板输出 ruby​​ 数组 - 2

    我正在使用puppet为ruby​​程序提供一组常量。我需要提供一组主机名，我的程序将对其进行迭代。在我之前使用的bash脚本中，我只是将它作为一个puppet变量hosts=>"host1,host2"我将其提供给bash脚本作为HOSTS=显然这对ruby​​不太适用——我需要它的格式hosts=["host1","host2"]自从phosts和putsmy_array.inspect提供输出["host1","host2"]我希望使用其中之一。不幸的是，我终其一生都无法弄清楚如何让它发挥作用。我尝试了以下各项:我发现某处他们指出我需要在函数调用前放置“function_”……这

  6. ruby - 检查数组是否在增加 - 2

    这个问题在这里已经有了答案:Checktoseeifanarrayisalreadysorted?(8个答案)关闭9年前。我只是想知道是否有办法检查数组是否在增加？这是我的解决方案，但我正在寻找更漂亮的方法:n=-1@arr.flatten.each{|e|returnfalseife

  7. ruby-on-rails - Rails HTML 请求渲染 JSON - 2

    在我的Controller中，我通过以下方式在我的index方法中支持HTML和JSON:respond_todo|format|format.htmlformat.json{renderjson:@user}end在浏览器中拉起它时，它会自然地以HTML呈现。但是，当我对/user资源进行内容类型为application/json的curl调用时(因为它是索引方法)，我仍然将HTML作为响应。如何获取JSON作为响应？我还需要说明什么？ 最佳答案 您应该将.json附加到请求的url，提供的格式在routes.rb的路径中定义。这

  8. ruby - 如果指定键的值在数组中相同，如何合并哈希 - 2

    我有一个这样的哈希数组:[{:foo=>2,:date=>Sat,01Sep2014},{:foo2=>2,:date=>Sat,02Sep2014},{:foo3=>3,:date=>Sat,01Sep2014},{:foo4=>4,:date=>Sat,03Sep2014},{:foo5=>5,:date=>Sat,02Sep2014}]如果:date相同，我想合并哈希值。我对上面数组的期望是:[{:foo=>2,:foo3=>3,:date=>Sat,01Sep2014},{:foo2=>2,:foo5=>5:date=>Sat,02Sep2014},{:foo4=>4,:dat

  9. ruby - 在 Ruby 中用键盘诅咒数组浏览 - 2

    我正在尝试在Ruby中制作一个cli应用程序，它接受一个给定的数组，然后将其显示为一个列表，我可以使用箭头键浏览它。我觉得我已经在Ruby中看到一个库已经这样做了，但我记不起它的名字了。我正在尝试对soundcloud2000中的代码进行逆向工程做类似的事情，但他的代码与SoundcloudAPI的使用紧密耦合。我知道cursesgem，我正在考虑更抽象的东西。广告有没有人见过可以做到这一点的库或一些概念证明的Ruby代码可以做到这一点？ 最佳答案 我不知道这是否是您正在寻找的，但也许您可以使用我的想法。由于我没有关于您要完成的工作

  10. ruby - 检查字符串是否包含散列中的任何键并返回它包含的键的值 - 2

    我有一个包含多个键的散列和一个字符串，该字符串不包含散列中的任何键或包含一个键。h={"k1"=>"v1","k2"=>"v2","k3"=>"v3"}s="thisisanexamplestringthatmightoccurwithakeysomewhereinthestringk1(withspecialcharacterslike(^&*$#@!^&&*))"检查s是否包含h中的任何键的最佳方法是什么，如果包含，则返回它包含的键的值？例如，对于上面的h和s的例子，输出应该是v1。编辑:只有字符串是用户定义的。哈希将始终相同。 最佳答案

随机推荐