I have table name users
contain column name user_email
. user_email
column having data in json format like below.
[
{
"card_email_id": "98",
"card_id": "88",
"email": "[email protected]",
"type": "Home"
},
{
"card_email_id": "99",
"card_id": "88",
"email": "[email protected]",
"type": "Home"
}
]
I want to query that search value from json string in only email
values.
I already tried REGEXP but that is not supported in sqlite.
is this possible using LIKE
operator or something else?
In SQLite there is a JSON1 extension that you can use it like this:
SELECT *
FROM users, json_each(user_email)
WHERE
json_extract(json_each.value, '$.email') LIKE '%criteria%';
And related question about LIKE
- HTH ;).
Unless the database supports a JSON type, JSON is just a string. You either need to insert the data as proper tables and columns, or use a SQL database with a JSON type like PostgreSQL.
In SQLite you'd turn that data into a new table for card emails that links to the cards table (that I presume exists).
create table card_email (
id integer primary key auto_increment,
card_id integer references cards(id),
email text not null,
type text not null
);
Then parse and insert the JSON into that table.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With