JSON_QUOTE() - Quoting JSON String

Q

How to convert a regular character string into a JSON (JavaScript Object Notation) string using the JSON_QUOTE() function?

✍: FYIcenter.com

A

JSON_QUOTE(str) is a MySQL built-in function that converts a regular character string into a JSON string by quoting the string with double quotes. It replaces the double-quote character (") as (\") within the string, and the escape character (\) as (\\). For example:

SELECT JSON_QUOTE('null'), JSON_QUOTE('"null"');
  -- +--------------------+----------------------+
  -- | JSON_QUOTE('null') | JSON_QUOTE('"null"') |
  -- +--------------------+----------------------+
  -- | "null"             | "\"null\""           |
  -- +--------------------+----------------------+

JSON uses the backslash "\" as the escape character. And "\" itself needs to be quoted. But "\" is also the escape character for MySQL string literals. So entering a "\" into a JSON string in MySQL statement becomes very tricky as shown below:

SELECT JSON_QUOTE('C:\Windows'), JSON_QUOTE('C:\\Windows');
  -- +--------------------------+---------------------------+
  -- | JSON_QUOTE('C:\Windows') | JSON_QUOTE('C:\\Windows') |
  -- +--------------------------+---------------------------+
  -- | "C:Windows"              | "C:\\Windows"             |
  -- +--------------------------+---------------------------+

SELECT JSON_QUOTE('Next\nLine'), JSON_QUOTE('Next\\nLine');;
  -- +--------------------------+---------------------------+
  -- | JSON_QUOTE('Next\nLine') | JSON_QUOTE('Next\\nLine') |
  -- +--------------------------+---------------------------+
  -- | "Next\nLine"             | "Next\\nLine"             |
  -- +--------------------------+---------------------------+

Reference information of the JSON_QUOTE() function:

JSON_QUOTE(str): json
  Returns a JSON quoted string converted from a given MySQL character string.

Arguments, return value and availability:
  str: Required. The character string be quoted.
  json: Return value. The quoted JSON string value.
  Available since MySQL 5.7.

 

JSON_REMOVE() - Removing JSON Child Members

JSON_PRETTY() - Validating JSON Value

MySQL Functions on JSON Values

⇑⇑ MySQL Function References

2023-12-10, 345🔥, 0💬