sql - 在PostgreSQL聚合中连接JSON数组

我有一个表，其中包含JSON类型的字段，其中包含数据数组:

      Column       |  Type
-------------------+---------
 id                | integer
 user_id           | uuid
 changes           | jsonb
 exercise_entry_id | integer

changes字段包含JSON对象的列表。

对于数据清理任务，我需要将changes字段的内容汇总为一个聚合，并返回另一个非嵌套的JSON数组。

说，数据库包含以下行:

id | user_id | changes         | exercise_entry_id
---+---------+-----------------+---------------------
 1 | foo     | ['a', 'b']      | 3
 2 | foo     | ['c', 'd']      | 3

我需要一个按user_id和exercise_entry_id分组的结果，其中的更改如下所示。

user_id | changes                     | exercise_entry_id
--------+-----------------------------+---------------------------
 foo    | ['a', 'b', 'c', 'd']        | 3

最佳答案

demo:db<>fiddle

SELECT
    user_id,
    jsonb_agg(elems) AS changes,
    exercise_entry_id
FROM
    mytable,
    jsonb_array_elements(changes) as elems
GROUP BY user_id, exercise_entry_id

使用jsonb_array_elements()扩展数组元素到每个元素一行

使用jsonb_agg()和GROUP BY将它们重新聚集到一个数组中