34

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?

32

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.

7

PostgreSQL has some nice features for JSON storage. You can select the JSON values by doing

SELECT DISTINCT FROM TABLE WHERE foo->title=test AND id=42

You can also index the specific keys on the JSON object if you use the jsonb type.

5

The existing answers for this deal with actually storing the OP's data as JSON (which may have been a better solution to his underlying problem).

However, the actual question was how to get find documents in the EAV-style table provided, based on multiple properties. So, perhaps an answer to that question would be useful.

The standard SQL way of doing what you are saying is INTERSECTION.

(SELECT document FROM table WHERE property="id" AND number=43)
INTERSECTION 
(SELECT document FROM table WHERE property="title" AND string="test")

You can also do this with self joins if you prefer.

SELECT t1.document
FROM table as t1, table as t2
WHERE t1.document = t2.document
AND t1.property="id" AND t1.number=43
AND t2.property="title" AND t2.string="test"

Of course the more "correct" and arguably most efficient way would be to just make a column for each property you need, like 'id' and 'title'.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.