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

S-Man · Accepted Answer · 2019-09-02 14:01:22Z

26

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

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

answered Sep 2, 2019 at 14:01

S-Man

24k9 gold badges51 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Onni Hakala Over a year ago

How would one handle case where value of changes column can also be NULL?

S-Man Over a year ago

Where is the problem? dbfiddle.uk/MEuHUSie

Onni Hakala Over a year ago

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: stackoverflow.com/questions/76765417/…

Collectives™ on Stack Overflow

Concatenate JSON arrays in PostgreSQL aggregate

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related