Search code examples
jsonsqliteduktape

Postgres equivalient in SQLite to search JSON object

I have got the below Postgres Query:

db.queryAsync('SELECT _id, value FROM ' + store + ' WHERE (value->>$1)=$2;', [index, id]);

I want to write the same in SQLite. I have created the value field as TEXT type, but then not sure how to query. I am using SQLite and JavaScript running on Duktape.

Solution

  • You could try the JSON1 extension of SQLite. Altough it is marked as "Draft" it should be quite stable.

    The SQLite statement corresponding to your PostgreSQL example would be:

    SELECT _id, value FROM store WHERE json_extract(value, '$.someField') = 'some value';