[deleted]
JSON with Sqlite
11–20 of 74 posts
Re: JSON with Sqlite
#12What are some useful use-cases for this? 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?
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...
Re: JSON with Sqlite
#13What are some useful use-cases for this? 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?
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.
Re: JSON with Sqlite
#14What are some useful use-cases for this? 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?
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.
Re: JSON with Sqlite
#15What are some useful use-cases for this? 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?
I especially like the aggregator functions, it allows to select an additional dimension of data.
Re: JSON with Sqlite
#16From a glance, this looks like its compatible with MySQL's JSON functions.
It isn't compatible, for example, with MariaDB's dynamic columns.
I'm not very familiar with the PostgreSQL json functionality, but I think that's subtly different again.
When I use local dbs for testing my json code I've used derby and defined the json_extract() functions etc myself just to test. With Sqlite having compatible functions, people wanting to test mysql stuff locally will be able to just point their code at an Sqlite DB instead. Great!
The bit that seems to be missing is the shorthand for selecting column values; in MySQL, instead of doing SELECT JSON_EXTRACT(col, "$.this.is.ugly[12]"), ... you can just do SELECT col->"$.this.is.ugly[12]", ...
Now what I want to be able to write is SELECT col.this.is.nicer[12], ....
I think there is some 'standard' somewhere that MySQL - and now Sqlite - is implementing? The functions and the 'path' syntax are standardized (although with only MySQL and now Sqlite supporting them its not perhaps a big deal). I just can't find any reference to that standard in the MySQL docs, nor this Sqlite doc.
Personally, I dislike the path syntax though! Every time I see an sql snippet with string paths full of dollar signs it offends my retinas.
Re: JSON with Sqlite
#17What are some useful use-cases for this? 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?
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!
Re: JSON with Sqlite
#18Is loading extensions at runtime new? Coulda sworn I used to have to recompile for this
It seems valuable enough to have in the standard build. That and the CSV extension as well.
Especially for things like Python's embedded SQLite. Packaging and loading an extension module cross-platform with an otherwise pure Python script seems difficult.
Re: JSON with Sqlite
#19From a glance, this looks like its compatible with MySQL's JSON functions. It isn't compatible, for example, with MariaDB's dynamic columns. I'm not very familiar with the PostgreSQL json functionality, but I think that's subtly different again. When I use local dbs for testing my json code I've used derby and defined the json_extract() functions etc myself just to test. With Sqlite having compatible functions, peopl…
According to [1], ISO/IEC 9075:2016 specifies JSON functionality in ISO SQL, though I don't have access to it/didn't buy the standard text. I also don't know whether Postgres etc. is designed to follow the standard, as Postgres' implementation predates it, but would expect sqlite to.
[1]: https://modern-sql.com/blog/2017-06/whats-new-in-sql-2016
Re: JSON with Sqlite
#20From a glance, this looks like its compatible with MySQL's JSON functions. It isn't compatible, for example, with MariaDB's dynamic columns. I'm not very familiar with the PostgreSQL json functionality, but I think that's subtly different again. When I use local dbs for testing my json code I've used derby and defined the json_extract() functions etc myself just to test. With Sqlite having compatible functions, peopl…
The json sql syntax was probably invented by the same jagoffs who came up with C++ STL map api. I can’t imagine how much bikeshedding it took to come up with this crap.