Concatenate JSON arrays in PostgreSQL aggregate

Question

I have a table that contains a field of JSON type, containing arrays of data:

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

The changes field contains a list of JSON objects.

For a data cleanup task, I need to concatenate the changes field's contents as an aggregate, returning another non-nested JSON array.

Say, the database contains the following rows:

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

I need a result, grouped by user_id and exercise_entry_id, where the changes are concatenated as follows.

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

Answer 1

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

1. Expand array elements into one row per element with jsonb_array_elements()
2. Reaggregate them into one single array using jsonb_agg() and GROUP BY