Master MySQL 8.0 JSON: Essential Functions, Queries, and Best Practices

After extensive testing, the project will migrate to MySQL 8.0, which adds and optimizes many JSON APIs.

Although most JSON handling is done at the application layer, knowing MySQL’s JSON syntax helps with debugging; the following essential features are highlighted for future reference.

Simple Overview

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

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

MySQL 8.0 also supports GeoJSON operations for geographic data.

JSON columns can be indexed using functional indexes (e.g., INDEX (JSON_EXTRACT(col, '$.path'))).

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 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"}

Additional utilities include JSON_QUOTE() to escape JSON strings, JSON_PRETTY() for formatted output, and type‑conversion functions such as CAST(JSON_EXTRACT(jdoc, '$.id') AS UNSIGNED).

JSON Merge Functions

The functions JSON_MERGE_PRESERVE() and JSON_MERGE_PATCH() exist but are rarely needed in typical business scenarios.

JSON Path Expression

The --> and -->> operators retrieve values using JSON Path expressions, which follow the ECMAScript standard and are familiar to front‑end developers.

-- Retrieve a scalar value
SELECT col->"$.mascot" FROM qtest;
-- Retrieve an unquoted string
SELECT sentence->>"$.mascot" FROM facts;

Examples of JSON Path syntax:

$[0] = 3;
$[1] = {"a": [5,6], "b": 10};
$[2] = [99,100];
$[1].a = [5,6];
$[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}];

Finding and Modifying JSON

// Extract all top‑level values
SELECT JSON_EXTRACT('{"a":1,"b":2,"c":[3,4,5]}', '$.*');
// Result: [1, 2, [3,4,5]]

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

// Deep search for key "b"
SELECT JSON_EXTRACT('{"a":{"b":1},"c":{"b":2}}', '$**.b');
// Result: [1,2]

// Slice an array
SELECT JSON_EXTRACT('[1,2,3,4,5]', '$[1 to 3]');
// Result: [2,3,4]

-- Update JSON values
SET @j = '["a", {"b": [true, false]}, [10,20]]';
SELECT JSON_SET(@j, '$[1].b[0]', 1, '$[2][2]', 2);
-- Result: ["a", {"b": [1,false]}, [10,20,2]]

JSON Table Functions

The JSON_TABLE() function treats JSON data as a relational table, enabling column extraction and row generation.

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;

Other topics such as comparison, ordering, and aggregation of JSON values are mentioned but deemed of limited practical value.