Immutable solution version
Delete element from jsonb array in cockaroachdb
Complete solution
Delete element from jsonb array in cockaroachdb: 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)
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?
Matched source
Source: Published skill Original query: "Delete element from jsonb array in cockaroachdb" Key terms: array, cockaroachdb, delete, element, jsonb
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 2 · 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>