MySQL JSON_VALUE() Function

In MySQL, the JSON_VALUE() function extracts the value of the specified path from a specified JSON document and returns it.

`JSON_VALUE()` Syntax

Here is the syntax of the MySQL JSON_VALUE() function:

JSON_VALUE(
  json,
  path [RETURNING type]
  [{NULL | ERROR | DEFAULT value} ON EMPTY]
  [{NULL | ERROR | DEFAULT value} ON ERROR])

Parameters

json

Required. A JSON document.
path

Required. a path expression.
RETURNING type

Optional. It indicates the type of return value. You can use one of the following values:
- FLOAT
- DOUBLE
- DECIMAL
- SIGNED
- UNSIGNED
- DATE
- TIME
- DATETIME
- YEAR (MySQL 8.0.22 and later)
- CHAR
- JSON
{NULL | ERROR | DEFAULT value} ON EMPTY

Optional. If specified, it indicates how to handle empty data:
- NULL ON EMPTY: If the specified path has no data, the JSON_VALUE() function will use NULL. It is the default behavior.
- DEFAULT value ON EMPTY: If the specified path has no data, the JSON_VALUE() function will use value.
- ERROR ON EMPTY: If the specified path has no data, the JSON_VALUE() function will throw an error.
{NULL | ERROR | DEFAULT value} ON ERROR

Optional. If specified, it determines the logic for handling errors:
- NULL ON ERROR: The JSON_VALUE() function will use NULL if there is an error. It is the default behavior.
- DEFAULT value ON ERROR: The JSON_VALUE() function will use value if there is an error.
- ERROR ON ERROR: If there is an error, the JSON_VALUE() function will throw an error.

Return value

If the JSON document has a value of the specified path, the JSON_VALUE() function returns the value of the path. The JSON_VALUE() function defaults to returning the data as a string, unless you use the RETURNING type clause.

There will happen an error in the following cases:

MySQL will give an error if the parameter json is not a valid JSON document. You can use JSON_VALID() to verify the JSON document.
If the parameter path is not a valid path expression, MySQL will give an error.

`JSON_VALUE()` Examples

Let’s first create a JSON document for use in the following examples:

SET @json = '[
  {
    "name": "Tim",
    "age": 20,
    "amount": 123.456,
    "note": "He is perfect."
  },
  {
    "name": "Tom",
    "age": 20,
    "amount": 456.1
  }
]';

Here, we create a JSON array that contains two user information.

Returns the age of the first user

SELECT JSON_VALUE(@json, '$[0].age');

+-------------------------------+
| JSON_VALUE(@json, '$[0].age') |
+-------------------------------+
| 20                            |
+-------------------------------+

Returns the amount of the second user

SELECT JSON_VALUE(@json, '$[1].amount');

+----------------------------------+
| JSON_VALUE(@json, '$[1].amount') |
+----------------------------------+
| 456.1                            |
+----------------------------------+

If you want the returned data to have 3 decimal places like the first user’s amount, you can use the RETURNING DECIMAL(10,3)clause :

SELECT JSON_VALUE(@json, '$[1].amount' RETURNING DECIMAL(9,3));

+---------------------------------------------------------+
| JSON_VALUE(@json, '$[1].amount' RETURNING DECIMAL(9,3)) |
+---------------------------------------------------------+
|                                                 456.100 |
+---------------------------------------------------------+

Defaults

If there is no data at the specified path, you can use the ON EMPTYclause set a default value:

SELECT
    JSON_VALUE(@json, '$[0].note' DEFAULT 'Nothing' ON EMPTY) AS `$[0].note`,
    JSON_VALUE(@json, '$[1].note' DEFAULT 'Nothing' ON EMPTY) AS `$[1].note`;

+----------------+-----------+
| $[0].note      | $[1].note |
+----------------+-----------+
| He is perfect. | Nothing   |
+----------------+-----------+

JSON_VALUE() Syntax