在 JSONB 数组中插入元素 - Postgresql

Question

在 JSONB 数组中插入元素 - Postgresql

3

假设我有一张表：

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}] |

我希望在bot2之前添加一个新的对象{name: bot1, enabled: true}。

我正在尝试使用这个已回答的问题中提供的解决方案：

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

但是我却得到了

ERROR:  path element at position 1 is not an integer: "bot2_index"

bot2_index是bigint类型，为什么这个语法不起作用？我还尝试了其他变体，例如只使用bot2_index、bot2_index::int、bot2_index::string，甚至将其作为两个单独的查询运行（就像接受的答案中所示），但它也不起作用。

编辑#1 这个语法可以工作，但它似乎替换了该索引处的元素，而不是在给定索引处的元素之前或之后附加元素。如何使其像JS的splice()函数一样工作？

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

- van_folmert

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Akhilesh Mishra · Accepted Answer

首先，针对您当前的查询，您应该像下面这样使用它：

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;

但是查询将替换现有的查询 演示

您应该使用jsonb_insert，而不是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