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!

Akhilesh Mishra · Accepted Answer · 2020-09-08 09:25:13Z

2

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.

answered Sep 8, 2020 at 9:25

Akhilesh Mishra

6,1503 gold badges20 silver badges37 bronze badges

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

Comments

jakson dikison · Accepted Answer · 2024-03-13 18:53:46Z

1

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.

answered Mar 13, 2024 at 18:53

jakson dikison

1491 silver badge3 bronze badges

1 Comment

jakson dikison Over a year ago

Just found this: dba.stackexchange.com/a/224232

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related