mySql JSON string field returns encoded

Question

First week having to deal with a MYSQL database and JSON field types and I cannot seem to figure out why values are encoded automatically and then returned in encoded format.

Given the following SQL

-- create a multiline string with a tab example
SET @str ="Line One
Line 2  Tabbed out
Line 3";
-- encode it
SET @j = JSON_OBJECT("str", @str);

-- extract the value by name
SET @strOut = JSON_EXTRACT(@J, "$.str");

-- show the object and attribute value.
SELECT @j, @strOut;

You end up with what appears to be a full formed JSON object with a single attribute encoded.

@j = {"str": "Line One\n\tLine 2\tTabbed out\n\tLine 3"}

but using JSON_EXTRACT to get the attribute value I get the encoded version including outer quotes.

@strOut = "Line One\n\tLine 2\tTabbed out\n\tLine 3"

I would expect to get my original string with the \n \t all unescaped to the original values and no outer quotes. as such

Line One
Line 2  Tabbed out
Line 3

I can't seem to find any JSON_DECODE or JSON_UNESCAPE or similar functions.

I did find a JSON_ESCAPE() function but that appears to be used to manually build a JSON object structure in a string.

What am I missing to extract the values to the original format?

GMB · Accepted Answer · 2020-07-30 21:07:14Z

2

I like to use handy operator ->> for this.

It was introduced in MySQL 5.7.13, and basically combines JSON_EXTRACT() and JSON_UNQUOTE():

SET @strOut = @J ->> '$.str';

edited Jul 30, 2020 at 21:07

answered Jul 30, 2020 at 21:06

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Bill Karwin Over a year ago

Yep, that's equivalent to JSON_UNQUOTE(JSON_EXTRACT(@J, '$.str'))

GMB Over a year ago

@BillKarwin: yes, and it is much shorter to write...

DarrenMB Over a year ago

My SELECT Version() returns "8.0.17", it doesn't seem to work for me assigning to a variable like that, but it does work in a SELECT like this SELECT @j, @strOut; @j->>"$.str"

vanDee · Accepted Answer · 2020-07-30 20:57:00Z

1

You are looking for the JSON_UNQUOTE function

SET @strOut = JSON_UNQUOTE( JSON_EXTRACT(@J, "$.str") );

answered Jul 30, 2020 at 20:57

vanDee

263 bronze badges

1 Comment

DarrenMB Over a year ago

Thank you so much, browse documentation for 2 hours to not find that myself :) I'll accept later cause it won't accept so fast.

Bill Karwin · Accepted Answer · 2020-07-30 21:03:09Z

1

The result of JSON_EXTRACT() is intentionally a JSON document, not a string.

A JSON document may be:

An object enclosed in { }
An array enclosed in [ ]
A scalar string value enclosed in " "
A scalar number or boolean value
A null — but this is not an SQL NULL, it's a JSON null. This leads to confusing cases because you can extract a JSON field whose JSON value is null, and yet in an SQL expression, this fails IS NULL tests, and it also fails to be equal to an SQL string 'null'. Because it's a JSON type, not a scalar type.

answered Jul 30, 2020 at 21:03

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

1 Comment

DarrenMB Over a year ago

Thanks, this is useful as I can now see why it returned encoded. That would allow more complex documents to be broken up and manipulated via all the same JSON functions.

Collectives™ on Stack Overflow

mySql JSON string field returns encoded

3 Answers 3

3 Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related