JSON and Virtual Columns in SQLite

Re:JSON and Virtual Columns in SQLite by nine_k on Monday May 16, 2022 @11:09AM (#31398577)

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.

[ Parent | on HN ]

Re:JSON and Virtual Columns in SQLite by alphalima on Monday May 16, 2022 @12:01PM (#31399389)

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.

[ Parent | on HN ]