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