I can already use JSON in Sqlite by doing parsing in the client outside of the Sqlite API. Any examples where I'd want to use this instead? I'm guessing for where clauses in queries, perhaps?
Can I create an index on a field within a JSON tuple?
It's nice to have the option for the data to still be queryable without having to make it a first class schema with all schema setup involved.
It isn't for 1st class data that is used frequently. But when you might only need the data on occassion its nice to have productivity-wise.
frequently|used|data|in|these|cols|full_json_response
The JSON module is really a god send! It allows to do things that otherwise would be extremely painful, difficult or not ergonomic.
Just to take few examples, here (http://redbeardlab.tech/rediSQL/blog/JaaS/) is a simple way to store JSON and doing manipulation on it, it would have been impossible without the JSON module.
In this other examples (http://redbeardlab.tech/rediSQL/blog/golang/using-redisql-wi...) I use the module to avoid computation on the client and I leave the extraction of the value to SQLite, very convenient if you ask me.
Please get in touch, there is my email in the profile! There is a small community of people that try to live with open source software.
If your product is somehow usable with RediSQL we could do marketing together or bundle our products, there are a lot of synergy in software that can be enhanced.
sqlite> create table a (id int primary key, j json);
sqlite> insert into a values (1, '{"hello":"world"}');
sqlite> create index idx_a on a (json_extract(j, '$.hello'));
sqlite> explain query plan select * from a where json_extract(j, '$.hello') = 'world';
QUERY PLAN
`--SEARCH TABLE a USING INDEX idx_a (<expr>=?)
sqlite> explain query plan select * from a where json_extract(j, '$.foo') = 'world';
QUERY PLAN
`--SCAN TABLE aBefore this I loaded and filtered all the data in memory. Now, with json_extract, I can both index and filter the data using SQLite, which is a massive performance boost.
This can be because, actually, you have a json blob that is associated with the row e.g. I have a script that scrapes some public registries of historic monuments (I have dull hobbies!) and it just stores the responses in a json column. Its convenient.
Another way these 'dynamic columns' are used is to flatten one-to-many relationships. For example, I have a database where account managers can add arbitrary tags to customers. The classic approach would be to have a customer table, then a 1:M into a tag table with the key value, and then another M:1 for the key to go from the key id to the key names. Instead, I just have a json column with all the key values in it, right there in the customer record. Its convenient!
Note that for databases that support array columns, this is much nicer to represent with an array. Postgres supports them, for example. (SQLite doesn't)
Let's say you have a product with multiple labels: usually you'll get one line per (product, label) tuple so you'll have to do some job application side if you what to get one [product, labels] object per product.
With the json aggregate function you can get a (product, json array of labels) tuple per product and just have to do some json_decode in your application code.
What's the payoff of doing [product, labels], particularly if that means a json array of labels? I briefly investigated doing that for my application but I found that it would increase disk space (not really a big problem) but it would also make efficient querying a big chore (e.g. labels->products queries.)
I can see this being a good tradeoff if querying by labels is extremely rare and instead you only ever want to query product->labels. But that's still pretty damn fast with the (product, label) tuple schema.
When querying you do something like
select p.name, json_agg(l.value) as labels
from product p, label lhttps://github.com/coast-framework/lighthouse/blob/master/RE...
There is also the possibility of magic 'compression' by having the server automagically extract common schema elements of the stored data to probably cut the storage size in half again.