How to convert a JSON value to a given MySQL data type using the JSON_VALUE() function?

JSON_VALUE(json, path RETURNING type on_empty on_error) is a MySQL built-in function that converts a JSON value to a given MySQL data type. The input JSON value can be a member of a parent JSON array or object. For example:

SELECT JSON_VALUE('{"fname": "Joe", "lname": "Palmer"}', '$.fname');
  -- +--------------------------------------------------------------+
  -- | JSON_VALUE('{"fname": "Joe", "lname": "Palmer"}', '$.fname') |
  -- +--------------------------------------------------------------+
  -- | Joe                                                          |
  -- +--------------------------------------------------------------+

SELECT JSON_VALUE('{"item": "shoes", "price": "49.95"}', '$.price'
  RETURNING DECIMAL(4,2)) AS price;
  -- +-------+
  -- | price |
  -- +-------+
  -- | 49.95 |
  -- +-------+

Reference information of the JSON_VALUE() function:

JSON_VALUE(json, path COLUMNS (column_list) AS alias): rows
  Creates an inline table by parsing JSON array members

Arguments, return value and availability:
  json: Required. The JSON array to be parsed.
  path: Required. The location of a child array to be parsed.
  COLUMNS (column_list): Required. The column definitions.
  AS alias: Required: The inline table name.
  rows: Return value. The output rows of parsed result.
  Available since MySQL 8.0.

COLUMNS (column_list) supports the following syntaxes:
  name FOR ORDINALITY: INTEGER column for row sequence starting from 1 
  name type PATH path [on_empty] [on_error]: Extracted value at a given location 
  name type EXISTS PATH path: 1 if value exists at a given location
  NESTED [PATH] path COLUMNS (column_list): Outer join with a child inline table

Related MySQL functions:

• JSON_EXTRACT() - Exacting Members from JSON
• JSON_VALUE() - Converting JSON Value

⇒ MySQL Functions for Encryption and Compression

⇐ JSON_VALID() - Validating JSON Value

⇑ MySQL Functions on JSON Values

⇑⇑ MySQL Function References