Efficiently insert elements into json array at specific index in Postgres

Question

We have an "order_json" column to store and represent the order of all the items referencing that row.

Items can be added by clients in specific indexes in json arrays.

However, I feel my solution for this is rather awkward and inefficient.

SELECT JSON_AGG(element)
FROM 

(WITH first_part AS (
    SELECT id, jsonb_array_elements(order_json) AS element
    FROM my_boards
    WHERE id = 5
    LIMIT 2 -- means that client wants to add the element at index 2
)
, second_part AS (
    SELECT id, jsonb_array_elements(order_json) AS element
    FROM my_boards
    WHERE id = 5
    OFFSET 2
)
SELECT element
FROM first_part
UNION ALL

-- the new json element the client wants to add at index 2
SELECT element
FROM (values(5,'{"item_id": 999}'::JSONB)) n(id, element)

UNION ALL -- regular 'union' can mess up the order
SELECT element
FROM second_part) nj;

Is there a more efficient way to insert json objects at specific indexes?

user330315 · Accepted Answer · 2022-04-11 07:02:51Z

1

jsonb_insert() can insert an element into an array at any position:

The following:

with data (order_json) as (
  values ('[{"item_id": 1}, {"item_id": 2}, {"item_id": 3}, {"item_id": 4}]'::jsonb)
)
select jsonb_insert(order_json, '{1}', '{"item_id": 999}')
from data

will return

[{"item_id": 1}, {"item_id": 999}, {"item_id": 2}, {"item_id": 3}, {"item_id": 4}]

answered Apr 11, 2022 at 7:02

user330315

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

2 Comments

BVtp Over a year ago

Embarrassed how I managed to miss that.. Thank you very much. Just one question - if I use an index which is out of bounds, for example 1000 using your example, is there a way to return an error in this case, instead of inserting it as the last element?

user330315 Over a year ago

@BVtp: Quote from the manual "If the last path step is an array index that is out of range, the new value is added at the beginning of the array if the index is negative, or at the end of the array if it is positive."

Collectives™ on Stack Overflow

Efficiently insert elements into json array at specific index in Postgres

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related