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
Topic
JSON
Nested, loosely typed and everywhere. Query it, flatten it, validate it and store it without losing what the nesting meant.
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
JSON
When JSONB Is the Wrong Choice and Typed Columns Win
Signs that a PostgreSQL JSONB column should be real columns, what the workarounds cost, and a step-by-step migration that keeps the data and adds constraints.
8 min read
JSON
Aggregating Rows Into JSON With jsonb_agg in PostgreSQL
Build nested JSON documents from relational tables with jsonb_agg, jsonb_build_object and to_jsonb, avoid the join fan-out bug, and see which form is fastest.
8 min read
JSON
Using JSON_TABLE in PostgreSQL 17 to Turn JSON Into Rows
JSON_TABLE turns nested JSON into typed rows in one clause. Paths, nested arrays, ordinality, EXISTS columns and ON ERROR rules, run on PostgreSQL 18.
8 min read
Get new articles by email
We send one email when a new article is published. No spam.