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": "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?

score 23 · Accepted Answer · edited May 23 '17 at 12:16

23

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

edited May 23 '17 at 12:16

Community

1
1

answered Dec 31 '16 at 08:50

shA.t

16,580
5
54
111

1

This is typically not yet available on Android ... but the question didn't ask for that. :) – CL. Dec 31 '16 at 10:31
@shA.t when i run in that query it gives error that "no function : json_extract" – Maulik patel Dec 31 '16 at 10:55
1

Did you [load](https://sqlite.org/loadext.html) JSON1 extension? It is a loadable extension ;). – shA.t Dec 31 '16 at 11:06
Note that I do not believe this uses indexes. You'll be parsing and scanning every single row for every query. – Schwern Dec 31 '16 at 18:01
1

@shA.t json_extract use in json object. i want to search from json array and there is many times "email" object in json array. – Maulik patel Jan 04 '17 at 10:22
I can't test the query, but I updated query to check each object ;). – shA.t Jan 04 '17 at 10:35
@shA.t 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 – Maulik patel Jan 06 '17 at 05:42
I'm not familiar with that error; Please ask it in another question ;). – shA.t Jan 06 '17 at 06:22
@shA.t if we use "json_each" and if any row contain empty value then it doesn't display in select query. any solution for that? – Maulik patel Jan 17 '17 at 12:28

score 1 · Answer 2 · answered Dec 31 '16 at 05:54

1

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.

answered Dec 31 '16 at 05:54

Schwern

153,029
25
195
336

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. – Maulik patel Dec 31 '16 at 08:00
1

@Maulikpatel Why do you say it would be better to not make another table? – Schwern Dec 31 '16 at 18:00
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. – Maulik patel Jan 02 '17 at 05:56
@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. – Schwern Jan 04 '17 at 20:58
I am referring 1M rows in sqlite. let me check performance in Sqlite and get back to you. – Maulik patel Jan 05 '17 at 06:39

Sqllite : search value from json string

2 Answers2