Sqllite : search value from json string

Question

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?

Answer 1

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 ;).

Answer 2

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.