Menu

MariaDB JSON_VALUE(): Extract a Scalar from JSON

MariaDB JSON_VALUE() extracts a scalar value from a JSON document at a specified path. It returns NULL for invalid JSON documents or paths that do not match. See the official MariaDB JSON_VALUE reference.

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

JSON_VALUE() returns the scalar selected by the path.

JSON_VALUE() also returns NULL if the path selects an object or array instead of a scalar, or if any argument is NULL. Use JSON_QUERY() when you need to return an object or array.

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 |
+------------------+------+------+

Here, $ selects an array and $[2] selects an object. JSON_VALUE() returns NULL for both because neither result is scalar.

Path with no match

If the JSON document is valid but the path does not select a value, JSON_VALUE() returns NULL:

SELECT JSON_VALUE('{"x": 1}', '$.missing');

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                      |
+-----------------------+---------------------------+