在CockroachDB中从jsonb数组中删除元素

Question

我有一个jsonb类型的字段，例如 tags: [{"value": "tag1"}]

我需要执行像这样的操作update table1 set tags = tags - '{"value": "tag1"}'，但是这个语句不起作用

请问我应该执行什么查询来删除数组中的元素？

- Егор Лебедев

Answer 1

假设您的表格长这样：

 CREATE TABLE public.hasjsonb (
                  id INT8 NOT NULL,
                  hash JSONB NULL,
                  CONSTRAINT hasjsonb_pkey PRIMARY KEY (id ASC)
             )

您可以使用以下语句来完成此操作：

INSERT INTO hasjsonb(id, hash) 
    (SELECT id,array_to_json(array_remove(array_agg(json_array_elements(hash->'tags')),'{"value": "tag1"}')) 
        FROM hasjsonb 
        GROUP BY id
    ) 
    ON CONFLICT(id) DO UPDATE SET hash = jsonb_set(hasjsonb.hash, array['tags'], excluded.hash);

这里实际的JSON操作很简单，但有些冗长。我们嵌套了以下函数：

hash->'tags' -- extract the json value for the "tags" key
json_array_elements -- treat the elements of this json array like rows in a table
array_agg -- just kidding, treat them like a regular SQL array
array_remove -- remove the problematic tag
array_to_json -- convert it back to a json array

很棘手的是，json_array_elements不允许出现在UPDATE语句的SET部分，因此我们不能只使用SET hash = jsonb_set(hash, array['tags'], <that function chain>。相反，我的解决方案在SELECT语句中使用它，因为它是允许的，然后将选择的结果插入回表中。每次尝试插入都会触发ON CONFLICT子句，因此我们可以使用已计算的JSON数组进行UPDATE set。

这里的另一种方法可能是使用字符串操作，但这很脆弱，因为您需要担心逗号出现在嵌套在您的JSON中的对象中。

- histocrat

---

实际上 tags 是一列，但我理解你的例子了，明天上班我会尝试。 - Егор Лебедев

我收到了错误信息 unknown signature array_agg(jsonb)，似乎没有这样的函数。 - Егор Лебедев

你有没有漏掉 json_array_elements 函数？它仍然是必需的。 - histocrat

Answer 2

如果您知道元素的索引，可以使用json_remove_path通过传递一个整数来删除该元素。

否则，我们可以使用更简单的子查询来过滤数组元素，然后使用json_agg构建一个新数组。

create table t (tags jsonb);
insert into t values ('[{"value": "tag2"}, {"value": "tag1"}]');

然后，我们可以移除具有{"value": "tag1"}的标签，如下：

UPDATE t
   SET tags = (
                SELECT json_agg(tag)
                  FROM (
                        SELECT *
                          FROM ROWS FROM (json_array_elements(tags)) AS d (tag)
                       )
                 WHERE tag != '{"value": "tag1"}'
            );

- ajwerner