MySQL JSON_INSERT() of an Array into Object creates Escape characters

Question

MySQL v8.

The line of code below:

SELECT JSON_INSERT(@varJSON, '$.recordings', @recordedJSON) INTO @varJSON;

Will insert a JSON array into a JSON object. However, it is also introducing escape characters when doing so.

/*Outputs*/

/* @varJSON before - Correct format. */
{"routeName": "INDIA", "routeNotes": "This is an area that must be reviewed and remarked out."}
 
/* @recordedJSON - Correct format.*/
[{"sessionID": "7811a89a-58b8-11ec-8172-42010ac00007", "recordedGeom": {"type": "LineString", "coordinates": [[-35.1235, 139.1234], [-36.1235, 139.1234], [1.234, 5.5456], [1.234, 149.1234]]}, "recordingEnd": null, "applianceName": "The Basin - Pumper", "recordingStart": "2021-12-10 01:58:17.000000", "applianceBrigade": "The Basin"}, {"sessionID": "another_test_test_test_", "recordedGeom": {"type": "LineString", "coordinates": [[-34.920281, 138.606703], [-34.922584, 138.607913]]}, "recordingEnd": null, "applianceName": "The Basin - Pumper", "recordingStart": "2021-12-14 21:50:37.000000", "applianceBrigade": "The Basin"}]

SELECT JSON_INSERT(@varJSON, '$.recordings', @recordedJSON) INTO @varJSON;
/*Note - I receive the same output format in JSON_SET*/


/*MySQL then inputs escape slashes in when I am using the code above.*/

{"routeName": "INDIA", "recordings": "[{\"sessionID\": \"7811a89a-58b8-11ec-8172-42010ac00007\", \"recordedGeom\": {\"type\": \"LineString\", \"coordinates\": [[-35.1235, 139.1234], [-36.1235, 139.1234], [1.234, 5.5456], [1.234, 149.1234]]}, \"recordingEnd\": null, \"applianceName\": \"The Basin - Pumper\", \"recordingStart\": \"2021-12-10 01:58:17.000000\", \"applianceBrigade\": \"The Basin\"}, {\"sessionID\": \"another_test_test_test_\", \"recordedGeom\": {\"type\": \"LineString\", \"coordinates\": [[-34.920281, 138.606703], [-34.922584, 138.607913]]}, \"recordingEnd\": null, \"applianceName\": \"The Basin - Pumper\", \"recordingStart\": \"2021-12-14 21:50:37.000000\", \"applianceBrigade\": \"The Basin\"}]", "routeNotes": "This is an area that must be reviewed and remarked out."}

I have read through this documentation several times and I cannot see what/where I am going wrong:

https://dev.mysql.com/doc/refman/5.7/en/json-modification-functions.html#function_json-set https://dev.mysql.com/doc/refman/8.0/en/json.html#json-normalization

MrMadium · Accepted Answer · 2021-12-14 23:46:30Z

Further to @nbk , I just found casting the other variable as JSON also worked.

SELECT JSON_SET(@varJSON, '$.recordings', CAST(@recordedJSON AS JSON));
{"routeName": "INDIA", "recordings": [{"sessionID": "7811a89a-58b8-11ec-8172-42010ac00007", "recordedGeom": {"type": "LineString", "coordinates": [[-35.1235, 139.1234], [-36.1235, 139.1234], [1.234, 5.5456], [1.234, 149.1234]]}, "recordingEnd": null, "applianceName": "The Basin - Pumper", "recordingStart": "2021-12-10 01:58:17.000000", "applianceBrigade": "The Basin"}, {"sessionID": "another_test_test_test_", "recordedGeom": {"type": "LineString", "coordinates": [[-34.920281, 138.606703], [-34.922584, 138.607913]]}, "recordingEnd": null, "applianceName": "The Basin - Pumper", "recordingStart": "2021-12-14 21:50:37.000000", "applianceBrigade": "The Basin"}], "routeNotes": "This is an area that must be reviewed and remarked out."}

I don't know if one is more performant than the other though - this might help someone in the future with a similar yet different problem.

nbk · Accepted Answer · 2021-12-14 23:37:25Z

0

Use JSON_UNQUOTE see manual

/*Outputs*/

/* @varJSON before - Correct format. */
SET @varJSON :=  '{"routeName": "INDIA", "routeNotes": "This is an area that must be reviewed and remarked out."}';
 
/* @recordedJSON - Correct format.*/
SET @recordedJSON := '[{"sessionID": "7811a89a-58b8-11ec-8172-42010ac00007", "recordedGeom": {"type": "LineString", "coordinates": [[-35.1235, 139.1234], [-36.1235, 139.1234], [1.234, 5.5456], [1.234, 149.1234]]}, "recordingEnd": null, "applianceName": "The Basin - Pumper", "recordingStart": "2021-12-10 01:58:17.000000", "applianceBrigade": "The Basin"}, {"sessionID": "another_test_test_test_", "recordedGeom": {"type": "LineString", "coordinates": [[-34.920281, 138.606703], [-34.922584, 138.607913]]}, "recordingEnd": null, "applianceName": "The Basin - Pumper", "recordingStart": "2021-12-14 21:50:37.000000", "applianceBrigade": "The Basin"}]';

SELECT JSON_INSERT(JSON_UNQUOTE(@varJSON), '$.recordings', JSON_UNQUOTE()@recordedJSON)) INTO @varJSON;
/*Note - I receive the same output format in JSON_SET*/

SELECT @varJSON;
/*Note - I receive the same output format in JSON_SET*/

| @varJSON                                                                                        |
| :---------------------------------------------------------------------------------------------- |
| {"routeName": "INDIA", "routeNotes": "This is an area that must be reviewed and remarked out."} |

✓

db<>fiddle here

answered Dec 14, 2021 at 23:37

nbk

49.6k8 gold badges32 silver badges53 bronze badges

3 Comments

MrMadium Over a year ago

I just came back here to suggest a different answer to myself, where I cast the type AS JSON: SELECT JSON_SET(@varJSON, '$.recordings', CAST(@recordedJSON AS JSON)); Returns the preferred format as well. Is there a difference between casting the type and your suggestion of JSON_UNQUOTE?

nbk Over a year ago

CAST is usually slower

MrMadium Over a year ago

Perfect, thanks nbk!

Collectives™ on Stack Overflow

MySQL JSON_INSERT() of an Array into Object creates Escape characters

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related