3

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.

Romaan
  • 2,645
  • 5
  • 32
  • 63
  • 2
    I don't think SQLite has JSON query capabilities. If you always search on the same field (`"index"`) you probably want to denormalize that into a column so that it can be queried. – Thilo Dec 16 '14 at 05:08
  • The JSON objects have different key values – Romaan Dec 16 '14 at 09:14

1 Answers1

6

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';
blerontin
  • 2,892
  • 5
  • 34
  • 60