Is it possible to store and query JSON in SQLite

jsonsqlite

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?

Best Answer

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.

Related Solutions

SQLite or CouchDB for Locational Data Mining Project

I think the answer mainly depends on how much time do you want to spend learning new databases vs how much time to you want to spend learning machine learning.

For example PostgreSQL has lots of GIS stuff built-in which I assume could be very useful for your queries. CouchDB has many useful features with their map/reduce stuff but I find it a bit limited. If you think you're going to add many columns later on based on new algorithms you might want to look into the column-oriented databases like Apache Cassandra.

However, I would recommend splitting the problem into two parts:

Data gathering
Data analysis

The first part you seem to have a good plan for already. I would write a super simple app that would just suck in the data from twitter and put it in a table as a BLOB. Or just a file on disk.

When you have that you can go from the raw data and insert it into any kind of backend. Now you can choose backend based on the current problem (algorithm) you're working on, instead of having one solution to fit all your problems. The key point here is to extract exactly the data you need. That way you shouldn't have to consider the fact that the data is nested inside a document from Twitter as you only pick out the parts you need. If you go that way I think an RDBMS would perform very nice as they can run all kinds of queries.

Make sense?

Referencing multiple tables from a single table and store additional data

I think what you're missing is a car_configuration table. This will have an FK to the cars table and have car_config_parts and car_config_tunings as children, like so:

create table car_configuration (
  config_id integer primary key,
  car_id integer references cars (car_id)
);

create table car_config_parts (
  config_id integer references car_configuration (config_id),
  part_id integer references parts (part_id),
  constraint car_config_part_pk primary key (config_id ,part_id)
);

create table car_config_tunings (
  config_id integer references car_configuration (config_id),
  tuning_id integer references tuning (tuning_id),
  constraint car_config_tuning_pk primary key (config_id, tuning_id)
);

You can then include the config_id as a reference in the timeset table (with no reference to cars). You'll need to ensure that you can't change the parts and tunings for a configuration or implement some form of versioning on this table to ensure you can reconstruct the exact details used to set a given time in the past.

Related Question