Let's say I have a table:
SELECT * FROM settings;
| id | name | strategies |
| -- | --- | --- |
| 1 | default | [{name: xyz, enabled: true}, {name: bot2, enabled: true}] |
| 2 | new1 | [{name: bot2, enabled: true}, {name: xyz, enabled: false}] |
I want to add a new object {name: bot1, enabled: true} before bot2.
I'm trying to use a solution from this answered question:
WITH bot2_index AS (SELECT
pos- 1 AS bot2_index
FROM
settings,
jsonb_array_elements(strategies) WITH ordinality arr(elem, pos)
WHERE
NAME = 'default'
AND elem->>'name' = 'bot2')
UPDATE settings
SET strategies = jsonb_set(strategies, '{bot2_index}', '{
"name": "bot1",
"enabled": false
}', TRUE);
but I get
ERROR: path element at position 1 is not an integer: "bot2_index"
bot2_index is of type bigint so why this syntax doesn't work?
I've also tried other variations like just bot2_index, bot2_index::int, bot2_index::string, or even run it as two separate queries (like in the accepted answer) but it doesn't work either.
EDIT #1
This syntax works, but it seems to replace the element at that index, rather than appending the element before or after the element at given index - how can I make it work like JS splice() function?
UPDATE settings
SET strategies = jsonb_set(strategies, concat('{',(SELECT
pos- 1 AS bot2_index
FROM
settings,
jsonb_array_elements(strategies) WITH ordinality arr(elem, pos)
WHERE
NAME = 'default'
AND elem->>'name' = 'js:bot2'),'}')::text[], '{
"name": "bot1",
"enabled": false
}', TRUE);
First of all for your current query you should use it like below:
WITH bot2_index AS (SELECT
pos- 1 AS bot2_index
FROM
settings,
jsonb_array_elements(strategies) WITH ordinality arr(elem, pos)
WHERE
name = 'default'
AND elem->>'name' = 'bot2')
UPDATE settings
SET strategies = jsonb_set(strategies, array[bot2_index::text], '{
"name": "bot1",
"enabled": false
}'::jsonb, false) from bot2_index;
But query will replace the existing one DEMO
You should use jsonb_insert for it instead of jsonb_set.
WITH bot2_index AS (SELECT
pos- 1 AS bot2_index
FROM
settings,
jsonb_array_elements(strategies) WITH ordinality arr(elem, pos)
WHERE
name = 'default'
AND elem->>'name' = 'bot2')
UPDATE settings
SET strategies = jsonb_insert(strategies, array[bot2_index::text], '{
"name": "bot1",
"enabled": false
}'::jsonb, false) from bot2_index;