Skip to main content
Documents
Share
LinkedIn
Facebook
Twitter
Copy
ClausesStatementsKeywordsExpressionsFunctionsIndexesPerformance Optimization

MySQL JSON_UNQUOTE() Function

The `JSON_UNQUOTE()` function in MySQL is used to remove the outermost quotes from a JSON string literal. It is particularly useful for extracting and displaying JSON string values without the surrounding quotes.

Usage

The `JSON_UNQUOTE()` function is used when you need to retrieve a JSON string value in its plain form, without the enclosing double quotes. It is often applied when processing JSON data stored in MySQL tables.

JSON_UNQUOTE(json_val)

In this syntax, `json_val` represents the JSON string value from which you want to remove the quotes.

Examples

1. Basic Unquoting of a JSON String

SELECT JSON_UNQUOTE('"Hello World"') AS result;

This example removes the quotes from the JSON string literal `"Hello World"`, resulting in the plain string `Hello World`.

2. Unquoting a JSON Value from a Table

SELECT JSON_UNQUOTE(json_column->'$.name') AS name
FROM my_table;

In this example, the `JSON_UNQUOTE()` function is used to extract and unquote the `name` field from a JSON object stored in the `json_column` of `my_table`. The arrow operator (`->`) is used to access a JSON path expression.

3. Handling Nested JSON Data

SELECT JSON_UNQUOTE(json_column->'$.address.city') AS city
FROM my_table
WHERE JSON_UNQUOTE(json_column->'$.address.city') = 'New York';

Here, `JSON_UNQUOTE()` is applied to a nested JSON field to compare and retrieve unquoted city names, specifically filtering for `New York`.

Tips and Best Practices

  • Ensure valid JSON input. Pass valid JSON strings to `JSON_UNQUOTE()` to avoid unexpected results or errors.
  • Non-string JSON values. When the input is not a valid JSON string (e.g., a number or boolean), `JSON_UNQUOTE()` returns the value as is without raising an error.
  • Error Handling. If the input is malformed or not a valid JSON string, `JSON_UNQUOTE()` may return `NULL` or raise an error, depending on the MySQL version.
  • Use with JSON functions. Combine `JSON_UNQUOTE()` with other JSON functions like `JSON_EXTRACT()` for more complex JSON handling.
  • Check for NULL values. Be prepared to handle `NULL` results if the input JSON path does not exist or the input is `NULL`.
  • Optimize for readability. Use aliases to clarify the purpose of unquoted values in your queries.
  • Compatibility. Be aware of potential differences in behavior across MySQL versions and consult the documentation for version-specific details.
  • JSON_EXTRACT(): Use this function to extract data from a JSON document.
  • JSON_SET(): Modify a JSON document by setting new values.