Update a JSON column with WHERE Clause in JSON Column Postgres

Question

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?

• user1822: 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.

Answer 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

• FAZ_PG_SQL: Awesome, thank you very much. This answers my question.