I have table named User contain column named userDetails. userDetails column having data in json format like below.
{
"id": "77",
"uid": "247",
"email": "abc@xyz.com",
}
How to query like 'where uid = 247' which is present in this json object.
If the column userDetails is a valid JSON string, you can use the function json_extract():
select *
from user
where json_extract(userDetails, '$.uid') = '247'
If you can't use SQLite's JSON1 extension, you can do it by treating userDetails as a normal string and use the operator LIKE:
select *
from user
where ',' || replace(replace(replace(userDetails, '{', ''), '}', ''), ' ', '') || ',' like '%,"uid":"' || '247' ||'",%'
Replace the ? placeholder with the id you search for, like '247'.
See a simplified demo.