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

21–30 of 74 posts

Re: JSON with Sqlite

#21

Post by matharmin »

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?

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.

Re: JSON with Sqlite

#23

Post by masklinn »

tannhaeuser wrote: post #19
willvarfar wrote: post #16

From 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

Postgres 11 doesn't follow the standard at all (as per your link). A third party implemented the standard [0] which was submitted on pghackers[1] very late in the Postgres 11 cycle[2] (at 75k gzipped the patch is pretty humongous). A quick googling doesn't show any information pertaining it being integrated (in part or full) into pg12 so IDK.

edit: apparently https://commitfest.postgresql.org/17/1471/ has been "Waiting on Author" and bumped from CF to CF since early 2018, and https://commitfest.postgresql.org/17/1472/ and https://commitfest.postgresql.org/17/1473/ pretty much the same with no "waiting on author" but I don't really know how CF works and I see no comment or requests or reviews so…

[0] https://obartunov.livejournal.com/200076.html

[1] https://www.postgresql.org/message-id/CAF4Au4w2x-5LTnN_bxky-...

[2] https://www.postgresql.org/message-id/00531c7e-f501-b852-9b6...

Re: JSON with Sqlite

#24

Post by robocat »

IloveHN84 wrote: post #22

Why 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 which BLOBs will store the binary encoding for JSON.

Re: JSON with Sqlite

#25

Post by arkh »

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?

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.

Re: JSON with Sqlite

#26

Post by misev »

tannhaeuser wrote: post #19
willvarfar wrote: post #16

From 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

There's a technical report ISO/IEC TR 19075-6:2017 corresponding to the standard, freely available from [1]. It's a much more accessible version of the standard if you're not looking to implement it.

1. https://standards.iso.org/ittf/PubliclyAvailableStandards/

Re: JSON with Sqlite

#28

Post by remus »

kokokokoko wrote: post #14
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?

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 schema stays very manageable. Also gives you the flexibility to add new fields to the schema by pulling the data out of the json.

Re: JSON with Sqlite

#29

Post by londons_explore »

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?

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.

Re: JSON with Sqlite

#30

Post by Sean1708 »

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

Post reply on HN