Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

JSON with Sqlite

sqlite.org

Post reply on HN
Print view View original

31–40 of 74 posts

Re: JSON with Sqlite

#31

Post by tarruda »

Sean1708 wrote: post #30

> Backwards compatibility constraints mean that SQLite is only able to store values that are NULL, integers, floating-point numbers, text, and BLOBs. It is not possible to add a sixth "JSON" type. I'd be interested to know what these constraints are? Does SQLite guarantee that files created with newer SQLite versions are still compatible with older SQLite versions?

From https://www.sqlite.org/onefile.html :

> The SQLite database file format is also stable. All releases of SQLite version 3 can read and write database files created by the very first SQLite 3 release (version 3.0.0) going back to 2004-06-18. This is "backwards compatibility". The developers promise to maintain backwards compatibility of the database file format for all future releases of SQLite 3. "Forwards compatibility" means that older releases of SQLite can also read and write databases created by newer releases. SQLite is usually, but not completely forwards compatible.

Re: JSON with Sqlite

#32

Post by blattimwind »

WaltPurvis wrote: post #3
eli wrote: post #2

Is loading extensions at runtime new? Coulda sworn I used to have to recompile for this

Pretty sure it's been around for at least 10 years or so. (But it's not a widely used feature, I think.)

JSON1 is compiled into every Linux distro's SQLite I tested and is also in Mac's SQLite, as well as the Windows binaries provided by sqlite.org. The only exception I found was the SQLite bundled with Python for Windows, which doesn't seem to have any extensions at all.

Performance-wise this is fast enough that you can do faily complex un-indexed queries (so full table scans) against tables with a few ten-thousand to hundred-thousand rows and have them complete in a couple tens msecs.

Re: JSON with Sqlite

#33

Post by tsmarsh »

toomim wrote: post #4

What 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?

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.

Re: JSON with Sqlite

#34

Post by kierenj »

tarruda wrote: post #31
Sean1708 wrote: post #30

> Backwards compatibility constraints mean that SQLite is only able to store values that are NULL, integers, floating-point numbers, text, and BLOBs. It is not possible to add a sixth "JSON" type. I'd be interested to know what these constraints are? Does SQLite guarantee that files created with newer SQLite versions are still compatible with older SQLite versions?

From https://www.sqlite.org/onefile.html : > The SQLite database file format is also stable. All releases of SQLite version 3 can read and write database files created by the very first SQLite 3 release (version 3.0.0) going back to 2004-06-18. This is "backwards compatibility". The developers promise to maintain backwards compatibility of the database file format for all future releases of SQLite 3. "Forwards compat…

And yet, "The json1 extension uses the sqlite3_value_subtype() and sqlite3_result_subtype() interfaces that were introduced with SQLite version 3.9.0 (2015-10-14) The json1 extension will not work in earlier versions of SQLite."

Re: JSON with Sqlite

#35

Post by matharmin »

kierenj wrote: post #34
tarruda wrote: post #31

Earlier quoted context omitted.

From https://www.sqlite.org/onefile.html : > The SQLite database file format is also stable. All releases of SQLite version 3 can read and write database files created by the very first SQLite 3 release (version 3.0.0) going back to 2004-06-18. This is "backwards compatibility". The developers promise to maintain backwards compatibility of the database file format for all future releases of SQLite 3. "Forwards compat…

And yet, "The json1 extension uses the sqlite3_value_subtype() and sqlite3_result_subtype() interfaces that were introduced with SQLite version 3.9.0 (2015-10-14) The json1 extension will not work in earlier versions of SQLite."

This just means you can't use the json1 extension with an old version of the library. But anything you write using it, will still be readable with an older version.

Of course, if you do something like creating an index using a json1 expression, that will cause issues if you try to use the database with an older version.

Re: JSON with Sqlite

#36

Post by the_duke »

willvarfar wrote: post #17
toomim wrote: post #4

What 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 relationsh…

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)

Re: JSON with Sqlite

#37

Post by fforflo »

I'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 wise, by carefully defining index on expressions. You can somehow normalize it with views (SQLite doesn't support materialized views).

And of course it's almost always faster to query data where it exists (via SQL) instead of fetching it from disk and querying it say Python. You pay too much IO cost. (Yes, my dear aspring data scientist, do not load everything in a huge DataFrame, go learn yourself some SQL :-) )

The json dump is not stored in binary, but in text format, but honestly I haven't seen this to be a problem, plus you can easily run queries at the CLI and pipe the output to jq, sed etc.

If your application is data warehouse-like and read-heavy (for example an internal reporting dashboard) I can't see any reason why you should pay the cost of setting up a Postgres or MongoDB instance (although I do love both.)

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

Re: JSON with Sqlite

#38

Post by rgovostes »

I recently wrote an SQLite extension that lets me query stored Protobuf messages. I was inspired by the JSON extension. Adding virtual tables to SQLite with an extension is tricky but kind of magical when you finally have the full query language to play with.

https://github.com/rgov/sqlite_protobuf

Re: JSON with Sqlite

#39

Post by punnerud »

To check if you already have it installed run "pragma compile_options;" and look for ENABLE_JSON1. On my Mac I had it installed both on sqlite3 in terminal/bash and Python :-)

  import sqlite3
  con = sqlite3.connect(':memory:')
  con.enable_load_extension(True)
  a = con.execute("pragma compile_options;")
  for i in a: print(i); #check for ENABLE_JSON1
The newest releases of sqlite3 have it included. If not you can build it this way: https://burrows.svbtle.com/build-sqlite-json1-extension-as-s... Note that you will only get this error on load if you have it: 'Error: error during initialization:'

Re: JSON with Sqlite

#40

Post by micahlopez »

the_duke wrote: post #36
willvarfar wrote: post #17

Earlier quoted context omitted.

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 relationsh…

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?
Post reply on HN