Is it possible to store and query JSON in SQLite?

I need to store JSON objects in a SQLite database,
and then do complex queries on it.

I did a table like this:

+--------------------------------------+
|document |  property | string | number|
+--------------------------------------+
|foo      |  "title"  | "test" |       | 
+--------------------------------------+
|foo      |  "id"     |        |  42   | 
+--------------------------------------+
|bar      |  "id"     |        |  43   | 
+--------------------------------------+

for the two objects

foo {"title": "test", "id": 42} 
bar {id: 43}

But I can't do "AND" queries, like:

SELECT DISTINCT id  FROM table WHERE title = "test" AND id = 42

as you see, the part after the "WHERE" is total nonsense,
but I have no idea of how to create a query that would do what I want.

So do you think there is a better way to store my data,
or a workaround to do an "AND" query?

And of course, the JSON can contain any property,
so I can't create a table with columns for each properties.

I am using WebSQL, which is SQLite without extensions.

I know my question is pretty specific, but could you help me?

SQLite 3.9 introduced a new extension (JSON1) that allows you to easily work with JSON data .

Also, it introduced support for indexes on expressions, which (in my understanding) should allow you to define indexes on your JSON data as well.

StackExchange Database Administrators Q#122198, answer score: 54