Delete element from jsonb array in cockaroachdb

Question

I got field with jsonb tags: [{"value": "tag1"}]

I need to do something like this update table1 set tags = tags - '{"value": "tag1"}' - but this don't work

What query should I execute to delete element from array?

Answer 1

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"}'
            );