clear markdown compare help best fiddles feedback dbanow.uk
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2591697 fiddles created (45712 in the last week).

CREATE TABLE tbl ( tbl_id int PRIMARY KEY , jar json[] ); INSERT INTO tbl VALUES (1, '{"{\"value\" : \"03334/254146\", \"typeId\" : \"ea4e7d7e-7b87-4628-ba50-f5\"}" ,"{\"value\" : \"03334/254147\", \"typeId\" : \"ea4e7d7e-7b87-4628-ba50-f6\"}" ,"{\"value\" : \"03334/254148\", \"typeId\" : \"ea4e7d7e-7b87-4628-ba50-f7\"}"}') , (2, '{"{\"value\" : \"a\", \"typeId\" : \"x\"}" ,"{\"value\" : \"b\", \"typeId\" : \"y\"}" ,"{\"value\" : \"c\", \"typeId\" : \"z\"}"}');
2 rows affected
 hidden batch(es)


-- array_remove() with text representation (unreliable) SELECT tbl_id , jar, array_length(jar, 1) AS jar_len , jar::text[] AS t, array_length(jar::text[], 1) AS t_len , array_remove(jar::text[], '{"value" : "03334/254147", "typeId" : "ea4e7d7e-7b87-4628-ba50-f6"}'::text) AS t_result , array_remove(jar::text[], '{"value" : "03334/254147", "typeId" : "ea4e7d7e-7b87-4628-ba50-f6"}'::text)::json[] AS j_result FROM tbl;
tbl_id jar jar_len t t_len t_result j_result
1 {"{\"value\" : \"03334/254146\", \"typeId\" : \"ea4e7d7e-7b87-4628-ba50-f5\"}","{\"value\" : \"03334/254147\", \"typeId\" : \"ea4e7d7e-7b87-4628-ba50-f6\"}","{\"value\" : \"03334/254148\", \"typeId\" : \"ea4e7d7e-7b87-4628-ba50-f7\"}"} 3 {"{\"value\" : \"03334/254146\", \"typeId\" : \"ea4e7d7e-7b87-4628-ba50-f5\"}","{\"value\" : \"03334/254147\", \"typeId\" : \"ea4e7d7e-7b87-4628-ba50-f6\"}","{\"value\" : \"03334/254148\", \"typeId\" : \"ea4e7d7e-7b87-4628-ba50-f7\"}"} 3 {"{\"value\" : \"03334/254146\", \"typeId\" : \"ea4e7d7e-7b87-4628-ba50-f5\"}","{\"value\" : \"03334/254148\", \"typeId\" : \"ea4e7d7e-7b87-4628-ba50-f7\"}"} {"{\"value\" : \"03334/254146\", \"typeId\" : \"ea4e7d7e-7b87-4628-ba50-f5\"}","{\"value\" : \"03334/254148\", \"typeId\" : \"ea4e7d7e-7b87-4628-ba50-f7\"}"}
2 {"{\"value\" : \"a\", \"typeId\" : \"x\"}","{\"value\" : \"b\", \"typeId\" : \"y\"}","{\"value\" : \"c\", \"typeId\" : \"z\"}"} 3 {"{\"value\" : \"a\", \"typeId\" : \"x\"}","{\"value\" : \"b\", \"typeId\" : \"y\"}","{\"value\" : \"c\", \"typeId\" : \"z\"}"} 3 {"{\"value\" : \"a\", \"typeId\" : \"x\"}","{\"value\" : \"b\", \"typeId\" : \"y\"}","{\"value\" : \"c\", \"typeId\" : \"z\"}"} {"{\"value\" : \"a\", \"typeId\" : \"x\"}","{\"value\" : \"b\", \"typeId\" : \"y\"}","{\"value\" : \"c\", \"typeId\" : \"z\"}"}
 hidden batch(es)


-- unnest the array and test fields of individual elements SELECT tbl_id, array_agg(j) AS j_new FROM tbl, unnest(jar) AS j -- LATERAL JOIN WHERE j->>'value' <> '03334/254146' AND j->>'typeId' <> 'ea4e7d7e-7b87-4628-ba50-6a5f6e63dbf5' GROUP BY 1;
tbl_id j_new
1 {"{\"value\" : \"03334/254147\", \"typeId\" : \"ea4e7d7e-7b87-4628-ba50-f6\"}","{\"value\" : \"03334/254148\", \"typeId\" : \"ea4e7d7e-7b87-4628-ba50-f7\"}"}
2 {"{\"value\" : \"a\", \"typeId\" : \"x\"}","{\"value\" : \"b\", \"typeId\" : \"y\"}","{\"value\" : \"c\", \"typeId\" : \"z\"}"}
 hidden batch(es)


-- alternative test with row type SELECT tbl_id, array_agg(j) AS j_new FROM tbl, unnest(jar) AS j -- LATERAL JOIN WHERE (j->>'value', j->>'typeId') NOT IN ( ('03334/254146', 'ea4e7d7e-7b87-4628-ba50-6a5f6e63dbf5') ,('a', 'x') ) GROUP BY 1;
tbl_id j_new
1 {"{\"value\" : \"03334/254146\", \"typeId\" : \"ea4e7d7e-7b87-4628-ba50-f5\"}","{\"value\" : \"03334/254147\", \"typeId\" : \"ea4e7d7e-7b87-4628-ba50-f6\"}","{\"value\" : \"03334/254148\", \"typeId\" : \"ea4e7d7e-7b87-4628-ba50-f7\"}"}
2 {"{\"value\" : \"b\", \"typeId\" : \"y\"}","{\"value\" : \"c\", \"typeId\" : \"z\"}"}
 hidden batch(es)


-- UPDATE as requested UPDATE tbl t SET jar = j.jar FROM tbl t1 CROSS JOIN LATERAL ( SELECT ARRAY( SELECT j FROM unnest(t1.jar) AS j -- LATERAL JOIN WHERE j->>'value' <> 'a' AND j->>'typeId' <> 'x' ) AS jar ) j WHERE t1.tbl_id = 2 -- only relevant rows AND t1.tbl_id = t.tbl_id RETURNING t.*;
tbl_id jar
2 {"{\"value\" : \"b\", \"typeId\" : \"y\"}","{\"value\" : \"c\", \"typeId\" : \"z\"}"}
 hidden batch(es)


-- original suggestion (typically slower) UPDATE tbl t SET jar = sub.jar FROM ( SELECT tbl_id, array_agg(j) AS jar FROM (SELECT tbl_id, jar FROM tbl WHERE tbl_id = 2) t -- only relevant rows , unnest(jar) AS j -- LATERAL JOIN WHERE j->>'value' <> 'a' AND j->>'typeId' <> 'x' GROUP BY 1 ) sub WHERE t.tbl_id = sub.tbl_id RETURNING t.*;
tbl_id jar
2 {"{\"value\" : \"b\", \"typeId\" : \"y\"}","{\"value\" : \"c\", \"typeId\" : \"z\"}"}
 hidden batch(es)