How to prepend/insert an element into a jsonb array in PostgreSQL 12?

Question

Example:

drop table a;
create table a( value jsonb);
insert into a values ('{"items":[0,1,2]}'::jsonb);
select value->'items' from a;

I already know how to append to an array. For example:

select jsonb_build_object('items', 
        value->'items' || '3'::jsonb
)  from a

This returns:

{"items": [0, 1, 2, 3]}

But I don't know how to preprended or insert an element into an array.

For example:

select jsonb_build_object('items', 
        jsonb_array_insert(value->'items',0,-1)
)  from a

And this would return:

{"items": [-1, 0, 1, 2]}

But there is no jsonb_array_insert function. The documentation at https://www.postgresql.org/docs/12/functions-json.html ( Table 9.45. Additional jsonb Operators ) describes some useful jsonb operators, but none of them can be used for inserting/prepending elements.

Well, jsonb_array_elements(value->'items') can be used to convert an array into a set of elements. But I don't see how it could be used to insert/prepend elements into the array.

I could possibly write my own PL/SQL function for this, but there must be a better way!

Answer 1

You can use JSONB_INSERT to achieve what you want. Please refer the documentation.

Try it this way:

select 
jsonb_insert(value,'{items,0}','-1'::jsonb)
from a

This function works exactly as per your requirement.