MySQL 8.0 JSON Functions: Overview, Syntax, and Practical Examples

After extensive testing, the author migrated the whole project to MySQL 8.0, which adds and optimizes many JSON‑related APIs.

Key points of MySQL 8.0 JSON support

JSON columns cannot be NULL ; their definition is similar to LONGBLOB or LONGTEXT and their maximum size is limited by max_allowed_packet .

The function JSON_STORAGE_SIZE(col) returns the storage size of a JSON field.

MySQL 8.0 also supports GeoJSON operations for spatial data.

JSON columns can be indexed using generated columns and the new INDEX function.

In‑place updates are possible with functions such as JSON_SET() , JSON_REPLACE() and JSON_REMOVE() , offering better performance under certain constraints.

Basic JSON utility functions

// Create a JSON array
SELECT JSON_ARRAY(1, "abc", NULL, TRUE, CURTIME());
// Result: [1, "abc", null, true, "11:30:24.000000"]

// Create a JSON object
SELECT JSON_OBJECT('id', 87, 'name', 'carrot');
// Result: {"id": 87, "name": "carrot"}

// Nested arrays and objects example
[99, {"id": "HK500", "cost": 75.99}, ["hot", "cold"]] {"k1": "value", "k2": [10, 20]}

// Date/Time literals in JSON
["12:18:29.000000", "2015-07-29", "2015-07-29 12:18:29.000000"]

// Escape a JSON value as a string
SELECT JSON_QUOTE(' "null" ');
// Result: "\"null\""

// Pretty‑print JSON
JSON_PRETTY(...);

MySQL also provides merging functions JSON_MERGE_PRESERVE() and JSON_MERGE_PATCH() , though they are rarely needed in typical business scenarios.

Extraction operators

// Using the -> operator (removes surrounding quotes)
SELECT col->"$.mascot" FROM qtest;  -- Result: "Our mascot is a dolphin named \"Sakila\"."

// Using the ->> operator (returns unquoted value)
SELECT sentence->>"$.mascot" FROM facts;  -- Result: Our mascot is a dolphin named "Sakila".

The content inside the double quotes after -> or ->> is a JSON Path expression, which follows the ECMAScript standard and is familiar to front‑end developers.

JSON Path expression examples

[3, {"a": [5, 6], "b": 10}, [99, 100]]
$[0] = 3;
$[1] = {"a": [5, 6], "b": 10};
$[2] = [99, 100];
$[1].a[1] = 6;
$[1].b = 10;
$[2][0] = 99;
$[1 to 2] = [{"a": [5, 6], "b": 10}, [99, 100]];
$[last-2 to last-1] = [3, {"a": [5, 6], "b": 10}];

JSON_TABLE function

SELECT * FROM JSON_TABLE(
  '[{"a":"3"},{"a":2},{"b":1},{"a":0},{"a":[1,2]}]',
  "$[*]" COLUMNS(
    rowid FOR ORDINALITY,
    ac VARCHAR(100) PATH "$.a" DEFAULT '111' ON EMPTY DEFAULT '999' ON ERROR,
    aj JSON PATH "$.a" DEFAULT '{"x": 333}' ON EMPTY,
    bx INT EXISTS PATH "$.b"
  )
) AS tt;

The article also notes that comparison, ordering, and aggregation of JSON values are currently of limited practical value.

Finally, the author invites readers to share the article, join the architecture community, and continue learning.