How the JSON_QUERY() function works in Mariadb?

The JSON_QUERY() function is a built-in function in Mariadb that allows you to extract a JSON document from another JSON document at a given path. It can be useful when you want to access a nested or complex JSON document in a simple way.

Syntax

The syntax of the JSON_QUERY() function is as follows:

JSON_QUERY(json_doc, path)

The function takes two arguments, as follows:

• json_doc: This is the JSON document to extract from. It must be a valid JSON document or a column that contains JSON documents.
• path: This is the path to the JSON document to extract. It must be a valid JSON path expression or a column that contains JSON path expressions.

The function returns a new JSON document that is extracted from the JSON document at the specified path.

Examples

Example 1: Extracting a JSON array from a JSON object

In this example, we use the JSON_QUERY() function to extract a JSON array from a JSON object.

SELECT JSON_QUERY(
  '{"name": "Alice", "age": 25, "gender": "female", "hobbies": ["reading", "writing", "coding"]}',
  '$.hobbies'
) AS extracted_doc;

The output is:

+----------------------------------+
| extracted_doc                    |
+----------------------------------+
| ["reading", "writing", "coding"] |
+----------------------------------+

As you can see, the function returns a JSON array that contains the value of the hobbies key in the JSON object.

Example 2: Extracting a JSON object from a JSON array

In this example, we use the JSON_QUERY() function to extract a JSON object from a JSON array.

SELECT JSON_QUERY(
  '[{"name": "Alice", "age": 25, "gender": "female"}, {"name": "Bob", "age": 30, "gender": "male"}]',
  '$[0]'
) AS extracted_doc;

The output is:

+--------------------------------------------------+
| extracted_doc                                    |
+--------------------------------------------------+
| {"name": "Alice", "age": 25, "gender": "female"} |
+--------------------------------------------------+

As you can see, the function returns a JSON object that contains the first element of the JSON array.

Example 3: Extracting a JSON document with a complex path

In this example, we use the JSON_QUERY() function to extract a JSON document with a complex path.

SELECT JSON_QUERY(
  '{"name": "Alice", "age": 25, "gender": "female", "friends": [{"name": "Bob", "age": 30, "gender": "male", "hobbies": ["gaming", "cooking", "traveling"]}, {"name": "Carol", "age": 28, "gender": "female", "hobbies": ["singing", "dancing", "painting"]}]}',
  '$.friends[1].hobbies'
) AS extracted_doc;

The output is:

+------------------------------------+
| extracted_doc                      |
+------------------------------------+
| ["singing", "dancing", "painting"] |
+------------------------------------+

As you can see, the function returns a JSON array that contains the value of the hobbies key in the second element of the friends array in the JSON object.

Related Functions

There are some other functions in Mariadb that are related to the JSON_QUERY() function. Here are some of them:

• JSON_VALUE() function: This function extracts a scalar value from a JSON document at a given path. It takes a JSON document and a path as arguments, and returns a string, a number, a boolean, or a null that is the value of the JSON document at the specified path. For example, JSON_VALUE('{"a": "b", "c": "d"}', '$.c') returns "d".
• JSON_SEARCH() function: This function searches for a value in a JSON document and returns the path to the value. It takes a JSON document, a mode argument, a search value, and optional path arguments, and returns a string that is the path to the first or all occurrences of the search value in the JSON document, depending on the mode. For example, JSON_SEARCH('{"a": "b", "c": ["d", "e"]}', 'one', 'e') returns "$[1]".
• JSON_REPLACE() function: This function replaces a value in a JSON document at a given path. It takes a JSON document and one or more pairs of path and value arguments, and returns a new JSON document that has the same values as the original JSON document, except for the replaced values. For example, JSON_REPLACE('{"a": "b", "c": "d"}', '$.c', 'e') returns {"a": "b", "c": "e"}.

Conclusion

The JSON_QUERY() function is a useful function in Mariadb that allows you to extract a JSON document from another JSON document at a given path. It can handle different types of JSON documents, such as arrays, objects, or scalars. It returns a new JSON document that is extracted from the JSON document at the specified path. There are also some other functions that are related to the JSON_QUERY() function, such as JSON_VALUE(), JSON_SEARCH(), and JSON_REPLACE(). You can use these functions to manipulate JSON data in different ways.