alt Hacker NewsThe JSON functions are the sleeper hit for me. I've used them extensively in ETL scripts where input data is semi-structured - being able to do json_extract and json_each directly in SQL instead of writing a Python preprocessing step saved a surprising amount of complexity. Strict tables are also worth calling out more. The lack of type enforcement was always the thing that made me reach for PostgreSQL instead, and strict mode closes that gap nicely for smaller projects.
Replies
Everybody has a JSON extension, and they're all slightly different.
I just got hit badly by Dreamhost, which is still running MySQL 8.0. That version has a "JSON extension" which is almost totally useless. I designed something before discovering how different MySQL 8.4 and MySQL 8.0 are.
That does seem cool. So there are sufficient functions to iterate through collections that might be stored in a single row of a JSON column?
One handy bit of SQLite's JSON features which isn't called out in this article is the short syntax, e.g.
This works a little more consistently than JSON_EXTRACT - the -> operator always returns a JSON string; the ->> operator (used here) always returns a SQL scalar (text, number, or null). More details: https://sqlite.org/json1.html#jptr