Nesting jsonb in postgres without converting to jsonb[]

Question

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?

Your expected output is not valid JSON. Your "actual" output is not valid JSON, either, so of course can't be the actual actual output. — jjanes
– jjanes, Commented Mar 3, 2022 at 2:10

user330315 · Accepted Answer · 2022-03-02 14:19:40Z

1

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

answered Mar 2, 2022 at 14:19

user330315

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

Collectives™ on Stack Overflow

Nesting jsonb in postgres without converting to jsonb[]

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related