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
Topic
MariaDB
The MySQL fork that grew its own personality. What carries over, what quietly changed, and the features only it has.
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
JSON
MySQL JSON Versus MariaDB JSON and the Differences
MariaDB's JSON is validated text and MySQL's is a binary type. What differs in storage, operators, indexes, errors and speed, measured on 200,000 rows.
8 min read
JSON
Building JSON Output With JSON_OBJECT and JSON_ARRAYAGG
Build nested JSON from MySQL tables with JSON_OBJECT and JSON_ARRAYAGG, and handle booleans, ordering, empty results and join fan-out.
7 min read
JSON
Searching Values Inside a MySQL JSON Array Efficiently
MEMBER OF, JSON_CONTAINS, JSON_OVERLAPS and JSON_SEARCH compared on real tables, with a multi-valued index, and the matching rules for numbers and strings.
8 min read
JSON
Using JSON_TABLE to Turn MySQL JSON Into Rows and Columns
JSON_TABLE in MySQL 8 turns JSON arrays into typed rows. Syntax, nested paths, default and error clauses, the silent NULLs, and timings on 250,000 items.
8 min read
JSON
Indexing a MySQL JSON Field With a Generated Column
MySQL cannot index a JSON column directly. Add a generated column, index it, and learn the collation, NULL and cast traps that change query results.
8 min read
JSON
Updating Parts of a MySQL JSON Document in Place Safely
Use JSON_SET, JSON_REPLACE, JSON_REMOVE and array functions to change one part of a MySQL JSON column, with partial updates, binlog size and the counter trap.
8 min read
JSON
JSON_EXTRACT and the Arrow Operators in MySQL Explained
How MySQL JSON paths work, what JSON_EXTRACT returns for each value type, why -> and ->> are not the same, and the path rules that cause errors.
7 min read
JSON
Creating and Querying a MySQL JSON Column From Scratch
Create a MySQL JSON column with a default, insert documents several ways, then filter, sort and group on keys, with the NULL and ordering traps checked.
8 min read
JSONGuide
Using the JSON Column Type in MySQL Without Regret
A working guide to MySQL 8.4 JSON columns: paths, reading, updating, searching, generated-column and multi-valued indexes, and MariaDB differences.
19 min read
CSV
Fixing Data Truncated Warnings in a MySQL CSV Load
LOAD DATA LOCAL clips long strings and out of range numbers to the nearest value and only warns. See each warning, the checks that find them, and the fix.
9 min read
Get new articles by email
We send one email when a new article is published. No spam.