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?
CodePudding user response:
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}]