Nesting jsonb in postgres without converting to jsonb[]

user_14760635 0
Viewed 39

I have 1 table with 2 columns 1 is an index that holds the group number and a column of jsonb data

|      Index     |     payload    |
|----------------|----------------|
|       1        |     {jsonb}    |
|       1        |     {jsonb}    |
|       2        |     {jsonb}    |
|       2        |     {jsonb}    |

I then want to nest the payload into another jsonb, but it must not be an array.
Expected Output:

|      Index     |     payload    |
|----------------|----------------|
|       1        |{{jsonb},{jsonb}}|
|       2        |{{jsonb},{jsonb}}|

Actual Output:

|      Index     |     payload    |
|----------------|----------------|
|       1        |[{{jsonb},{jsonb}}]|
|       2        |[{{jsonb},{jsonb}}]|


SELECT index, jsonb_agg(payload) as "payload"
FROM table1
GROUP BY 1
ORDER BY 1

As you can see the output does aggregate the columns into a jsonb, but also converts it into an array. Is it possible to remove the array?

jsonb postgresql sql json
1 Answers

You can create your own aggregate that just appends the JSONB values:

create aggregate jsonb_append_agg(jsonb) 
(
  sfunc = jsonb_concat(jsonb, jsonb),
  stype = jsonb
);

Then you can do:

SELECT index, jsonb_append_agg(payload) as "payload"
FROM table1
GROUP BY 1
ORDER BY 1
user_3303150
Related