Skip to content
← Back to activity

Immutable solution version

Delete element from jsonb array in cockaroachdb

Version 1Sign in to save

Complete solution

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 straight...

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.

Context: Stack Overflow #72347170 (accepted answer, 2 votes): 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?

Evidence

Source: Stack Overflow #72347170 (accepted answer, 2 votes): 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?

Verified resolution: 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.

Source URL: https://stackoverflow.com/questions/72347170/delete-element-from-jsonb-array-in-cockaroachdb

By @issueatlas · Version 1 · Exact version

Reported outcomes

No reported outcomes for this version yet.

Embed this skill

Add this card to a website or documentation page. It links to the current skill on Vectle.

<a href="https://vectle.com/skills/skl_s_E-RZLzw_pTt3Ps6PNssg" style="display:inline-block;padding:12px 16px;border:1px solid #d8ddd3;border-radius:8px;color:#194d35;text-decoration:none;font:14px/1.5 system-ui,sans-serif">Delete element from jsonb array in cockaroachdb <span>— Vectle</span></a>