MariaDB includes two selectors that enable us to select elements from JSON arrays:

• [N] selects element number N in the array (for example, [0] to select the first element).
• [*] selects all elements in the array.

These can be used in a number of JSON functions that are included in MariaDB. The following examples use them with the JSON_EXTRACT() function in order to return selected array elements.

Example – [N]

Here’s a simple example to demonstrate how to select a single array element:

SELECT JSON_EXTRACT('[1,2,3]', '$[1]');

Result:

+---------------------------------+
| JSON_EXTRACT('[1,2,3]', '$[1]') |
+---------------------------------+
| 2                               |
+---------------------------------+

Arrays are zero-based, and so $[1] selects the second array element.

Here’s another example, this time using a slightly larger JSON document:

SET @json = '
    { 
        "_id": 1, 
        "product": "Left Handed Screwdriver", 
        "sizes": [ "S", "M", "L" ] 
    }
';

SELECT JSON_EXTRACT(@json, '$.sizes[1]');

Result:

+-----------------------------------+
| JSON_EXTRACT(@json, '$.sizes[1]') |
+-----------------------------------+
| "M"                               |
+-----------------------------------+

In this case, .sizes specifies the sizes object member, and so $.sizes[1] selects the second item in the sizes array.

Example- [*]

The [*] selector selects all elements in the array.

Example:

SET @json = '
    { 
        "_id": 1, 
        "product": "Left Handed Screwdriver", 
        "sizes": [ "S", "M", "L" ] 
    }
';

SELECT JSON_EXTRACT(@json, '$.sizes[*]');

Result:

+-----------------------------------+
| JSON_EXTRACT(@json, '$.sizes[*]') |
+-----------------------------------+
| ["S", "M", "L"]                   |
+-----------------------------------+

In this case, the result looks exactly the same as the original array, in which case, it could have been returned just by specifying $.sizes.

Multiple Array Selectors

But here’s another example that uses two array selectors.

First, we use a wildcard selector to select all elements in the array. Then we use another array selector to select only the second element in an array that’s nested within those elements:

SET @json = '
{ 
    "products" : 
    [
        { 
            "_id": 1, 
            "product": "Left Handed Screwdriver", 
            "sizes": [ "S", "M", "L" ] 
        },
        { 
            "_id": 2, 
            "product": "Long Weight", 
            "sizes": [ 8, 7, 10 ] 
        },
        { 
            "_id": 3, 
            "product": "Bottomless Coffee Cup", 
            "sizes": [ "Tall", "Grande", "Venti" ] 
        }
    ]
}
';

SELECT JSON_EXTRACT(@json, '$.products[*].sizes[1]');

Result:

+-----------------------------------------------+
| JSON_EXTRACT(@json, '$.products[*].sizes[1]') |
+-----------------------------------------------+
| ["M", 7, "Grande"]                            |
+-----------------------------------------------+

Wildcard Step

If your JSON document contains multiple arrays with the same name, some nested at different levels or in their own object, you can select them all with the help of the wildcard step (**). The wildcard step recursively selects all child elements of the current element.

Here, we use it to help build a JSONPath expression that returns the second array element from all arrays called sizes, including arrays that are nested within other documents:

SET @json = '[
    { 
        "_id": 1, 
        "product": { 
            "name" : "Tuxedo", 
            "color" : "Blue",
            "sizes": [ "S", "M", "L" ],
            "accessories" : {
                "belt" : {
                    "color" : "Navy",
                    "sizes" : [ "Wide", "Narrow" ]
                },
                "tie" : {
                    "color" : "Black",
                    "sizes" : [ "Short", "Medium", "Long" ]
                }
            }
        }
    }
]';

SELECT JSON_EXTRACT(@json, '$**.sizes[1]');

Result:

+-------------------------------------+
| JSON_EXTRACT(@json, '$**.sizes[1]') |
+-------------------------------------+
| ["M", "Narrow", "Medium"]           |
+-------------------------------------+