PostgreSQL – How to Update Complex JSONB Column

jsonpostgresqlpostgresql-9.5update

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?

Best Answer

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->>'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->'task_packets') elem
   ) tp
WHERE  jt.filter_data @> '{"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->>'task_id' = '1001'
                                THEN jsonb_set(elem, '{state}', to_jsonb(text 'DONE'))
                                ELSE elem
                              END))
   FROM   jsonb_array_elements(filter_data->'task_packets') elem
   )
WHERE  filter_data @> '{"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:

Related Question