How to update complex jsonb column? I have a table with following definition:

create table json_test (
    filter_data jsonb);


and i insert to it values like this:

'{"task_packets": [
    {
        "state": "PROCEEDING",
        "task_id": 1001
    },
    {
        "state": "REVERTING",
        "task_id": 1002
    }
]}'


Now I want to update this jsonb column to:

'{"task_packets": [
    {
        "state": "DONE",
        "task_id": 1001
    },
    {
        "state": "REVERTING",
        "task_id": 1002
    }
]}'


i.e. I want to change state for value with specified task_id inside array of task_packets. I suggest somehow to use jsonb_set() function in combination with #- operator (first delete value from array, then append to it with updated state).
How can I do it?
Like I commented, this would be more efficient with a normalized DB layout, with a table like this

CREATE TABLE task_packets (
  task_id int PRIMARY KEY
, state text NOT NULL
-- or: state_id int NOT NULL REFERENCES state(state_id) ...
);


Among other things, we can have a PK constraint enforcing unique task_id numbers. And the UPDATE you want is trivial.



But to answer the question asked:

To SELECT:

SELECT *
FROM   json_test jt
     , LATERAL (
   SELECT jsonb_set(filter_data
                  , '{task_packets}'
                  , jsonb_agg(CASE WHEN elem-&gt;&gt;'task_id' = '1001'
                               THEN jsonb_set(elem, '{state}', to_jsonb(text 'DONE'))
                               ELSE elem
                             END)) AS filter_data_new
   FROM   jsonb_array_elements(filter_data-&gt;'task_packets') elem
   ) tp
WHERE  jt.filter_data @&gt; '{"task_packets": [{"task_id": 1001}]}';


I suggest a LATERAL join, among other things to exclude the possibility of multiple matching rows that might be lumped together incorrectly in a plain join.

To UPDATE:

UPDATE json_test
SET    filter_data =
   (
   SELECT jsonb_set(filter_data
                  , '{task_packets}'
                  , jsonb_agg(CASE WHEN elem-&gt;&gt;'task_id' = '1001'
                                THEN jsonb_set(elem, '{state}', to_jsonb(text 'DONE'))
                                ELSE elem
                              END))
   FROM   jsonb_array_elements(filter_data-&gt;'task_packets') elem
   )
WHERE  filter_data @&gt; '{"task_packets": [{"task_id": 1001}]}';


The same can be implemented with a correlated subquery in the UPDATE (or in the SELECT as well).

To make this fast for big tables, be sure to have an appropriate index, ideally a jsonb_path_ops index:


What's the proper index for querying structures in arrays in Postgres jsonb?