Clauses Statements Keywords Expressions Functions Indexes Performance 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)Được hỗ trợ bởi

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;Được hỗ trợ bởi

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;Được hỗ trợ bởi

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';Được hỗ trợ bởi

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.