# 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",
},
{
"state": "REVERTING",
}
]}'


Now I want to update this jsonb column to:

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


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 (
, 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.

To SELECT:

SELECT *
FROM   json_test jt
, LATERAL (
SELECT jsonb_set(filter_data
, jsonb_agg(CASE WHEN elem->>'task_id' = '1001'
THEN jsonb_set(elem, '{state}', to_jsonb(text 'DONE'))
ELSE elem
END)) AS filter_data_new
) tp


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

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: