The JSON_UNQUOTE function removes quotation marks from JSON data.
Syntax
STRING JSON_UNQUOTE(JSON <json>)
-- Standard example.
-- Returns 123.
SELECT JSON_UNQUOTE(JSON "123");Parameters
json: Required. The JSON data to unquote. The data type of this parameter is JSON.
Note: The function recognizes the following escape sequences in the json data. All other escape sequences cause a JSON parsing error.
Escape sequence | Character represented by the sequence |
" | Double quotation mark " |
\b | Backspace character |
\f | Form feed |
\n | Line feed |
\r | Carriage return |
\t | Tab character |
\ | Backslash \ |
\uxxxx | UTF-8 byte for the Unicode value XXXX |
Return value
Returns the unquoted JSON data as a STRING. The following rules apply:
If the value of json is NULL, the function returns NULL.
If the json data starts and ends with double quotation marks but is not a valid JSON string literal, an error is reported.
Examples
Example 1: Use the JSON_EXTRACT function to fetch data from a specified JSON path, and then use the JSON_UNQUOTE function to remove the double quotation marks from the result.
-- Returns New York. SELECT JSON_UNQUOTE(JSON_EXTRACT(json '{"name": "John", "age": 25, "city": "New York"}','$.city'));Example 2: Unquote a JSON string that contains escape sequences.
SELECT JSON_UNQUOTE(json "123"); +-----+ | _c0 | +-----+ | 123 | +-----+ SELECT JSON_UNQUOTE(JSON '"te\\rst"'); -- +-----+ | _c0 | +-----+ | te st | +-----+ SELECT JSON_UNQUOTE(JSON '"\\ttest"'); -- +-----+ | _c0 | +-----+ | test | +-----+ SELECT JSON_UNQUOTE(JSON '"te\\nst"'); -- +-----+ | _c0 | +-----+ | te st | +-----+ SELECT JSON_UNQUOTE(JSON '"te\\bst"'); -- +-----+ | _c0 | +-----+ | tst | +-----+ SELECT JSON_UNQUOTE(JSON '"\\u03A9"'); +-----+ | _c0 | +-----+ | Ω | +-----+
Related functions
JSON_UNQUOTE is a JSON function. For more information about JSON functions, see JSON functions.