CODEWITHSUNDEEP
arrayspostgresqljsonb
nagylzs
nagylzs

Reputation: 4180

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

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!

Upvotes: 2

Views: 5273

Answers (2)

jakson dikison
jakson dikison

Reputation: 149

You can also use the concatenation operator to prepend elements to a jsonb array simply by switching the order of the operands (note that in order for the concatenation to work in this case the right-hand operand needs to be wrapped in parentheses):

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

This returns:

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

One benefit of this method is that you can use it to prepend multiple elements at a time:

select jsonb_build_object('items',
        '[-2, -1]'::jsonb || (value->'items')
)  from a;

This returns:

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

This doesn't handle the case where you want to insert elements at an arbitrary index.

Upvotes: 1

Akhilesh Mishra
Akhilesh Mishra

Reputation: 6130

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.

Upvotes: 2

Related Questions