MariaDB JSON_VALUE() Function

In MariaDB, JSON_VALUE() is a built-in function that returns a scalar value from a JSON document based on the provided path.

Use JSON_QUERY() if you need to query objects or arrays from JSON documents.

MariaDB JSON_VALUE() Syntax

Here is the syntax for the MariaDB JSON_VALUE() function:

JSON_VALUE(json_doc, path)

Parameters

json_doc

Required. A JSON document.

path

Required. You should specify at least one path expression.

If you supply the wrong number of arguments, MariaDB will report an error: ERROR 1582 (42000): Incorrect parameter count in the call to native function 'JSON_VALUE'.

Return value

A MariaDB JSON_VALUE() function returns a scalar value from a JSON document based on the provided path.

If the path given in the JSON document is not a scalar value, JSON_VALUE() it will be returned NULL.

The MariaDB JSON_VALUE() function will return NULL if the given JSON is invalid.

The MariaDB JSON_VALUE() function will return NULL if any argument is NULL.

MariaDB JSON_VALUE() Examples

The following examples show the usage of the MariaDB JSON_VALUE() function.

Basic example

SET @json_doc = '[1, 2, {"x": 3}]';
SELECT
  @json_doc AS 'Json',
  JSON_VALUE(@json_doc, '$[0]') AS `$[0]`;

Output:

+------------------+------+
| Json             | $[0] |
+------------------+------+
| [1, 2, {"x": 3}] | 1    |
+------------------+------+

If the path you give is not a scalar value, MariaDB JSON_VALUE() will return NULL.

SET @json_doc = '[1, 2, {"x": 3}]';
SELECT
  @json_doc AS 'Json',
  JSON_VALUE(@json_doc, '$') AS `$`,
  JSON_VALUE(@json_doc, '$[2]') AS `$[2]`;

Output:

+------------------+------+------+
| Json             | $    | $[2] |
+------------------+------+------+
| [1, 2, {"x": 3}] | NULL | NULL |
+------------------+------+------+

In this example, since $ is an array and the value at $[2] is an object, they are both returned NULL.

Invalid JSON

The MariaDB JSON_VALUE() function will return NULL if the given JSON is invalid.

SELECT JSON_VALUE('a', '$[0]');

Output:

+-------------------------+
| JSON_VALUE('a', '$[0]') |
+-------------------------+
| NULL                    |
+-------------------------+

NULL parameters

The MariaDB JSON_VALUE() function will return NULL if any argument is NULL.

SELECT
    JSON_VALUE(NULL, '$'),
    JSON_VALUE('[1,2]', NULL);

Output:

+-----------------------+---------------------------+
| JSON_VALUE(NULL, '$') | JSON_VALUE('[1,2]', NULL) |
+-----------------------+---------------------------+
| NULL                  | NULL                      |
+-----------------------+---------------------------+

Conclusion

In MariaDB, JSON_VALUE() is a built-in function that returns a scalar value from a JSON document based on the provided path.