CODEWITHSUNDEEP
postgresqlsql-updatejsonbpostgresql-10
van_folmert
van_folmert

Reputation: 4507

Insert element in JSONB Array - Postgresql

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);

Upvotes: 3

Views: 377

Answers (1)

Akhilesh Mishra
Akhilesh Mishra

Reputation: 6130

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;

DEMO

Upvotes: 2

Related Questions