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?

Gramicidin

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

Gujarat

This is typically not yet available on Android ... but the question didn't ask for that. :) – Disembogue

@Gujarat when i run in that query it gives error that "no function : json_extract" – Gramicidin

Did you load JSON1 extension? It is a loadable extension ;). – Gujarat

Note that I do not believe this uses indexes. You'll be parsing and scanning every single row for every query. – Adeliaadelice

@Gujarat json_extract use in json object. i want to search from json array and there is many times "email" object in json array. – Gramicidin

I can't test the query, but I updated query to check each object ;). – Gujarat

@Gujarat this is work in sqlite database manager software(v3.7.0). but when i run this query in Phone model = OPPO a37f, Sqlite version = 3.8.6.1 it gives below error. Caused by: android.database.sqlite.SQLiteException: near "(": syntax error (code 1): , while compiling: SELECT cl_contacts_cards_email50, cl_contacts_cards_address48 FROM WC_contacts_list, json_each(cl_contacts_cards_email50) as eml, json_each(cl_contacts_cards_address48) as address – Gramicidin

I'm not familiar with that error; Please ask it in another question ;). – Gujarat

@Gujarat if we use "json_each" and if any row contain empty value then it doesn't display in select query. any solution for that? – Gramicidin

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.

Adeliaadelice

yes this is option to create another table and save it. i am using sqlite in android and ios, so no other option like POstgreSQL. It would be better if not to create another table for this. – Gramicidin

@Maulikpatel Why do you say it would be better to not make another table? – Adeliaadelice

we have around 1M rows in tables and 8 columns stores json string data. if we are going to create separate tables for json column, then there will be total 9 tables. and if we have to join that 9 tables which may decrease performance in sqlite in mobile app. – Gramicidin

@Maulikpatel For a properly designed SQL database with good indexes, 1 million rows and 9 tables is nothing. You shouldn't have to join all 9 tables in a single query, but joins are very efficient if they're using integer primary keys. This is what relational databases are designed to do for the last 40 years: relationships. JSON fields subvert the efficiency of a relational database, and your ORM, by basically turning the database into an indexed text file it has to parse over and over again. I'm available to help out with this, see my profile for info. – Adeliaadelice

I am referring 1M rows in sqlite. let me check performance in Sqlite and get back to you. – Gramicidin