The JSON1 extension can be used in Android with the sqlite-android library
JSON with Sqlite
41–50 of 74 posts
Re: JSON with Sqlite
#42I've been using the JSON1 extension for some time now (in production projects) and it's truly remarkable. I usually just dump the JSON-response data from an API to a "raw_data" table (typically one "updated_at" column and a second "json_data" one). At that point you can somehow normalize your schema, but only if you really have to! That is because you can get away with a NoSQL-like denormalized schema performance wis…
> (Yes, my dear aspring data scientist, do not load everything in a huge DataFrame, go learn yourself some SQL :-) )
This hits a little bit too close to home for me. I am quite proficient at writing performant SQL queries and recently started using Pandas. I find the data frame abstraction better for certain data manipulation tasks. Assuming there is enough RAM available is it still better to offload everything to the database engine?
Re: JSON with Sqlite
#43I've been using the JSON1 extension for some time now (in production projects) and it's truly remarkable. I usually just dump the JSON-response data from an API to a "raw_data" table (typically one "updated_at" column and a second "json_data" one). At that point you can somehow normalize your schema, but only if you really have to! That is because you can get away with a NoSQL-like denormalized schema performance wis…
> It is true that SQLite does not support concurrent-writes, but (and that's a big BUT) if you carefully open connections only when you need them and use prepared statements, I can't see how you could run into problems with modern SSD hardware (unless you're Google-scale of coure).
WAL mode is your friend. (Various SQLite drivers, including the Python one, are however somewhat buggy in their transaction handling and need workarounds; essentially they delay the BEGIN of a transaction until you issue DML statements which obviously breaks snapshot isolation entirely).
WAL mode allows one writer at a time without impeding readers.
(See https://docs.sqlalchemy.org/en/latest/dialects/sqlite.html#p... for the pysqlite workaround)
Re: JSON with Sqlite
#44I've been using the JSON1 extension for some time now (in production projects) and it's truly remarkable. I usually just dump the JSON-response data from an API to a "raw_data" table (typically one "updated_at" column and a second "json_data" one). At that point you can somehow normalize your schema, but only if you really have to! That is because you can get away with a NoSQL-like denormalized schema performance wis…
> (Yes, my dear aspring data scientist, do not load everything in a huge DataFrame, go learn yourself some SQL :-) ) This hits a little bit too close to home for me. I am quite proficient at writing performant SQL queries and recently started using Pandas. I find the data frame abstraction better for certain data manipulation tasks. Assuming there is enough RAM available is it still better to offload everything to th…
As usual: it depends...
If you're doing prototyping and are working in a Jupyter notebook, sure, go ahead and work on the Pandas-level.
Once however you're done with prototyping and have settled to a "final_df" (I bet you have something like that in your last notebook cells), maybe you should think transforming some of the "columns" to sql queries (which are VCS-able, sometimes are faster, and most importantly other people can use them too. And instead of 10 people loading 10 different DFs, you can have 10 people querying the same table/view.
Re: JSON with Sqlite
#45I've been using the JSON1 extension for some time now (in production projects) and it's truly remarkable. I usually just dump the JSON-response data from an API to a "raw_data" table (typically one "updated_at" column and a second "json_data" one). At that point you can somehow normalize your schema, but only if you really have to! That is because you can get away with a NoSQL-like denormalized schema performance wis…
> It is true that SQLite does not support concurrent-writes, but (and that's a big BUT) if you carefully open connections only when you need them and use prepared statements, I can't see how you could run into problems with modern SSD hardware (unless you're Google-scale of coure). WAL mode is your friend. (Various SQLite drivers, including the Python one, are however somewhat buggy in their transaction handling and…
I see your point, but I'm very hesitant to change any configuration of SQLite. It kinda feels like one step too close to doing devops - which one wants to avoid by using SQLite I guess.
Having said that, I do play around with PRAGMA statements when it's really needed, but usually tweaking the code usually works fine - even increasing the timeout is probably enough :D
Re: JSON with Sqlite
#46I've been using the JSON1 extension for some time now (in production projects) and it's truly remarkable. I usually just dump the JSON-response data from an API to a "raw_data" table (typically one "updated_at" column and a second "json_data" one). At that point you can somehow normalize your schema, but only if you really have to! That is because you can get away with a NoSQL-like denormalized schema performance wis…
> (Yes, my dear aspring data scientist, do not load everything in a huge DataFrame, go learn yourself some SQL :-) ) This hits a little bit too close to home for me. I am quite proficient at writing performant SQL queries and recently started using Pandas. I find the data frame abstraction better for certain data manipulation tasks. Assuming there is enough RAM available is it still better to offload everything to th…
Dask has an out-of-memory dataframe implementation. Works great! I think it might support sql queries for that matter.
Re: JSON with Sqlite
#47Why not converting JSON from/to BLOB?
From the article: The json1 extension does not (currently) support a binary encoding of JSON. Experiments have been unable to find a binary encoding that is significantly smaller or faster than a plain text encoding. (The present implementation parses JSON text at over 300 MB/s.) All json1 functions currently throw an error if any of their arguments are BLOBs because BLOBs are reserved for a future enhancement in whi…
Seems like it has been updated to say 1GB/s now.
Re: JSON with Sqlite
#48What 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'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.
Re: JSON with Sqlite
#49Earlier quoted context omitted.
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.…
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 sch…
Completely agree, I do this all the time. This is a useful pattern.
Re: JSON with Sqlite
#50This should be build in standard SQLite.