Postgres 12

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

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

CREATE TABLE



INSERT 0 2


-- 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"}"}



SELECT 2


-- 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"}"}



SELECT 2


-- 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"}"}



SELECT 2


-- 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"}"}



UPDATE 1


-- 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"}"}



UPDATE 1

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"}"}

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"}"}

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"}"}