All Products
Search

This Product

    MaxCompute:JSON_UNQUOTE

    Document Center

    MaxCompute:JSON_UNQUOTE

    Last Updated:Mar 26, 2026

    Returns the value of a JSON string as a plain SQL string, stripping surrounding double quotation marks and resolving escape sequences. Returns NULL if the input is NULL.

    Syntax

    STRING JSON_UNQUOTE(JSON <json>)

    Example

    -- Returns 123.
SELECT JSON_UNQUOTE(JSON "123");

    Parameters

    json: Required. The JSON data to unquote. The data type is JSON.

    The function recognizes the following escape sequences. All other escape sequences cause a JSON parsing error.

    Escape sequenceCharacter represented
    \"Double quotation mark "
    \bBackspace
    \fForm feed
    \nLine feed
    \rCarriage return
    \tTab
    \\Backslash \
    \uxxxxUTF-8 byte for the Unicode value XXXX

    Return value

    Returns the unquoted value as a STRING.

    • If json is NULL, the function returns NULL.

    • If json starts and ends with double quotation marks but is not a valid JSON string literal, an error is reported.

    Examples

    Strip quotes from a JSON_EXTRACT result

    Use JSON_EXTRACT to get a value from a JSON path, then use JSON_UNQUOTE to return it as a plain string.

    -- Returns New York.
SELECT JSON_UNQUOTE(JSON_EXTRACT(json '{"name": "John", "age": 25, "city": "New York"}','$.city'));

    The following example shows the difference between a raw JSON_EXTRACT result and an unquoted result:

    InputExpressionOutput
    {"city": "New York"}JSON_EXTRACT(...)"New York" (JSON string, with quotes)
    {"city": "New York"}JSON_UNQUOTE(JSON_EXTRACT(...))New York (plain string, no quotes)

    Unquote values with escape sequences

    JSON_UNQUOTE resolves escape sequences as it strips the surrounding quotes.

    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.