Master MySQL 8.0 JSON Functions: Practical Tips and Code Samples

MySQL 8.0 JSON Enhancements

After extensive testing, the author plans to migrate to MySQL 8.0, which adds many new JSON APIs.

Key Points

JSON columns cannot be NULL; they behave like LONGBLOB/LONGTEXT and are limited by max_allowed_packet.

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

MySQL 8.0 also supports GeoJSON operations.

JSON columns can be indexed using the new functional index syntax.

In‑place updates are possible with functions such as JSON_SET(), JSON_REPLACE(), JSON_REMOVE().

Basic JSON Functions

// Create a JSON array
SELECT JSON_ARRAY(1, "abc", NULL, TRUE, CURTIME());
// Create a JSON object
SELECT JSON_OBJECT('id', 87, 'name', 'carrot');
// Nested arrays and objects example
[99, {"id":"HK500","cost":75.99}, ["hot","cold"]] {"k1":"value","k2":[10,20]}

Other useful utilities include JSON_QUOTE, JSON_PRETTY, and type‑conversion functions such as CAST(JSON_EXTRACT(... ) AS UNSIGNED).

JSON Merge and Extraction

Merge functions (JSON_MERGE_PRESERVE, JSON_MERGE_PATCH) are rarely used in practice. The -> and ->> operators retrieve values by JSON Path, where ->> removes surrounding quotes.

SELECT col->"$.mascot" FROM qtest;
SELECT sentence->>"$.mascot" FROM facts;

JSON Path Expressions

JSON Path follows ECMAScript syntax, familiar to front‑end developers. Example:

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

Finding and Modifying JSON

SELECT JSON_EXTRACT('{"a":1,"b":2,"c":[3,4,5]}', '$.*');
SELECT JSON_EXTRACT('{"a":1,"b":2,"c":[3,4,5]}', '$.c[*]');
SELECT JSON_EXTRACT('{"a":{"b":1},"c":{"b":2}}', '$**.b');
SELECT JSON_SET(@j, '$[1].b[0]', 1, '$[2][2]', 2);
SELECT JSON_INSERT(@j, '$[1].b[0]', 1, '$[2][2]', 2);
SELECT JSON_REPLACE(@j, '$[1].b[0]', 1, '$[2][2]', 2);
SELECT JSON_REMOVE(@j, '$[2]', '$[1].b[1]');

JSON Table Functions

JSON_TABLE can treat JSON data as a relational table.

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;