56

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?

8
  • 2
    If that is really important for your application you are probably better off using either a real document store ("NoSQL") or e.g. switch to Postgres which combines very efficient JSON storage with strong relational features. Nov 26, 2015 at 15:16
  • 4
    Read the documentation.
    – CL.
    Nov 26, 2015 at 16:27
  • @CL. Yupp. That's also the first result when googling for "sqlite json" :)
    – Izzy
    Nov 26, 2015 at 18:36
  • You have to accounts and should merge them: dba.stackexchange.com/users/81459/tuxlu and dba.stackexchange.com/users/81513/tuxlu You can use this help page: dba.stackexchange.com/help/merging-accounts Nov 27, 2015 at 10:33
  • 2
    @Izzy First of all, for me at least this question is now the first result, not the docs. Secondly, that extension is not a good answer to OP, who explicitly stated using 'WebSQL, which is SQLite without extensions'.
    – OJFord
    Sep 8, 2018 at 13:12

3 Answers 3

52

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.

10

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.

4
  • 3
    Is there any 1:1 equivalent to this in Sqlite3 these days in 2020+? Jun 19, 2020 at 7:53
  • 12
    Doesn't answer OP question... not about PG Sep 15, 2020 at 23:00
  • Did you consider that maybe OP was already using Postgres in production and wanted to mimic PSQL's capabilities for testing ?
    – jdkealy
    Sep 25, 2020 at 22:27
  • 1
    @NickSweeting in 2022 the -> and ->> operators work in sqlite sqlite.org/json1.html#jptr select foo->'title' from atable
    – Matt
    May 4 at 1:39
7

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.