MySQL JSON_VALUE() Function

Summary: in this tutorial, you will learn how to use the MySQL JSON_VALUE() function to extract a value and convert it to a desired type.

Introduction to MySQL JSON_VALUE() function

The JSON_VALUE() function allows you to extract a value at a path in a JSON document and optionally convert the value to a desired type.

Here’s the syntax of the JSON_VALUE() function:

JSON_VALUE(json_doc, path [RETURNING type] [on_empty] [on_error])Code language: SQL (Structured Query Language) (sql)

The JSON_VALUE() function has the following parameters and optional clauses:

json_doc

json_doc: This is the JSON document from which you want to extract the value.

path

path: This is the JSON path expression that specifies the value within the json_doc that you want to extract.

RETURNING type

RETURNING type: This clause allows you to convert the extracted value to a desired type including float, double, decimal, signed, unsigned, date, datetime, year, char, and json.

If you omit the RETURNING type clause, the function returns a value with the type of VARCHAR(512).

on_empty

on_empty clause determines how the function behaves when the path does not locate any data. The on_empty clause accepts one of the following values:

• NULL ON EMPTY: The function returns NULL.
• DEFAULT value ON EMPTY: The function returns the value.
• ERROR ON EMPTY: The function raises an error.

on_error

on_error clause determines the outcome when an error occurs. It takes one of the following values:

• NULL ON ERROR: The function returns NULL.
• DEFAULT value on ERROR: The function returns the value.
• ERROR ON ERROR: The function throws an error.

Note that the JSON_VALUE() function has been available since MySQL 8.0.21.

MySQL JSON_VALUE() function examples

Let’s take some examples of using the MySQL JSON_VALUE() function.

1) Extracting a string value

The following example uses the JSON_VALUE() function to extract a string from a JSON document:

SELECT 
  JSON_VALUE(
    '{"name": "John Doe","age": 22, "date_of_birth":"1990-12-15"}', 
    "$.name"
  ) name;Code language: SQL (Structured Query Language) (sql)

Output:

+----------+
| name     |
+----------+
| John Doe |
+----------+
1 row in set (0.01 sec)Code language: SQL (Structured Query Language) (sql)

2) Extracting a number

The following example uses the JSON_VALUE() function to extract a value and convert it to a value of the UNSIGNED type:

SELECT 
  JSON_VALUE(
    '{"name": "John Doe","age": 22, "date_of_birth":"1990-12-15"}', 
    "$.age" RETURNING UNSIGNED
  ) age;Code language: SQL (Structured Query Language) (sql)

Output:

+------+
| age  |
+------+
|   22 |
+------+
1 row in set (0.01 sec)Code language: SQL (Structured Query Language) (sql)

3) Extracting a date

The following example uses the JSON_VALUE() function to extract a value and convert it to a DATE value:

SELECT 
  JSON_VALUE(
    '{"name": "John Doe","age": 22, "date_of_birth":"1990-12-15"}', 
    "$.date_of_birth" RETURNING DATE
  ) date_of_birth;Code language: SQL (Structured Query Language) (sql)

Output:

+---------------+
| date_of_birth |
+---------------+
| 1990-12-15    |
+---------------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

Summary

• Use the JSON_VALUE() function to extract a value and convert it to a specific type.