Master MySQL 8.0 JSON Functions: Practical Tips and Code Samples

After extensive testing, we are migrating to MySQL 8.0, which introduces numerous new and optimized JSON APIs.

https://dev.mysql.com/doc/refman/8.0/en/json.html https://dev.mysql.com/doc/refman/8.0/en/json-utility-functions.html

Simple Overview

JSON columns cannot be NULL; the JSON format is similar to LONGBLOB or LONGTEXT and its maximum size is limited by max_allowed_packet.

Use JSON_STORAGE_SIZE(col) to check the storage size of a JSON column.

MySQL also supports GeoJSON operations for geographic data.

JSON columns can be indexed using functional indexes introduced in MySQL 8.0.

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

Basic JSON Tools

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

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

// Nested array and object example
[99, {"id": "HK500", "cost": 75.99}, ["hot", "cold"]]

// Date/Time values
["12:18:29.000000", "2015-07-29", "2015-07-29 12:18:29.000000"]

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

// Pretty‑print JSON
SELECT JSON_PRETTY(jdoc);

Merge operations such as JSON_MERGE_PRESERVE() and JSON_MERGE_PATCH() are rarely needed in typical business scenarios.

JSON Path Expression

The --> and -->> operators retrieve values by JSON Path, which follows the ECMAScript specification and is familiar to front‑end developers.

SELECT col->"$.mascot" FROM qtest;   -- Returns the JSON string with quotes
SELECT sentence-->>"$.mascot" FROM facts; -- Returns the raw value

Example of accessing array elements and ranges:

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

Finding and Modifying JSON

SELECT JSON_EXTRACT('{"a":1,"b":2,"c":[3,4,5]}', '$.*');
-- Result: [1,2,[3,4,5]]

SELECT JSON_EXTRACT('{"a":1,"b":2,"c":[3,4,5]}', '$.c[*]');
-- Result: [3,4,5]

SELECT JSON_SET(@j, '$[1].b[0]', 1, '$[2][2]', 2);
-- Updates nested elements

SELECT JSON_REMOVE(@j, '$[2]', '$[1].b[1]');
-- Removes specified paths

JSON Table Functions

When a JSON document represents a tabular structure, JSON_TABLE can project it into relational rows and columns.

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.