CODEWITHSUNDEEP
sqlarraysjsonpostgresqlpostgresql-9.5
Vladyslav
Vladyslav

Reputation: 2265

Update jsonb list item in PostgreSQL

I am try to update key name of 1 field in my jsonb list.

Initial data

select jsonb_array_elements(status_history) from deals;
>>> [{"date": "2020-10-02T12:05:02.728691", "state": "gathering_info"}, {"date": "2020-10-08T10:15:20.798500", "state": "archived"}, {"date": "2020-10-08T10:43:17.651033", "state": "gathering_info"}]
>>> ...

I can retrieve each element with this function

select jsonb_array_elements(status_history) from deals;

>>> {"date": "2020-10-02T11:51:55.624263", "state": "gathering_info"}

I want to update state key to be status and I try to do this

update deals set elem=elem & {'status': elem->'state'} from (select jsonb_array_elements(status_history) from deals) elem;
update deals set elem->'status' = elem->'state' from (select jsonb_array_elements(status_history) from deals) elem;

But this is not work. How I can do this?

Upvotes: 1

Views: 66

Answers (1)

GMB
GMB

Reputation: 222422

You can unnest the array while keeping track of the position of each object, change the key name in each individual element using operators - and ||, then reaggregate. Assuming that the primary key of your table is id, you could phrase this as:

update deals d
set status_history = d1.status_history
from (
    select d.id, 
        json_agg(
            x.obj - 'state' || jsonb_build_object('status', x.obj -> 'state')
            order by x.n
        ) as status_history
    )
    from deals d
    cross join lateral jsonb_array_elements(d.status_history) with ordinality as x(obj, n)
    group by d.id
) d1
where d1.id = d.id

Upvotes: 1

Related Questions