What I have found with any relational database that has this kind of JSON storage option(MySQL, PostgresSQL) is that you can take some kind of data that arrives via 3rd party as JSON and just dump it straight into the db as JSON as opposed to having to create a schema for it.

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.

One pattern that I've used when parsing large json responses is to have a table schema like

    frequently|used|data|in|these|cols|full_json_response

So you pull out the fields you need frequently but have the full json so you can dig in to details the response on an adhoc basis. Not very efficient as you can end up storing a big blob of json, but you're not throwing away any data when you parse the response and your schema stays very manageable. Also gives you the flexibility to add new fields to the schema by pulling the data out of the json.

I use this pattern a lot, too. Just make sure not to `SELECT *`, which could be much slower and waste a lot of RAM in some cases (ORMs using e.g. active record pattern are often susceptible to this when used naively).

I've also done similar where there are differing systems where the extra data is different, but common bits are, well common. For example supporting direct CC, Paypal and Amazon pay. Putting the common parts of the transaction as separate columns, with the full details in a JSON column.

Completely agree, I do this all the time. This is a useful pattern.

It's nice for being stage 1 of an import: pull the JSON in and use queries to populate other columns/tables with the extracted portions you're working with since you have the full power of a SQL database for many common cleanup tasks.

I work quite a lot with SQLite since is the foundation of https://redisql.com/

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.

RediSQL looks interesting and I like the pricing structure. I'm working on marketing my own set of products so I know it's difficult to keep it up. Good luck with this venture and I hope it works out for you and your team.

Great!

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.

Since you can make effectively arbitrary index expressions[1], I'd expect the answer to be "yes". The article doesn't explicitly state if they're deterministic or not, but it seems like some of them could / should be.

[1]: https://www.sqlite.org/expridx.html

I gave it a shot and it appears to work as expected.

  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 a

I'm using this in a mobile application (Android and iOS). The data model can change server-side at any time, so the data is stored as JSON to avoid complicated client-side migrations.

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

Its really common to store json documents inside rows in normal relational databases.

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!

I've done this too.

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)

Is there a link for the support documentation?

Column arrays have been in postgreSQL since 9.1 at least.

https://www.postgresql.org/docs/9.1/arrays.html

One thing I like is when getting data from multiple tables.

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.

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

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.

You don't change your schema to add some json field.

When querying you do something like

  select p.name, json_agg(l.value) as labels
  from product p, label l

I use this for “pulling” data out of the db hierarchically, similar to something like GraphQL:

https://github.com/coast-framework/lighthouse/blob/master/RE...

There is the possibility of storing a binary-encoded json object, which uses far less space than regular json, and since the server is doing the encoding you aren't going to need custom client side libraries to decode it.

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.

sqlite doesn't '(currently)' do either of these though.

Pivots and trees. Long skinny tables to wide sparse tables used to be something we did a lot, now we return a JSON Tree... we were always parsing the sparse table into a tree anyway.

I'm using this for change tracking: https://blog.budgetwithbuckets.com/2018/08/27/sqlite-changel...

I especially like the aggregator functions, it allows to select an additional dimension of data.