Search value from json in sqlite - Android

Question

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.

Answer 1

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.