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?