具有多个对象的JSON数据中的PostgreSQL json - String_agg

Question

我在PostgreSQL中创建了带有Json数据类型字段的表。

我还在json data中插入了带有多个对象的数据。

像这样

[
    {
        "Type": "1",
        "Amount": "1000",
        "Occurrence": 2,
        "StartDate": "1990-01-19",
        "EndDate": "1999-04-03"
    },
    {
        "Type": "2",
        "Amount": "2000",
        "Occurrence": 2,
        "StartDate": "1984-11-19",
        "EndDate": "1997-09-29"
    }
]

现在我必须按照下面的格式在单行中检索数据，就像string_agg()函数输出。

Type    Amount
1--2    1000-2000

此外，我还检查了PostgreSQL (https://www.postgresqltutorial.com/postgresql-json/)中json的内置函数，但没有找到任何解决方案。

Answer 1

您必须取消数组的嵌套并聚合各个键：

下面假设您在表中有某种类型的主键列(除了JSON列之外)：

select t.id, 
       string_agg(x.element ->> 'Type', '-') as types,
       string_agg(x.element ->> 'Amount', '-') as amounts
from the_table t
  cross join jsonb_array_elements(t.data) as x(element)
group by t.id;

jsonb_array_elements()提取每个数组元素，然后主查询使用string_agg()按ID聚合该元素。

上面假设您的列是用jsonb类型定义的(它应该是)。如果不是，则需要改用json_array_elements()。

Online example