How to push value into key[] in jsonb column?

user_196241 0
Viewed 123

I have such jsonb structure in Postgres table: { "res": [123, 223] } and I want to push values into res-array. I don't know how many values there is on array and as jsonb_insert operates on position, so I tried with -1 position:

select jsonb_insert( '{"res": [123, 223]}', '{res,-1}', '333');
       jsonb_insert       
--------------------------
 {"res": [123, 333, 223]}

It does not work. How to push new values to the end of array?

I am using Postgres 9.6

jsonb postgresql
2 Answers

Use the -1 index along with insert_after = true:

SELECT jsonb_insert( '{"res": [123, 456, 789, 101112]}', '{res, -1}', '333', true);

+-------------------------------------+
|jsonb_insert                         |
+-------------------------------------+
|{"res": [123, 456, 789, 101112, 333]}|
+-------------------------------------+

The default value of insert_after is false, meaning that although you're targeting the last element (index = -1), you end up inserting in the second to last position:

SELECT jsonb_insert( '{"res": [123, 456, 789, 101112]}', '{res, -1}', '333', false);

+-------------------------------------+
|jsonb_insert                         |
+-------------------------------------+
|{"res": [123, 456, 789, 333, 101112]}|
+-------------------------------------+
user_26504370

One possible solution is to use ridiculously big position:

select jsonb_insert( '{"res": [123, 223]}', '{res,1000000}', '333');
       jsonb_insert       
--------------------------
 {"res": [123, 223, 333]}
user_1962410
Related