How to update complex jsonb column?
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?