mysql json unquote_JSON_UNQUOTE() – Remove Quotes from a JSON Document in MySQL

InMySQL, the JSON_UNQUOTE()

function “unquotes” a JSON document and returns the result as a utf8mb4

string.

You provide the JSON document as an argument, and the function will do the rest.

Syntax

The syntax goes like this:

JSON_UNQUOTE(json_val)

Where json_val

is the JSON document you want unquoted.

Example

Here’s an example to demonstrate.

SET @data = '"Homer Simpson"';

SELECT

@data Original,

JSON_UNQUOTE(@data) Unquoted;

Result:

+-----------------+---------------+

| Original | Unquoted |

+-----------------+---------------+

| "Homer Simpson" | Homer Simpson |

+-----------------+---------------+

Escape Characters

The way this function handles escape characters depends on whether NO_BACKSLASH_ESCAPES

is enabled or disabled.

When NO_BACKSLASH_ESCAPES is Disabled

The NO_BACKSLASH_ESCAPES

mode is disabled by default in MySQL 8.0.

Here’s what happens if we include t

to specify a tab character within a string when NO_BACKSLASH_ESCAPES

is disabled, and without

using JSON_UNQUOTE()

.

SET @data = '"Homer t Simpson"';

SELECT

@data Original;

Result:

+-------------------+

| Original |

+-------------------+

| "Homer Simpson" |

+-------------------+

And here’s what happens if we use JSON_UNQUOTE()

:

SET @data = '"Homer t Simpson"';

SELECT JSON_UNQUOTE(@data) Unquoted;

Result:

ERROR 3141 (22032): Invalid JSON text in argument 1 to function json_unquote: "Invalid escape character in string." at position 7.

We can overcome this by using two backslashes ( \t

). However, keep in mind that doing this will change the outcome when not

using JSON_UNQUOTE()

.

SET @data = '"Homer \t Simpson"';

SELECT

@data Original,

JSON_UNQUOTE(@data) Unquoted;

Result:

+--------------------+-----------------+

| Original | Unquoted |

+--------------------+-----------------+

| "Homer t Simpson" | Homer Simpson |

+--------------------+-----------------+

When NO_BACKSLASH_ESCAPES is Enabled

Here’s what happens when we enable NO_BACKSLASH_ESCAPES

before running the previous statement:

SET @@sql_mode = 'NO_BACKSLASH_ESCAPES';

SET @data = '"Homer \t Simpson"';

SELECT

@data Original,

JSON_UNQUOTE(@data) Unquoted;

Result:

+---------------------+------------------+

| Original | Unquoted |

+---------------------+------------------+

| "Homer \t Simpson" | Homer t Simpson |

+---------------------+------------------+

And here’s what happens if we remove the first backslash:

SET @@sql_mode = 'NO_BACKSLASH_ESCAPES';

SET @data = '"Homer t Simpson"';

SELECT

@data Original,

JSON_UNQUOTE(@data) Unquoted;

Result:

+--------------------+-----------------+

| Original | Unquoted |

+--------------------+-----------------+

| "Homer t Simpson" | Homer Simpson |

+--------------------+-----------------+

注意：本文来自Database.Guide。本站无法对本文内容的真实性、完整性、及时性、原创性提供任何保证，请您自行验证核实并承担相关的风险与后果！

CoLaBug.com遵循[CC BY-SA 4.0]分享并保持客观立场，本站不承担此类作品侵权行为的直接责任及连带责任。您有版权、意见、投诉等问题，请通过[eMail]联系我们处理，如需商业授权请联系原作者/原网站。