JSON
Validating Text as JSON With json_valid in SQLite Tables
What json_valid accepts in SQLite with each flag, why a CHECK can pass a NULL, how to find bad rows, add a check to an existing table and check JSONB blobs.
7 min read
Topic
JSON
Nested, loosely typed and everywhere. Query it, flatten it, validate it and store it without losing what the nesting meant.
JSON
Storing Settings as JSON in SQLite and Updating One Key
A SQLite settings table with a JSON column: a CHECK that it is an object, json_set for one key, json_patch upserts, defaults on read, counters and a trigger.
8 min read
JSON
Using the JSONB Format Added in SQLite 3.45 Safely
SQLite 3.45 added JSONB, a binary JSON blob. Convert a column, measure size and speed, learn the function pairs and the pitfalls with older drivers.
8 min read
JSON
Indexing JSON Fields in SQLite With Expression Indexes
Index a field inside a SQLite JSON document with an expression index or a generated column, and see which query spellings use it and what it costs.
7 min read
JSON
Building JSON Results With json_group_array in SQLite
Build nested JSON from SQLite tables with json_group_array, json_group_object and json_object, and avoid the lost-subtype bug that turns arrays into strings.
7 min read
JSON
Expanding JSON Arrays With json_each and json_tree
Turn SQLite JSON arrays and objects into rows with json_each and walk whole documents with json_tree, with every column explained and the edge cases checked.
7 min read
JSON
The SQLite Arrow Operators for JSON Explained With Examples
SQLite's -> and ->> operators read JSON like PostgreSQL and MySQL, with their own rules for bare keys, negative indexes, precedence and the types they return.
7 min read
JSON
Querying JSON Text With json_extract in SQLite Step by Step
What json_extract returns for each JSON type in SQLite, how paths and multiple paths behave, the number and text traps, and what repeated calls cost.
7 min read
JSONGuide
JSON Functions in SQLite and What You Can Do With Them
A tested guide to SQLite's JSON functions: reading, arrows, editing, json_each, aggregates, JSONB, expression indexes and the type traps, on 200,000 rows.
18 min read
YAMLGuide
Converting YAML to and From JSON CSV XML and Excel
What survives when YAML becomes JSON, CSV, XML or Excel and back, with a tested loss matrix, the type traps in each direction and a conversion checklist.
19 min read
JSON
Fixing Invalid JSON Text Errors in MySQL Inserts and Imports
Decode MySQL error 3140 Invalid JSON text and its relatives, including the backslash trap in SQL string literals that silently changes your JSON.
9 min read
JSON
Validating JSON Before It Gets Into a MySQL Column
Enforce structure on a MySQL JSON column with JSON_TYPE checks and JSON_SCHEMA_VALID in CHECK constraints, with the draft 4 limits and the insert cost.
8 min read
Get new articles by email
We send one email when a new article is published. No spam.