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.
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
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’.