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 (id int, column_name jsonb);
INSERT INTO tbl VALUES
  (1, '{"values": [11, 22, 33]}')
, (2, '{"values": ["11", "22", "33"]}')
, (3, '{"values": ["11", 22, "33"]}')  -- mixed types!
, (4, '{"values": [11, "22", 33]}');  -- mixed types!

CREATE TABLE



INSERT 0 4


-- lateral join:
SELECT jsonb_set(column_name, '{values}', new_values) AS column_name
FROM   tbl t
LEFT   JOIN LATERAL (
    SELECT jsonb_agg(val) AS new_values
    FROM   jsonb_array_elements(t.column_name -> 'values') val
    WHERE  val <> jsonb '33'  -- do not cast to integer!
   ) sub ON true;


column_name


{"values": [11, 22]}

{"values": ["11", "22", "33"]}

{"values": ["11", 22, "33"]}

{"values": [11, "22"]}



SELECT 4


-- correlated subquery: a bit faster, but harder to read
SELECT jsonb_set(column_name
               , '{values}'
               , (SELECT jsonb_agg(val)
                  FROM   jsonb_array_elements(t.column_name -> 'values') x(val)
                  WHERE  val <> jsonb '33')
                 ) AS column_name
FROM   tbl t;


column_name


{"values": [11, 22]}

{"values": ["11", "22", "33"]}

{"values": ["11", 22, "33"]}

{"values": [11, "22"]}



SELECT 4


-- cast can raise exception (or do the wrong thing)
select jsonb_set(column_name, '{values}', new_values) new_column_name
from tbl t
left join lateral (
	select jsonb_agg(val) new_values
	from jsonb_array_elements(t.column_name -> 'values') x(val)
	where val::int <> 33
) x on 1 = 1;

ERROR:  cannot cast jsonb string to type integer