How the JSON_VALUE() function works in Mariadb?

The JSON_VALUE() function is a useful tool for extracting scalar values from JSON documents in Mariadb. It takes a JSON document and a JSON path expression as arguments, and returns the value of the JSON element that matches the path. If the path does not exist, or the value is not a scalar, the function returns NULL by default. However, you can also specify an optional on error clause to handle such cases.

In this article, we will learn how to use the JSON_VALUE() function in Mariadb, and see some examples of its syntax and usage. We will also explore some related functions that can help us work with JSON data in Mariadb.

Syntax

The general syntax of the JSON_VALUE() function is as follows:

JSON_VALUE(json_doc, path)

• json_doc is a valid JSON document, or an expression that evaluates to a JSON document.
• path is a JSON path expression that specifies the element to extract from the JSON document. The path must begin with a dollar sign ($) and follow the rules of the JSON path language.

Examples

Let’s see some examples of how to use the JSON_VALUE() function in Mariadb. We will use the following sample JSON document as our input:

set @json_doc = '{
  "name": "Alice",
  "age": 25,
  "gender": "female",
  "hobbies": ["reading", "writing", "coding"],
  "education": {
    "degree": "Bachelor of Science",
    "major": "Computer Science",
    "university": "MIT"
  }
}';

Example 1: Extracting a scalar value

We can use the JSON_VALUE() function to extract a scalar value from the JSON document, such as the name, age, or gender of the person. For example, to get the name of the person, we can use the following query:

SELECT JSON_VALUE(@json_doc, '$.name') AS name;

The output is:

+-------+
| name  |
+-------+
| Alice |
+-------+

Example 2: Extracting a nested value

We can also use the JSON_VALUE() function to extract a nested value from the JSON document, such as the degree, major, or university of the person’s education. For example, to get the degree of the person, we can use the following query:

SELECT JSON_VALUE(@json_doc, '$.education.degree') AS degree;

The output is:

+---------------------+
| degree              |
+---------------------+
| Bachelor of Science |
+---------------------+

Example 3: Handling non-existent paths

If we try to extract a value from a path that does not exist in the JSON document, the JSON_VALUE() function will return NULL by default. For example, if we try to get the phone number of the person, which is not present in the JSON document, we can use the following query:

SELECT JSON_VALUE(@json_doc, '$.phone') AS phone;

The output is:

+-------+
| phone |
+-------+
| NULL  |
+-------+

Example 4: Handling non-scalar values

If we try to extract a value from a path that is not a scalar, such as an array or an object, the JSON_VALUE() function will also return NULL by default. For example, if we try to get the hobbies of the person, which is an array, we can use the following query:

SELECT JSON_VALUE(@json_doc, '$.hobbies') AS hobbies;

The output is:

+---------+
| hobbies |
+---------+
| NULL    |
+---------+

Related Functions

The JSON_VALUE() function is not the only function that can help us work with JSON data in Mariadb. There are some other related functions that can perform different tasks, such as:

• JSON_EXTRACT() function: This function can extract one or more values from a JSON document, based on a JSON path expression. Unlike JSON_VALUE(), this function can return non-scalar values, such as arrays or objects, as JSON strings.

• JSON_QUERY() function: This function can extract a JSON subdocument from a JSON document, based on a JSON path expression. Unlike JSON_EXTRACT(), this function can return only arrays or objects, not scalars.

• JSON_CONTAINS() function: This function can check whether a JSON document contains a specific value at a given path. It returns 1 if the value is found, 0 if not, or NULL if any argument is NULL or the path is invalid.

• JSON_LENGTH() function: This function can return the length of a JSON document, or the number of elements in a JSON array or object, at a given path. It returns NULL if any argument is NULL or the path is invalid.

There are many more functions that can help us manipulate and query JSON data in Mariadb. You can find the full list and documentation of the JSON functions.

Conclusion

In this article, we have learned how to use the JSON_VALUE() function in Mariadb to extract scalar values from JSON documents. We have also seen how to handle errors and non-scalar values using the on error clause. Moreover, we have explored some related functions that can help us work with JSON data in Mariadb, such as JSON_EXTRACT(), JSON_QUERY(), JSON_CONTAINS(), and JSON_LENGTH().