How the JSON_EXTRACT() function works in Mariadb?

The JSON_EXTRACT() function is a JSON function that extracts a value or values from a JSON document at a given path or paths.

Posted on

The JSON_EXTRACT() function is a JSON function that extracts a value or values from a JSON document at a given path or paths. The function takes a JSON document and one or more path arguments. The function returns a JSON value or values that match the path or paths. If the JSON document or any of the paths are invalid, the function returns NULL.

Syntax

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

JSON_EXTRACT(json_doc, path[, path] ...)

Where json_doc is a valid JSON document and path is a valid JSON path expression.

Examples

Example 1: Extracting a value from a JSON document at the root level

In this example, we create a JSON document that contains an array of numbers using the JSON_ARRAY() function. Then we use the JSON_EXTRACT() function to extract the value at the root level.

SELECT JSON_EXTRACT(JSON_ARRAY(1, 2, 3), '$') AS result;

The output is:

+-----------+
| result    |
+-----------+
| [1, 2, 3] |
+-----------+

This means that the function returns the JSON value that matches the path $, which is the entire JSON document.

Example 2: Extracting a value from a JSON document at a specified path

In this example, we create a JSON document that contains an object with some key-value pairs using the JSON_OBJECT() function. Then we use the JSON_EXTRACT() function to extract the value at the path $.name.

SELECT JSON_EXTRACT(JSON_OBJECT('name', 'Alice', 'age', 25), '$.name') AS result;

The output is:

+---------+
| result  |
+---------+
| "Alice" |
+---------+

This means that the function returns the JSON value that matches the path $.name, which is the string "Alice".

Example 3: Extracting a value from a JSON document at a nested path

In this example, we create a JSON document that contains a nested object using the JSON_OBJECT() and JSON_ARRAY() functions. Then we use the JSON_EXTRACT() function to extract the value at the nested path $.hobbies[0].

SELECT JSON_EXTRACT(JSON_OBJECT('name', 'Bob', 'age', 30, 'hobbies', JSON_ARRAY('sports', 'music', 'movies')), '$.hobbies[0]') AS result;

The output is:

+----------+
| result   |
+----------+
| "sports" |
+----------+

This means that the function returns the JSON value that matches the nested path $.hobbies[0], which is the string "sports".

Example 4: Extracting multiple values from a JSON document at multiple paths

In this example, we create a JSON document that contains a nested object using the JSON_OBJECT() and JSON_ARRAY() functions. Then we use the JSON_EXTRACT() function to extract multiple values from the JSON document at multiple paths.

SELECT JSON_EXTRACT(JSON_OBJECT('name', 'Carol', 'age', 35, 'hobbies', JSON_ARRAY('music', 'movies', 'coding')), '$.name', '$.hobbies[2]') AS result;

The output is:

+---------------------+
| result              |
+---------------------+
| ["Carol", "coding"] |
+---------------------+

This means that the function returns a JSON array that contains the JSON values that match the paths $.name and $.hobbies[2], which are the strings "Carol" and "coding".

Example 5: Getting NULL if the JSON document or any of the paths are invalid

In this example, we use the JSON_EXTRACT() function to extract a value or values from a JSON document at a given path or paths. However, we use an invalid JSON document and an invalid path as arguments. The function returns NULL in this case.

SELECT JSON_EXTRACT('{"name": "Dave"', '$.name') AS result;

The output is:

+--------+
| result |
+--------+
|   NULL |
+--------+

This means that the function returns NULL because the JSON document is not valid (it is missing a closing brace).

SELECT JSON_EXTRACT(JSON_OBJECT('name', 'Eve', 'age', 40), '$**') AS result;

The output is:

+--------+
| result |
+--------+
|   NULL |
+--------+

This means that the function returns NULL because the path $** is not valid (it contains a wildcard token that is not allowed in this situation).

There are some other JSON functions that are related to the JSON_EXTRACT() function. Here are some of them:

  • JSON_CONTAINS(): This function tests whether a JSON document contains a specific value at a given path or paths. The function takes a JSON document, a value, and an optional path as arguments. The function returns 1 or 0 depending on whether the value is found within the document at the specified path or paths. If the JSON document, the value, or any of the paths are invalid, the function returns NULL.
  • JSON_CONTAINS_PATH(): This function tests whether a JSON document contains any data at a given path or paths. The function takes a JSON document, a oneOrAll argument, and one or more path arguments. The function returns 1 or 0 depending on whether any or all paths exist within the document. If the JSON document or any of the paths are invalid, the function returns NULL.
  • JSON_SEARCH(): This function searches for a value in a JSON document and returns the path or paths to the value. The function takes a JSON document, a oneOrAll argument, a search string, and an optional path as arguments. The function returns a JSON value or values that contain the path or paths to the value. If the JSON document, the search string, or any of the paths are invalid, the function returns NULL.

Conclusion

The JSON_EXTRACT() function is a useful JSON function that can extract a value or values from a JSON document at a given path or paths. The function takes a JSON document and one or more path arguments. The function returns a JSON value or values that match the path or paths. If the JSON document or any of the paths are invalid, the function returns NULL. There are also some other related functions that can test, search, or compare values in JSON documents, such as JSON_CONTAINS(), JSON_CONTAINS_PATH(), and JSON_SEARCH().