5 ms·

But if you are going to be querying on a particular, known-ahead-of-time value in the JSON, why did you use JSON to begin with? It's not like the data came thro

But if you are going to be querying on a particular, known-ahead-of-time value in the JSON, why did you use JSON to begin with? It's not like the data came through some other system, since this is SQLite. Couldn't you have just extracted the pieces you cared about ahead of time?

ec109685 4y ago
If you have between 11 and 100 fields and only need to lookup based on 2, it seems like it’s a win?
deleted 4y ago
[deleted]
nine_k 4y ago
What if you know a few fields, like some "id" or "name" at the top level, but the rest of the JSON is highly nested, tree-like? Instead of mapping it onto relational tables and keeping indexes, and then running a complex query, you can just put them all into a ready-made, well, blob. Note that it only makes sense to have a relational column if you need to (1) use it as a query condition or (2) update it independently of others. In many tables, many columns are neither, and can be safely stored as JSON.
- alphalima 4y ago
  Exactly the point I was going to make. It's also relatively trivial to generate view statents from the schema of the object if required for analytics (not fast though). Much better than a crazy ORM rat nest in my experience.
linsomniac 4y ago
In my case, I have a bunch of data in the JSON, but found users need to filter requests based on just one field of the data. In specific, I'm loading Postfix logs into the database, I parse the logs into JSON and then store that JSON in a column. The format of that JSON comes in ~4-5 flavors, depending on the exact log event, but most of them include a "to" field (the envelope recipient), that the API users need to filter on. I ended up adding a "to" column and mirroring the JSON "to" field into that, but this would have worked as well. The API users still have access to that additional rich data, and the whole thing overall is simpler because I don't have it exploded out into a bunch of fields that have many of them NULLs for various log types.
jitl 4y ago
Adding bidirectional lenses to “normalize” and “denormalize” data before it enters and after it exits a SQL database means you need to maintain those lenses separately from the SQL database schema. You would have three things to keep track of - the upstream data source (the thing supplying the JSON, might not be under your control), the lenses in your SQL client source code, and the SQL database schema. Using virtual columns like this encodes the “normalizing” lens into the SQL schema in a purely-functional way. This seems like a pure win to me for clients that need to cache data but never update it. The client code can be a “dumb pipe” on the write side and just dump whatever received data into the SQL database. In essence, you are getting CQRS (command/query representation separation) for “free”, built into the storage system. Why spend the engineering cycles in the SQL client? For a real world example - at Notion we have a SQLite database running in our client apps. Most of the rows come from the back-end API and serve as a local cache. There are some object properties the clients need to query, and other object properties the clients don’t care to query, but are needed to render the data in the UI, so it still needs to be stored locally. Over time, those needs change — as do the shape of the upstream API data source. So, we put un-queried object properties into catch-all JSON columns. When we introduce a new query pattern to the client, we can run a number of different migration strategies to extract existing data from the JSON column and put it into a newly created column, or use a virtual column/index. Having the catch-all JSON column also means we can add an object property in our backend for web clients, and then later when we roll the feature out to native apps the data is already there - it just needs a migration to make it efficient to query.
- nbevans 4y ago
  +1 for lenses. A FP concept.
- munk-a 4y ago
  In Postgres there is a family of INSTEAD OF triggers that allow you to do all this lensing within the database so that the application can have multiple views of the data that all resolve to a single normalized structure internally but present in a variety of ways. To my googling knowledge SQLite also supports INSTEAD OF triggers - is there a benefit to using the predefined JSON columns instead of writing the data into an actual normalized structure and then zipping it up at read time or during the regeneration of a materialized view?
41b696ef1113 4y ago
One idea I have been considering - use SQLite as my JSON parser in Lua. Lua has solid SQLite bindings, but there are several JSON libraries, with varying performance characteristics. If I create a :memory: database, I could shunt JSON in/out of SQLite and use their performant implementations to extract fields from APIs. Admittedly, not a standard use case, but something I intend to try in the near future.
- remram 4y ago
  It seems more like a hack than a good idea. If you have to run with only the Lua standard library, and that's the only (performant) JSON parser in the library, do what you have to do; but otherwise it seems like a bad idea.
- banana_giraffe 4y ago
  Be careful with that. Since you'll end up coercing JSON types into SQLite types doing this, you might run into some odd edge cases. One big one is around big numbers: sqlite> select json_extract('[12345678901234567899]','$[0]') % 10; 7.0 It may never be a problem for you, but it's been a pain point for me dealing with different JSON libraries in the past when people stuff giant numbers into something because their end has no problem with it.
jmull 4y ago
This is just a way to extract the pieces you care about.
deleted 4y ago
[deleted]
Groxx 4y ago
>Couldn't you have just extracted the pieces you cared about ahead of time? Sure, but if you discover this desire later, you now have historical data to change and application behavior to change and database structure to change. And repeat all that when you discover the next piece of more-useful-than-anticipated data. Or you can just `alter table` and be done with it.

ec109685 4y ago

If you have between 11 and 100 fields and only need to lookup based on 2, it seems like it’s a win?

deleted 4y ago

[deleted]

nine_k 4y ago

What if you know a few fields, like some "id" or "name" at the top level, but the rest of the JSON is highly nested, tree-like? Instead of mapping it onto relational tables and keeping indexes, and then running a complex query, you can just put them all into a ready-made, well, blob. Note that it only makes sense to have a relational column if you need to (1) use it as a query condition or (2) update it independently of others. In many tables, many columns are neither, and can be safely stored as JSON.

alphalima 4y ago
Exactly the point I was going to make. It's also relatively trivial to generate view statents from the schema of the object if required for analytics (not fast though). Much better than a crazy ORM rat nest in my experience.

linsomniac 4y ago

In my case, I have a bunch of data in the JSON, but found users need to filter requests based on just one field of the data. In specific, I'm loading Postfix logs into the database, I parse the logs into JSON and then store that JSON in a column. The format of that JSON comes in ~4-5 flavors, depending on the exact log event, but most of them include a "to" field (the envelope recipient), that the API users need to filter on. I ended up adding a "to" column and mirroring the JSON "to" field into that, but this would have worked as well. The API users still have access to that additional rich data, and the whole thing overall is simpler because I don't have it exploded out into a bunch of fields that have many of them NULLs for various log types.

jitl 4y ago

Adding bidirectional lenses to “normalize” and “denormalize” data before it enters and after it exits a SQL database means you need to maintain those lenses separately from the SQL database schema. You would have three things to keep track of - the upstream data source (the thing supplying the JSON, might not be under your control), the lenses in your SQL client source code, and the SQL database schema. Using virtual columns like this encodes the “normalizing” lens into the SQL schema in a purely-functional way. This seems like a pure win to me for clients that need to cache data but never update it. The client code can be a “dumb pipe” on the write side and just dump whatever received data into the SQL database. In essence, you are getting CQRS (command/query representation separation) for “free”, built into the storage system. Why spend the engineering cycles in the SQL client? For a real world example - at Notion we have a SQLite database running in our client apps. Most of the rows come from the back-end API and serve as a local cache. There are some object properties the clients need to query, and other object properties the clients don’t care to query, but are needed to render the data in the UI, so it still needs to be stored locally. Over time, those needs change — as do the shape of the upstream API data source. So, we put un-queried object properties into catch-all JSON columns. When we introduce a new query pattern to the client, we can run a number of different migration strategies to extract existing data from the JSON column and put it into a newly created column, or use a virtual column/index. Having the catch-all JSON column also means we can add an object property in our backend for web clients, and then later when we roll the feature out to native apps the data is already there - it just needs a migration to make it efficient to query.

nbevans 4y ago
+1 for lenses. A FP concept.
munk-a 4y ago
In Postgres there is a family of INSTEAD OF triggers that allow you to do all this lensing within the database so that the application can have multiple views of the data that all resolve to a single normalized structure internally but present in a variety of ways. To my googling knowledge SQLite also supports INSTEAD OF triggers - is there a benefit to using the predefined JSON columns instead of writing the data into an actual normalized structure and then zipping it up at read time or during the regeneration of a materialized view?

41b696ef1113 4y ago

One idea I have been considering - use SQLite as my JSON parser in Lua. Lua has solid SQLite bindings, but there are several JSON libraries, with varying performance characteristics. If I create a :memory: database, I could shunt JSON in/out of SQLite and use their performant implementations to extract fields from APIs. Admittedly, not a standard use case, but something I intend to try in the near future.

remram 4y ago
It seems more like a hack than a good idea. If you have to run with only the Lua standard library, and that's the only (performant) JSON parser in the library, do what you have to do; but otherwise it seems like a bad idea.
banana_giraffe 4y ago
Be careful with that. Since you'll end up coercing JSON types into SQLite types doing this, you might run into some odd edge cases. One big one is around big numbers: sqlite> select json_extract('[12345678901234567899]','$[0]') % 10; 7.0 It may never be a problem for you, but it's been a pain point for me dealing with different JSON libraries in the past when people stuff giant numbers into something because their end has no problem with it.

jmull 4y ago

This is just a way to extract the pieces you care about.

deleted 4y ago

[deleted]

Groxx 4y ago

>Couldn't you have just extracted the pieces you cared about ahead of time? Sure, but if you discover this desire later, you now have historical data to change and application behavior to change and database structure to change. And repeat all that when you discover the next piece of more-useful-than-anticipated data. Or you can just `alter table` and be done with it.