Query to update the JSON that does not contain a specific key
0
votes
1
answer
615
views
I want to query a table that has JSON data. The data of the column **detail** in the table **details** are as follows.
id | 1
detail | {
myCompany: [{ email: 'company@company.com', inProfit: true }],
myData: [
{ email: 'example@example.com', personalUse: true },
{ email: 'test@test.com', personalUse: false },
{ email: 'test@example.com' },
],
};
The **JSON** value of the column **detail** is as follows
{
"myCompany":[
{
"email":"company@company.com",
"inProfit":true
}
],
"myData":[
{
"email":"example@example.com",
"personalUse":true
},
{
"email":"test@test.com",
"personalUse":false
},
{
"email":"test@example.com",
}
]
}
I want a query that updates the myData
. If the myData
field doesn't contain personalUse
then update that field with personalUse:true
. In the given example { email: 'test@example.com' }
field should update as { email: 'test@example.com', personalUse: true }
You can use the below queries to try it locally.
CREATE TABLE details (id bigserial primary key, detail json not null);
INSERT INTO details (detail)
VALUES
('{"myCompany":[{"email":"company@company.com", "inProfit":true } ],
"myData":[{"email":"example@example.com", "personalUse":true },
{"email":"test@test.com", "personalUse":false },
{"email":"test@example.com"} ] }');
Please, someone, help me out.
Asked by Rich
(1 rep)
Apr 23, 2020, 03:27 PM
Last activity: Aug 8, 2025, 10:08 PM
Last activity: Aug 8, 2025, 10:08 PM