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
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]}|
+-------------------------------------+
One possible solution is to use ridiculously big position:
select jsonb_insert( '{"res": [123, 223]}', '{res,1000000}', '333');
jsonb_insert
--------------------------
{"res": [123, 223, 333]}