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