在PostgreSQL聚合中连接JSON数组

Question

我有一个包含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

- Dave Vogt

Answer 1

演示：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

1. 使用jsonb_array_elements()将数组元素扩展为每个元素一行
2. 使用jsonb_agg()和GROUP BY将它们重新聚合为一个单一的数组

- S-Man

---

在处理更改列的值也可能为NULL的情况下，应该如何处理？ - Onni Hakala

问题出在哪里？https://dbfiddle.uk/MEuHUSie - S-Man

你的回答引导我到了正确的地方，但是我的数据集中的数组有时会缺失，然后像这样的方法会忽略数组为NULL的行。我在这个问题上写了关于我的问题和解决方案的内容：https://stackoverflow.com/questions/76765417/how-to-get-last-non-null-values-and-aggregated-non-null-values-by-timestamp-from/76769963?noredirect=1#comment135347475_76769963 - Onni Hakala