0

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?

1
  • 1
    This would be so much easier with a properly normalized data model. In this case you must unnest the array elements, change the ones you want and the aggregate everything back into the array. Mar 24, 2020 at 6:26

1 Answer 1

1

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

1
  • Awesome, thank you very much. This answers my question.
    – FAZ_PG_SQL
    Mar 24, 2020 at 18:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.