Traversing JSON Array Ranges in MySQL 8.0

MySQL introduced the JSON data type starting with version 5.7, allowing storage of dictionary‑like and array‑like structures and providing a set of functions for navigating JSON strings.

Array traversal can be performed using simple path expressions such as "$[index]" for a single element or "$[*]" for all elements. MySQL 8.0 adds range traversal with syntax like "$[m to n]" , where n may be the keyword last to indicate the final index.

Example JSON string (excerpt from an EXPLAIN FORMAT=JSON result) is assigned to a variable:

set @json_str1 = '{
  "query_block": {
    "table": {
      "filtered": "100.00",
      "cost_info": {
        "eval_cost": "898504.10",
        "read_cost": "17457.25",
        "prefix_cost": "915961.35",
        "data_read_per_join": "137M"
      },
      "table_name": "t1",
      "access_type": "ALL",
      "used_columns": [
        "id",
        "r1",
        "r2",
        "r3",
        "r4",
        "r5",
        "r6",
        "r7",
        "r8"
      ],
      "rows_examined_per_scan": 8985041,
      "rows_produced_per_join": 8985041
    },
    "cost_info": {
      "query_cost": "915961.35"
    },
    "select_id": 1
  }
}';

The used_columns key holds an array. Extracting it into a variable:

mysql> select json_extract(@json_str1,"$.query_block.table.used_columns") into @json_array1;

A traditional stored procedure sp_print_json_array_sample iterates over the array, removing the last element on each loop and storing intermediate results in a temporary table:

DELIMITER $$
USE `ytt`$$
DROP PROCEDURE IF EXISTS `sp_print_json_array_sample`$$
CREATE DEFINER=`root`@`%` PROCEDURE `sp_print_json_array_sample`(
    IN f_str1 JSON
)
BEGIN
    DECLARE v_tmp_length TINYINT UNSIGNED DEFAULT 0;
    DECLARE i TINYINT UNSIGNED DEFAULT 0;
    SET v_tmp_length = JSON_LENGTH(f_str1);
    SET i = v_tmp_length;
    SET @v_tmp_result = f_str1;
    DROP TEMPORARY TABLE IF EXISTS tmp;
    CREATE TEMPORARY TABLE IF NOT EXISTS tmp(str1 JSON);
    WHILE i > 0 DO
        SET i = i - 1;
        SET @stmt = CONCAT('select json_remove(@v_tmp_result,"$[',i,']") into @v_tmp_result');
        INSERT INTO tmp VALUES (@v_tmp_result);
        PREPARE s1 FROM @stmt;
        EXECUTE s1;
    END WHILE;
    DROP PREPARE s1;
    SET @v_stmt = NULL;
    SET @v_tmp_result = NULL;
    SELECT * FROM tmp;
END$$
DELIMITER ;

Calling the procedure yields a series of arrays with decreasing length:

mysql> call sp_print_json_array_sample(@json_array1);
+--------------------------------------------------------+
| str1                                                   |
+--------------------------------------------------------+
| ["id", "r1", "r2", "r3", "r4", "r5", "r6", "r7", "r8"] |
| ["id", "r1", "r2", "r3", "r4", "r5", "r6", "r7"]       |
| ["id", "r1", "r2", "r3", "r4", "r5", "r6"]             |
| ["id", "r1", "r2", "r3", "r4", "r5"]                   |
| ["id", "r1", "r2", "r3", "r4"]                         |
| ["id", "r1", "r2", "r3"]                               |
| ["id", "r1", "r2"]                                     |
| ["id", "r1"]                                           |
| ["id"]                                                 |
+--------------------------------------------------------+
9 rows in set (0.01 sec)

In MySQL 8.0 the same result can be obtained without a loop by using the range syntax directly:

DELIMITER $$
USE `ytt`$$
DROP PROCEDURE IF EXISTS `sp_print_json_array_sample`$$
CREATE DEFINER=`root`@`%` PROCEDURE `sp_print_json_array_sample`(
    IN f_str1 JSON
)
BEGIN
    DECLARE v_tmp_length TINYINT UNSIGNED DEFAULT 0;
    DECLARE i TINYINT UNSIGNED DEFAULT 0;
    SET v_tmp_length = JSON_LENGTH(f_str1);
    SET i = v_tmp_length;
    DROP TEMPORARY TABLE IF EXISTS tmp;
    CREATE TEMPORARY TABLE IF NOT EXISTS tmp(str1 JSON);
    WHILE i > 0 DO
        SET i = i - 1;
        SET @stmt = CONCAT(' insert into tmp select json_extract(', f_str1, ', "$[ 0 to ', i, ']" )');
        PREPARE s1 FROM @stmt;
        EXECUTE s1;
    END WHILE;
    DROP PREPARE s1;
    SELECT str1 AS array_result FROM tmp;
END$$
DELIMITER ;

Calling the optimized procedure produces the same nine rows:

mysql> call sp_print_json_array_sample(@json_array1);
+--------------------------------------------------------+
| array_result                                           |
+--------------------------------------------------------+
| ["id", "r1", "r2", "r3", "r4", "r5", "r6", "r7", "r8"] |
| ["id", "r1", "r2", "r3", "r4", "r5", "r6", "r7"]       |
| ... (remaining rows omitted for brevity) ...
+--------------------------------------------------------+
9 rows in set (0.00 sec)

The range path can also use the keyword last . For example, "$[0 to last-7]" returns the first two elements of the original array:

mysql> select json_extract(@json_array1,'$[0 to last-7]') as result;
+--------------+
| result       |
+--------------+
| ["id", "r1"] |
+--------------+

In summary, MySQL 8.0’s JSON array range traversal simplifies array slicing operations, making it easier for developers to manipulate JSON data directly within SQL.