JSON_ARRAY_APPEND adding escape slash
Posted by: Derek McKinnon
Date: August 21, 2022 04:40AM

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(@i, '$', @j)
SELECT @j, @a

@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.

Options: ReplyQuote

Written By
JSON_ARRAY_APPEND adding escape slash
August 21, 2022 04:40AM

Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.