Insert element in JSONB Array - Postgresql

Question

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

Akhilesh Mishra · Accepted Answer · 2020-07-22 11:36:05Z

2

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

edited Jul 22, 2020 at 11:36

answered Jul 22, 2020 at 11:17

Akhilesh Mishra

6,1503 gold badges20 silver badges37 bronze badges

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

1 Comment

Akhilesh Mishra Over a year ago

please check now

Collectives™ on Stack Overflow

Insert element in JSONB Array - Postgresql

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related