[CALCITE-3130] Implement JSON_UNQUOTE, JSON_QUOTE function

• Type: Sub-task
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Labels: pull-request-available
• Assignee: Forward Xu
• Reporter: Forward Xu
• Created: 15/Jun/19 08:31
• Updated: 24/Jul/19 01:58

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.

 

Issue Links

• links to: GitHub Pull Request #1272