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, data jsonb);
INSERT INTO tbl VALUES
  (1, '[{"id":"1","status":"test status1","updatedAt":"1571145003"}
       ,{"id":"2","status":"test status2","updatedAt":"1571145323"}
       ,{"id":"3","status":"test status3"}]')  -- last element does not qualify
, (2, '[{"id":"4","status":"test status4"}]')  -- nothing to update
, (3, '[{"id":"5","status":"test status5","updatedAt":"1571150000"}]')  -- nor here
, (4, '[{"id":"6","status":"test status5","updatedAt":null}]')  -- but null must change
;

CREATE TABLE



INSERT 0 4


-- Postgres 12 or later
SELECT data @? '$.updatedAt ? (@ != "1571150000")' AS qualifies, *
FROM   tbl;


qualifies
id
data


t
1
[{"id": "1", "status": "test status1", "updatedAt": "1571145003"}, {"id": "2", "status": "test status2", "updatedAt": "1571145323"}, {"id": "3", "status": "test status3"}]

f
2
[{"id": "4", "status": "test status4"}]

f
3
[{"id": "5", "status": "test status5", "updatedAt": "1571150000"}]

t
4
[{"id": "6", "status": "test status5", "updatedAt": null}]



SELECT 4


-- Postgres 12 or later
UPDATE tbl
SET    data = (
   SELECT jsonb_agg(jsonb_set(dt, '{updatedAt}', '"15711500000"', false))
   FROM   jsonb_array_elements(data) dt
   )
WHERE  data @? '$.updatedAt ? (@ != "1571150000")'
RETURNING *;


id
data


1
[{"id": "1", "status": "test status1", "updatedAt": "15711500000"}, {"id": "2", "status": "test status2", "updatedAt": "15711500000"}, {"id": "3", "status": "test status3"}]

4
[{"id": "6", "status": "test status5", "updatedAt": "15711500000"}]



UPDATE 2


SELECT jsonb_pretty(data) FROM tbl;


jsonb_pretty


[
    {
        "id": "4",
        "status": "test status4"
    }
]

[
    {
        "id": "5",
        "status": "test status5",
        "updatedAt": "1571150000"
    }
]

[
    {
        "id": "1",
        "status": "test status1",
        "updatedAt": "15711500000"
    },
    {
        "id": "2",
        "status": "test status2",
        "updatedAt": "15711500000"
    },
    {
        "id": "3",
        "status": "test status3"
    }
]

[
    {
        "id": "6",
        "status": "test status5",
        "updatedAt": "15711500000"
    }
]



SELECT 4

qualifies	id	data
t	1	[{"id": "1", "status": "test status1", "updatedAt": "1571145003"}, {"id": "2", "status": "test status2", "updatedAt": "1571145323"}, {"id": "3", "status": "test status3"}]
f	2	[{"id": "4", "status": "test status4"}]
f	3	[{"id": "5", "status": "test status5", "updatedAt": "1571150000"}]
t	4	[{"id": "6", "status": "test status5", "updatedAt": null}]