Postgres 10

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

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


-- inefficient
UPDATE tbl
SET    data = (
   SELECT jsonb_agg(jsonb_set(d, '{updatedAt}', '"15711500000"', false))
   FROM   jsonb_array_elements(data) d
   )
RETURNING *;


id
data


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

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

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

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



UPDATE 4


SELECT jsonb_pretty(data) FROM tbl;


jsonb_pretty


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

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

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

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



SELECT 4

id	data
1	[{"id": "1", "status": "test status1", "updatedAt": "15711500000"}, {"id": "2", "status": "test status2", "updatedAt": "15711500000"}, {"id": "3", "status": "test status3"}]
2	[{"id": "4", "status": "test status4"}]
3	[{"id": "5", "status": "test status5", "updatedAt": "15711500000"}]
4	[{"id": "6", "status": "test status5", "updatedAt": "15711500000"}]