I have a column in my table containing a JSON statuses_json:
{
"demoStatus" : "true",
"productionStatus": "false"
}
I would like to retrieve a value where the key is LIKE some string.
For example, if I pass in "demo", I want to retrieve the value for the key demoStatus.
Right now I am able to retrieve values when passing the exact key:
`statuses_json->>'productionStatus' = 'false' `;
Extract the keys and run a query on it:
select *
from json_object_keys('{
"demoStatus" : "true",
"productionStatus": "false"
}') k where k like '%demo%';
I don't have a new enough version of postgresql but jsonb_path_query looks interesting, too. Then used statuses_json->>(...) to extract the corresponding value(s).
select statuses_json from your_table
where statuses_json->>(
select prop
from json_object_keys(statuses_json) as prop
where prop like 'demo%'
) = 'false';
= operator there