Postgres, update statement from jsonb array with sorting

Question

I have a jsonb column in my table - it contains array of json objects one of fields in these objects is a date. Now i added new column in my table of type timestamp. And now i need statement which hepls me to update new column with most recent date value from jsonb array column af a same record.

Following statement works great on selecting most recent date from jsonb array column of certain record:

select history.date 
from document, 
     jsonb_to_recordset(document.history) as history(date date)  
where document.id = 'd093d6b0-702f-11eb-9439-0242ac130002'
order by history.date desc 
limit 1;

On update i have tried following:

update document 
  set status_recent_change_date = subquery.history.date
from (
   select id, history.date 
   from document, 
        jsonb_to_recordset(document.history) as history(date date)
) as subquery 
where document.id = subquery.id 
order by history.date desc 
limit 1;

Last statement does not working.

As documented in the manual UPDATE does neither support order by nor limit - if you really want to limit that to one row, you need to move that into the subquery — a_horse_with_no_name

S-Man S-Man · Accepted Answer · 2021-02-18T07:26:06

demo:db<>fiddle

UPDATE document d
SET status_recent_change_date = s.date
FROM (
    SELECT DISTINCT ON (id)                       
        *
    FROM document,
        jsonb_to_recordset(document.history) AS history(date date) 
    ORDER BY id, history.date DESC
) s 
WHERE d.id = s.id;

Using LIMIT would not work because you limit the entire output of your SELECT statement. But you want to limit the output of each document.id. This can be done using DISTINCT ON (id).

This result can be used to update each record using their id values.

Postgres, update statement from jsonb array with sorting

2 Answers