当前位置：文江博客话题详情

从cockaroachdb中的jsonb阵列删除元素

发布于 2025-01-31 23:32:27 字数 185 浏览 4 评论 0原文

我有JSONB 标签的字段：[{“ value”：“ tag1”}]

我需要做类似此类的事情更新table1 set table tabs = tags = tags -'{“ value”：“ tag1 “}' - 但这不起作用，

我应该执行什么查询以从数组中删除元素？

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

饮湿 2025-02-07 23:32:27

假设您的桌子看起来

 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在set 更新语句的一部分中不允许使用，因此我们可以't只是做set hash = jsonb_set（hash，array ['tags']，＆lt;该函数链＆gt;。相反，我的解决方案在允许的位置使用，然后插入Select语句中使用它选择回到表中的结果

。字符串操纵，但这很脆弱，因为您需要担心嵌套在JSON中的对象中的逗号。

Assuming your table looks like

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

you can do this with the following statement:

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

The actual json operation here is straightforward, if longwinded. We're nesting the following functions:

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

What's tricky is that json_array_elements isn't allowed in the SET part of an UPDATE statement, so we can't just do SET hash = jsonb_set(hash, array['tags'], <that function chain>. Instead, my solution uses it in a SELECT statement, where it is allowed, then inserts the result of the select back into the table. Every attempted insert will hit the ON CONFLICT clause, so we get to do that UPDATE set using the already-computed json array.

Another approach here could be to use string manipulation, but that's fragile as you need to worry about commas appearing inside objects nested in your json.

回复收藏 0 原文

时间你老了 2025-02-07 23:32:27

您可以使用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"}'
            );

You can use json_remove_path to remove the element if you know its index statically by passing an integer.

Otherwise, we can do a simpler subquery to filter array elements and then json_agg to build a new array.

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

Then we can remove the tag which has {"value": "tag1"} like:

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

回复收藏 0 原文

~没有更多了~