sqlsqlitesqlite-json1

Sqllite : search value from json string

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": "raj@ccs.sg",
    "type": "Home"
  },
  {
    "card_email_id": "99",
    "card_id": "88",
    "email": "maulik@ccs.sg",
    "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?

Solution

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