连接Postgresql json数组中对象的字符串值 [英] Concatenate string values of objects in a postgresql json array

问题描述

我有一个带有json列的postgresql表，其中填充了嵌套在数组中的对象.现在，我想构建一个查询，该查询返回json列中对象的ID和串联的字符串值.PostgreSQL版本是9.5.

I have a postgresql table with a json column filled with objects nested in array. Now I want to build a query that returns the id and concatenated string values of objects in the json column. PostgreSQL version is 9.5.

示例数据

CREATE TABLE test
(
    id integer,
    data json
);

INSERT INTO test (id, data) VALUES (1, '{
    "info":"a",
    "items":[
        { "name":"a_1" },
        { "name":"a_2" },
        { "name":"a_3" }
    ]
}');

INSERT INTO test (id, data) VALUES (2, '{
    "info":"b",
    "items":[
        { "name":"b_1" },
        { "name":"b_2" },
        { "name":"b_3" }
    ]
}');

INSERT INTO test (id, data) VALUES (3, '{
    "info":"c",
    "items":[
        { "name":"c_1" },
        { "name":"c_2" },
        { "name":"c_3" }
    ]
}');

与预期示例不符

不幸的是，到目前为止，我已经能够从表中获取值，而无需将字符串彼此添加.

So far I've been able to get the values from the table, unfortunately without the strings being added to one another.

SELECT
  row.id,
  item ->> 'name'
FROM
  test as row,
  json_array_elements(row.data #> '{items}' ) as item;

哪个会输出:

id | names
----------
 1 | a_1
 1 | a_2
 1 | a_3
 2 | b_1
 2 | b_2
 2 | b_3
 3 | c_1
 3 | c_2
 3 | c_3

预期的输出示例

查询看起来像这样会返回此输出吗?

How would a query look like that returns this output?

id | names
----------
 1 | a_1, a_2, a_3
 2 | b_1, b_2, b_3
 3 | c_1, c_2, c_3

SQL小提琴链接

推荐答案

您最初的尝试是一步一步地丢失了一个小组

Your original attempt was missing a group by step

这应该有效:

SELECT
    id
  , STRING_AGG(item->>'name', ', ')
FROM
  test,
  json_array_elements(test.data->'items') as item
GROUP BY 1

这篇关于连接Postgresql json数组中对象的字符串值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！