10.9 preview feature: JSON path expression and JSON_OVERLAPS()

The MariaDB 10.9 preview releases introduce a MySQL compatibility syntax extension, range notation. The ‘last’ keyword was added in JSON path expression, as requested in MDEV-22224 and MDEV-27911. Additionally, negative indexes are now also supported. Range notation, implemented using the ‘to’ keyword, and the ‘last’ keyword, is basically an array element selector.

Syntax:

Range notation:
[M to N] selects a range of elements starting from index M to N.
Last keyword:
[last-N] / [last] selects the Nth from last element, and last element, respectively.
Negative index:
[-N] Selects the Nth element from the end.

SET @json= '[
             [1, {"key1": "value1"}, 3],
             [false, 5, 6],
             [7, 8, [9, {"key2": 2}, 11]],
             [15, 1.34, [14], ["string1", [16, {"key1":[1,2,3,[4,5,6]]}, 18]]],
             [19, 20],
             21, 22
            ]';
SELECT JSON_EXTRACT(@json, '$[0 to last-3][-2]') AS selected_elements;
+-------------------------------------+
| selected_elements                   |
+-------------------------------------+
| [{"key1": "value1"}, 5, 8, [14]]    |
+-------------------------------------+

JSON_OVERLAPS() (MDEV-27677) is another function added for compatibility with MySQL. It compares two JSON documents and returns true if one of the following is true between two JSON documents:
1) at least one common key-value pair between two objects
2) at least one array element common between two arrays.
If objects are being compared as elements of JSON array, then both objects should match completely.
3) If either of the JSON document is a scalar and other is an array then the scalar should have same value and type as one of the elements in array.
If none of the above conditions are satisfied then it returns false. A partial match is considered no-match.

Example:

SELECT JSON_OVERLAPS('{"A": 1, "B": {"key1":"value1"}}', '{"A": 2, "B": {"key1":"value1"}}') AS is_overlap;
+---------------------+
| is_overlap          |
+---------------------+
| 1                   |
+---------------------+

SELECT JSON_OVERLAPS('[1, 2, true, false, null]', '[3, 4, [1]]') AS is_overlap;
+--------------------- +
| is_overlap           |
+----------------------+
| 0                    |
+----------------------+

Download the new mariadb-10.9.0-mdev-27911-json preview release here!