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

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

CodePudding user response：

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

CodePudding user response：

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.