Uploaded image for project: 'Calcite'
  1. Calcite
  2. CALCITE-2867 JSON support
  3. CALCITE-3130

Implement JSON_UNQUOTE, JSON_QUOTE function

    XMLWordPrintableJSON

    Details

    • Type: Sub-task
    • Status: Open
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: None

      Description

      JSON_QUOTE({{string)}}

      Quotes a string as a JSON value by wrapping it with double quote characters and escaping interior quote and other characters, then returning the result as a utf8mb4}}string. Returns {{NULL if the argument is NULL.

      This function is typically used to produce a valid JSON string literal for inclusion within a JSON document.

      SQL:

      SELECT JSON_QUOTE('null'), JSON_QUOTE('"null"');
      

      Result:

      Heading 1 Heading 2
      "null" "\"null\""

      JSON_UNQUOTE({{json_val)}}

      Unquotes JSON value and returns the result as a utf8mb4 string. Returns NULL if the argument is NULL. An error occurs if the value starts and ends with double quotes but is not a valid JSON string literal.

      Within a string, certain sequences have special meaning unless the NO_BACKSLASH_ESCAPES SQL mode is enabled. Each of these sequences begins with a backslash (}}), known as the escape character. MySQL recognizes the escape sequences shown in Table 12.22, “JSON_UNQUOTE() Special Character Escape Sequences”. For all other escape sequences, backslash is ignored. That is, the escaped character is interpreted as if it was not escaped. For example, {{\x is just x. These sequences are case-sensitive. For example, \b is interpreted as a backspace, but \B is interpreted as B.

      SQL:

      SELECT JSON_UNQUOTE('"\\t\\u0032"');
      

      Result:

      Heading 1
      2

      Note:

      https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sql-mode-setting

       The JSON_UNQUOTE function needs to depend on the NO_BACKSLASH_ESCAPES setting of sql_mode.

       

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                x1q1j1 Forward Xu
                Reporter:
                x1q1j1 Forward Xu
              • Votes:
                0 Vote for this issue
                Watchers:
                2 Start watching this issue

                Dates

                • Created:
                  Updated:

                  Time Tracking

                  Estimated:
                  Original Estimate - Not Specified
                  Not Specified
                  Remaining:
                  Remaining Estimate - 0h
                  0h
                  Logged:
                  Time Spent - 1h 40m
                  1h 40m