18

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
S-Man
  • 22,521
  • 7
  • 40
  • 63
Dave Vogt
  • 18,600
  • 7
  • 42
  • 54

1 Answers1

25

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
S-Man
  • 22,521
  • 7
  • 40
  • 63
  • How would one handle case where value of changes column can also be NULL? – Onni Hakala Jul 25 '23 at 17:15
  • Where is the problem? https://dbfiddle.uk/MEuHUSie – S-Man Jul 26 '23 at 13:01
  • Your answer was leading me to correct place but the arrays in my dataset were sometimes missing and then approach like this dismissed the rows where the array was NULL. I wrote about my problem and solution on this question: 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 Jul 26 '23 at 19:21