{"id":12125,"date":"2023-11-06T07:10:46","date_gmt":"2023-11-06T14:10:46","guid":{"rendered":"https:\/\/www.mysqltutorial.org\/?page_id=12125"},"modified":"2023-11-06T07:11:53","modified_gmt":"2023-11-06T14:11:53","slug":"mysql-json_value","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-json\/mysql-json_value\/","title":{"rendered":"MySQL JSON_VALUE() Function"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the MySQL JSON_VALUE()<\/code> function to extract a value and convert it to a desired type.<\/p>\n\n\n\n

Introduction to MySQL JSON_VALUE() function<\/h2>\n\n\n\n

The JSON_VALUE()<\/code> function allows you to extract a value at a path<\/a> in a JSON document and optionally convert the value to a desired type.<\/p>\n\n\n\n

Here’s the syntax of the JSON_VALUE()<\/code> function:<\/p>\n\n\n

JSON_VALUE(json_doc, path [RETURNING type] [on_empty] [on_error])<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The JSON_VALUE()<\/code> function has the following parameters and optional clauses:<\/p>\n\n\n\n
json_doc<\/h3>\n\n\n\n
json_doc<\/code>: This is the JSON document from which you want to extract the value.<\/p>\n\n\n\n
path<\/h3>\n\n\n\n
path<\/code>: This is the JSON path<\/a> expression that specifies the value within the json_doc<\/code> that you want to extract.<\/p>\n\n\n\n
RETURNING type<\/h3>\n\n\n\n
RETURNING type<\/code>: 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. <\/p>\n\n\n\n
If you omit the RETURNING type<\/code> clause, the function returns a value with the type of VARCHAR(512)<\/code>.<\/p>\n\n\n\n
on_empty<\/h3>\n\n\n\n
on_empty<\/code> clause determines how the function behaves when the path does not locate any data. The on_empty<\/code> clause accepts one of the following values:<\/p>\n\n\n\n
    \n
  • NULL<\/code> ON<\/code> EMPTY<\/code>: The function returns NULL<\/code>.<\/li>\n\n\n\n
  • DEFAULT<\/code> value ON<\/code> EMPTY<\/code>: The function returns the value.<\/li>\n\n\n\n
  • ERROR<\/code> ON<\/code> EMPTY<\/code>: The function raises an error.<\/li>\n<\/ul>\n\n\n\n
    on_error<\/h3>\n\n\n\n
    on_error<\/code> clause determines the outcome when an error occurs. It takes one of the following values:<\/p>\n\n\n\n
      \n
    • NULL<\/code> ON<\/code> ERROR<\/code>: The function returns NULL<\/code>.<\/li>\n\n\n\n
    • DEFAULT value on ERROR<\/code>: The function returns the value<\/code>.<\/li>\n\n\n\n
    • ERROR<\/code> ON<\/code> ERROR<\/code>: The function throws an error.<\/li>\n<\/ul>\n\n\n\n
      Note that the JSON_VALUE()<\/code>\u00a0function has been available since MySQL 8.0.21.<\/p>\n\n\n\n
      MySQL JSON_VALUE() function examples<\/h2>\n\n\n\n
      Let’s take some examples of using the MySQL JSON_VALUE()<\/code> function.<\/p>\n\n\n\n
      1) Extracting a string value<\/h3>\n\n\n\n
      The following example uses the JSON_VALUE()<\/code> function to extract a string from a JSON document:<\/p>\n\n\n
      SELECT<\/span> \n  JSON_VALUE(\n    '{\"name\": \"John Doe\",\"age\": 22, \"date_of_birth\":\"1990-12-15\"}'<\/span>, \n    \"$.name\"<\/span>\n  ) name<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
      Output:<\/p>\n\n\n
      +----------+<\/span>\n| name     |\n+----------+<\/span>\n| John Doe |\n+----------+<\/span>\n1 row in set<\/span> (0.01<\/span> sec)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
      2) Extracting a number<\/h3>\n\n\n\n
      The following example uses the JSON_VALUE()<\/code> function to extract a value and convert it to a value of the UNSIGNED<\/code> type:<\/p>\n\n\n
      SELECT<\/span> \n  JSON_VALUE(\n    '{\"name\": \"John Doe\",\"age\": 22, \"date_of_birth\":\"1990-12-15\"}'<\/span>, \n    \"$.age\"<\/span> RETURNING<\/span> UNSIGNED<\/span>\n  ) age;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
      Output:<\/p>\n\n\n
      +------+<\/span>\n| age  |\n+------+<\/span>\n|   22 |\n+------+<\/span>\n1 row in set<\/span> (0.01<\/span> sec)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
      3) Extracting a date<\/h3>\n\n\n\n
      The following example uses the JSON_VALUE()<\/code> function to extract a value and convert it to a DATE<\/code> value:<\/p>\n\n\n
      SELECT<\/span> \n  JSON_VALUE(\n    '{\"name\": \"John Doe\",\"age\": 22, \"date_of_birth\":\"1990-12-15\"}'<\/span>, \n    \"$.date_of_birth\"<\/span> RETURNING<\/span> DATE<\/span>\n  ) date_of_birth;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
      Output:<\/p>\n\n\n
      +---------------+<\/span>\n| date_of_birth |\n+---------------+<\/span>\n| 1990-12-15    |\n+---------------+<\/span>\n1 row in set<\/span> (0.00<\/span> sec)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
      Summary<\/h2>\n\n\n\n
        \n
      • Use the JSON_VALUE()<\/code> function to extract a value and convert it to a specific type.<\/li>\n<\/ul>\n
        \n\t
        \n\t\t
        Was this tutorial helpful? <\/div>\n\t\t
        \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
        \n\t\t
        \n\t\t\t
        <\/div>\n\t\t\t\n\t\t\t