postgresql 返回同一行的多个列作为对象的JSON数组

kx5bkwkv 于 2023-04-29 发布在 PostgreSQL

关注(0)|答案(3)|浏览(167)

下面是MyTable：

id │ value_two │ value_three │ value_four 
────┼───────────┼─────────────┼────────────
  1 │ a         │ A           │ AA
  2 │ a         │ A2          │ AA2
  3 │ b         │ A3          │ AA3
  4 │ a         │ A4          │ AA4
  5 │ b         │ A5          │ AA5

我想查询一个由value_two分组的对象{ value_three, value_four }数组。value_two应该单独存在于结果中。结果应该如下所示：

value_two │                                                                                    value_four                                                                                 
───────────┼───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
 a         │ [{"value_three":"A","value_four":"AA"}, {"value_three":"A2","value_four":"AA2"}, {"value_three":"A4","value_four":"AA4"}]
 b         │ [{"value_three":"A3","value_four":"AA3"}, {"value_three":"A5","value_four":"AA5"}]

无论是使用json_agg()还是array_agg()。
我能做的最好的是：

with MyCTE as ( select value_two, value_three, value_four from MyTable ) 
select value_two, json_agg(row_to_json(MyCTE)) value_four 
from MyCTE 
group by value_two;

它返回：

value_two │                                                                                    value_four                                                                                 
───────────┼───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
 a         │ [{"value_two":"a","value_three":"A","value_four":"AA"}, {"value_two":"a","value_three":"A2","value_four":"AA2"}, {"value_two":"a","value_three":"A4","value_four":"AA4"}]
 b         │ [{"value_two":"b","value_three":"A3","value_four":"AA3"}, {"value_two":"b","value_three":"A5","value_four":"AA5"}]

在对象中有一个额外的value_two键，我想摆脱它。我应该使用哪个SQL（Postgres）查询？

postgresql

来源：https://stackoverflow.com/questions/26486784/return-multiple-columns-of-the-same-row-as-json-array-of-objects

3条答案

按热度按时间

wwwo4jvm1#

Postgres 9中的`json_build_object()`。4或更新版本

或者jsonb_build_object()返回jsonb。

SELECT value_two, json_agg(json_build_object('value_three', value_three
                                           , 'value_four' , value_four)) AS value_four
FROM   mytable 
GROUP  BY value_two;

手册：
从可变参数列表构建JSON对象。按照约定，参数列表由交替的键和值组成。

适用于任何版本（包括Postgres 9.（3）

row_to_json()加上**ROW**表达式就可以了：

SELECT value_two
     , json_agg(row_to_json((value_three, value_four))) AS value_four
FROM   mytable
GROUP  BY value_two;

但会丢失原始列名称。强制转换为已注册的行类型可以避免这种情况。（临时表的行类型也用于即席查询。）

CREATE TYPE foo AS (value_three text, value_four text);  -- once in the same session

SELECT value_two
     , json_agg(row_to_json((value_three, value_four)::foo)) AS value_four
FROM   mytable
GROUP  BY value_two;

或者使用subselect代替ROW表达式。更详细，但没有类型转换：

SELECT value_two
     , json_agg(row_to_json((SELECT t FROM (SELECT value_three, value_four) t))) AS value_four
FROM   mytable
GROUP  BY value_two;

更多的解释在克雷格的相关回答：

PostgreSQL 9.2 row_to_json() with nested joins
db〈〉fiddle here*

老麻雀

赞(0）回复(0）举报 2023-04-29

eqzww0vc2#

to_jsonwitharray_aggwith复合类型

begin;
create table  mytable(
id bigint, value_two text, value_three text, value_four  text);
insert into mytable(id,value_two, value_three,value_four)
values
 ( 1, 'a',       'A',           'AA'),
  (2, 'a'    ,     'A2'  ,       'AA2'),
  (3, 'b'  ,       'A3',         'AA3'),
 ( 4, 'a'   ,      'A4',          'AA4'),
  (5, 'b' ,        'A5',          'AA5');
commit;
create type mytable_type as (value_three text, value_four text);

select value_two,
       to_json( array_agg(row(value_three,value_four)::mytable_type))
from mytable
group by 1;

赞(0）回复(0）举报 2023-04-29

6qqygrtg3#

使用jsonb_agg和to_jsonb。

SELECT
    value_two,
    jsonb_agg(to_jsonb (t.*) - '{id,value_two}'::text[]) AS data
FROM
    mytable t
GROUP BY
    1
ORDER BY
    1;

基于手动参考
jsonb - text[] → jsonb
从左操作数中删除所有匹配的键或数组元素。

赞(0）回复(0）举报 2023-04-29

我来回答

postgresql 返回同一行的多个列作为对象的JSON数组

3条答案

Postgres 9中的`json_build_object()`。4或更新版本

适用于任何版本（包括Postgres 9.（3）

相关问题

热门标签

最新问答

postgresql 返回同一行的多个列作为对象的JSON数组

3条答案

Postgres 9中的json_build_object()。4或更新版本

适用于任何版本（包括Postgres 9.（3）

相关问题

热门标签

最新问答

Postgres 9中的`json_build_object()`。4或更新版本