I am having trouble writing a `WHERE` clause to update a JSON column in Postgres database:
Given a table of the form:
```sql
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...
```json
{
"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?
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][1], you could use the following query ([fiddle][2])
```sql
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`
```sql
select
jsonb_array_elements((data->>'certified')::jsonb) as elems
from test_table
where clusterid = 1
```
The first [common table expression (CTE)][3] 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`
```sql
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`][4] 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
```sql
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]: https://www.postgresql.org/docs/current/functions-json.html
[2]: https://dbfiddle.uk/?rdbms=postgres_12&fiddle=b0a03e025654a407a088a08b2c4101fb
[3]: https://www.postgresql.org/docs/current/queries-with.html
[4]: https://www.postgresql.org/docs/current/queries-union.html