Postgresql - json update key of object in array

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 *;