Postgresql – Update a JSON column with WHERE Clause in JSON Column Postgres

jsonpostgresql

I am having trouble writing a WHERE clause to update a JSON column in Postgres database:

Given a table of the form: 

create table Test_Table (
    clusterId INT, 
    owner VARCHAR(50), 
    data JSONB
)

And three rows of data:

insert into Test_Table
values
(1, 'Jacky',{data}),
(2, 'Bob', NULL), 
(3, 'Wolf',NULL);

…where the {data} in row 1 is a json document of the form…

{
    "certified": [
        {
            "Type": "New Instance",
            "Status": "Active",
            "created": "2020-02-03",
            "id_no": "f6b2197c4889"
        },
        {
            "Type": "New Instance",
            "Status": "Failed",
            "created": "2020-02-04",
            "id_no": "216430036272"
        },
        {
            "Type": "New Instance",
            "Status": "Active",
            "created": "2020-02-04",
            "id_no": "3bb515f781c6"
        },
        {
            "Type": "New Instance",
            "Status": "Active",
            "created": "2020-02-04",
            "id_no": "26ae740e9b21"
        },
        {
            "Type": "New Instance",
            "Status": "Active",
            "created": "2020-02-04",
            "id_no": "7e30a80211a2"
        }
    ],
    "in_common": [
        {
            "name": "Kodali",
            "owner": "Jet"
        },
        {
            "name": "Lakers",
            "owner": "Fantastic"
        },
    ]
}

I want to write an update statement to update the data column and change the "Status" to 'Active' where cluster_id is 1 and "id_no" is "216430036272". How do I write a WHERE clause that will update only this attribute?

Best Answer

As noted in comments, this would be a much easier task if the data was properly normalised. However, in the interest of wasting a morning playing with postgres json functions, you could use the following query (fiddle)

with js as (
    select 
        jsonb_array_elements((data->>'certified')::jsonb) as elems
    from test_table
    where clusterid = 1
), to_keep as (
    select elems
    from js
    where js.elems->>'id_no' != '216430036272'
), to_upd as (
    select
        jsonb_set(elems,'{Status}','"Active"') as elems
    from js
    where js.elems->>'id_no' = '216430036272'
), final_array as (
    select jsonb_agg(elems) as elems
    from (
        select elems from to_keep
        union all 
        select elems from to_upd
    ) j
)
update test_table set 
    data = jsonb_set(data,'{certified}',elems)
from final_array fa
where clusterid = 1;

Pretty clearly this is very specifically answering your narrow question, so you'll need to make your own modifications to generalise it. By way of a breakdown though...

I want to ... update the data column and change the "Status" to "Active" where "cluster_id" is 1 and "id_no" is "216430036272"

js

select 
    jsonb_array_elements((data->>'certified')::jsonb) as elems
from test_table
where clusterid = 1

The first common table expression (CTE) filters down to clusterid = 1 out of all the rows in Test_Table

The element certified in your json document is itself an array of objects - as indicated by the [{},{}] notation. For this reason, you need to unwrap the array. jsonb_array_elements turns a single row into many rows. Thus the cell...

+---------+
| array   |
+---------+
| [{},{}] |
+---------+

...becomes...

+---------+
| objects |
+---------+
| {}      |
| {}      |
+---------+

to_keep

Now that you've unwrapped the objects in the array, you need to preserve them. This CTE isolates those objects that you do not want to modify (js.elems->>'id_no' != '216430036272') so that they can be re-aggregated unchanged into the array later.

to_upd

This is the object you've stated you want to update (specified by js.elems->>'id_no' = '216430036272'). jsonb_set(elems,'{Status}','"Active"') modifies the Status element as desired (case sensitive).

final_array

select jsonb_agg(elems) as elems
from (
    select elems from to_keep
    union all 
    select elems from to_upd
) j

You need to turn many rows back into one row to re-create the array-of-objects you decomposed to perform the update in the first place. UNION ALL re-connects the rowsets from to_keep and to_upd and then jsonb_agg causes 

+---------+
| objects |
+---------+
| {}      |
| {}      |
+---------+

...to turn back into...

+---------+
| array   |
+---------+
| [{},{}] |
+---------+

...which allows us to use it in...

The actual UPDATE statement

update test_table set 
    data = jsonb_set(data,'{certified}',elems)
from final_array fa
where clusterid = 1;

You must re-specify clusterid = 1 in order to target the correct row for modification. jsonb_set is seen here again, this time supplanting the entire certified node with the reconstructed data from final_array.elems

Related Question