JSON_ARRAY_APPEND adding escape slash
I am using MySQL 5.7.
The code will eventually be run in a stored procedure, but I am running in Query editor for debugging.
The following code is inserting \ in as an escape character. The basis of the code is from another forum, but I can't see why it won't work.
-- SET @i = NULL;
SET @i = JSON_ARRAY(JSON_OBJECT('a', '1', 'b', '2'));
SET @j = JSON_OBJECT('c', '3', 'd', '3');
-- SET @j = CAST('{"a": "1"}' AS JSON);
SET @a = IF( @i IS NULL OR JSON_TYPE(@i) != 'ARRAY',
JSON_ARRAY_APPEND(JSON_ARRAY(), '$', @j),
JSON_ARRAY_APPEND(@i, '$', @j)
);
SELECT @j, @a
Results
@j @a
{"c": "3", "d": "3"} [{"a": "1", "b": "2"}, "{\\"c\\": \\"3\\", \\"d\\": \\"3\\"}"]
Note that in my query editor it is only one slash at a time.
I have tried JSON_UNQUOTE and a great number of different arrangements, but I cna't get rid of the escape character. I have read one post suggesting using the REPLACE function, but it seems unlikely to me the developers would have required that.