When working with JSON in MariaDB, you can use JSONPath expressions to manipulate data within a JSON document.

One powerful feature that MariaDB provides is the wildcard step (**). This allows you to recursively select all child elements of the current element.

The wildcard step is a non-standard extension, and it’s also supported with the same meaning in MySQL.

Example

Here’s an example to demonstrate:

SET @json = '[
    { "name" : "Wag", "type" : "Dog" },
    { "name" : "Bark", "type" : "Dog" },
    { "name" : "Meow", "type" : "Cat" },
    { "name" : "Scratch", "type" : "Cat" }
]';

SELECT JSON_EXTRACT(@json, '$**.name');

Result:

+------------------------------------+
| JSON_EXTRACT(@json, '$**.name')    |
+------------------------------------+
| ["Wag", "Bark", "Meow", "Scratch"] |
+------------------------------------+

The wildcard step went through all objects and selected the values from their name members.

In this case, we could have achieved the same result with another selector. The array wildcard selector would have helped us get the same result:

SET @json = '[
    { "name" : "Wag", "type" : "Dog" },
    { "name" : "Bark", "type" : "Dog" },
    { "name" : "Meow", "type" : "Cat" },
    { "name" : "Scratch", "type" : "Cat" }
]';

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

Result:

+------------------------------------+
| JSON_EXTRACT(@json, '$[*].name')   |
+------------------------------------+
| ["Wag", "Bark", "Meow", "Scratch"] |
+------------------------------------+

Same result.

However, things change if we use a different document.

Example 2

In the following example, we get a different result between the wildcard step and the array selector:

SET @json = '[
    { 
        "name" : "Homer", 
        "pets" : [
            { 
            "name" : "Wag",
            "type" : "Dog"
            },
            { 
            "name" : "Scratch",
            "type" : "Cat"
            }
        ]    
    }
]';

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

Result:

+---------------------------------+----------------------------------+
| JSON_EXTRACT(@json, '$**.name') | JSON_EXTRACT(@json, '$[*].name') |
+---------------------------------+----------------------------------+
| ["Homer", "Wag", "Scratch"]     | ["Homer"]                        |
+---------------------------------+----------------------------------+

Different result.

The results will obviously depend on the document and the actual construction of the JSONPath expression, and the selectors used will depend on your requirements.

One thing to remember is that the wildcard step must not be the last step in the JSONPath expression. It must be followed by an array or object member selector step.

Example 3

Here’s an example 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"]           |
+-------------------------------------+

Arrays are zero-based and so $**.sizes[1] refers to the second element in all sizes arrays.