CODEWITHSUNDEEP
arraysjsonpostgresqljsonbpostgresql-9.6
Sabri Aziri
Sabri Aziri

Reputation: 4184

Postgresql - json update key of object in array

I have the following json in my database:

[
    {"schedule_id":1,"datetime":"2017-05-12 00:00:00","status":"available"},
    {"schedule_id":2,"datetime":"2017-05-12 02:00:00","status":"available"}
]

and I want to update the status property of the object where schedule_id = 2.

I found something like this but this requires the index of the object in array:

select 
jsonb_set('[
    {"schedule_id":1,"datetime":"2017-05-12 00:00:00","status":"available"},
    {"schedule_id":2,"datetime":"2017-05-12 02:00:00","status":"available"}
]', 
'{1, status}', 
'"booked"')

`

Upvotes: 3

Views: 3012

Answers (1)

klin
klin

Reputation: 121534

You can unnest the array with jsonb_array_elements(), modify filtered elements and finally aggregate the result into a json array using jsonb_agg(). Use simply concatenation operator to replace an inner json object.

Example (jsonb_pretty() not necessary, used for a nice output):

with my_table(data) as (
values(
    '[
        {"schedule_id":1,"datetime":"2017-05-12 00:00:00","status":"available"},
        {"schedule_id":2,"datetime":"2017-05-12 02:00:00","status":"available"}
    ]'::jsonb)
)

select
    jsonb_pretty(jsonb_agg(
        case value->>'schedule_id'
        when '2' then value || '{"status":"booked"}'
        else value
        end
    ))
from my_table,
jsonb_array_elements(data);

                jsonb_pretty                
--------------------------------------------
 [                                         +
     {                                     +
         "status": "available",            +
         "datetime": "2017-05-12 00:00:00",+
         "schedule_id": 1                  +
     },                                    +
     {                                     +
         "status": "booked",               +
         "datetime": "2017-05-12 02:00:00",+
         "schedule_id": 2                  +
     }                                     +
 ]
(1 row)

The easiest way to use this for updating a table is to enclose the query in a custom function, e.g.:

create or replace function change_schedule_status(schedule jsonb, id int, status text)
returns jsonb language sql as $$
    select
        jsonb_agg(
            case value->>'schedule_id'
            when id::text then value || jsonb_build_object('status', status)
            else value
            end
        )
    from jsonb_array_elements(schedule);
$$;

update search
set schedule = change_schedule_status(schedule, 2, 'booked')
where id = 1  -- probably the table has a primary key?
returning *;

Upvotes: 4

Related Questions