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

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.

Answer 2

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