Description:
Passing a JSON_OBJECT() result as a value arg in a variable to JSON_ARRAY_APPEND() adds backslashes, but passing it directly does not. Easy to conform that this so both for user vars and for declared vars in stored routines.
How to repeat:
SET @i = JSON_ARRAY(JSON_OBJECT('a', '1', 'b', '2'));
SET @j = JSON_OBJECT('c', '3', 'd', '3');
SET @a = IF( @i IS NULL OR JSON_TYPE(@i) != 'ARRAY',
JSON_ARRAY_APPEND( JSON_ARRAY(), '$', @j ),
JSON_ARRAY_APPEND( @i, '$', @j )
);
SET @b = IF( @i IS NULL OR JSON_TYPE(@i) != 'ARRAY',
JSON_ARRAY_APPEND( JSON_ARRAY(), '$', @j ),
JSON_ARRAY_APPEND( @i, '$', JSON_OBJECT('c', '3', 'd', '3'))
);
SELECT @j, @a, @b\G
*************************** 1. row ***************************
@j: {"c": "3", "d": "3"}
@a: [{"a": "1", "b": "2"}, "{\"c\": \"3\", \"d\": \"3\"}"]
@b: [{"a": "1", "b": "2"}, {"c": "3", "d": "3"}]
Suggested fix:
Workaround is to always use the text within a JSON object rather than the JSON Object itself.
See discussion at https://forums.mysql.com/read.php?176,705914,705914#msg-705914
Description: Passing a JSON_OBJECT() result as a value arg in a variable to JSON_ARRAY_APPEND() adds backslashes, but passing it directly does not. Easy to conform that this so both for user vars and for declared vars in stored routines. How to repeat: SET @i = JSON_ARRAY(JSON_OBJECT('a', '1', 'b', '2')); SET @j = JSON_OBJECT('c', '3', 'd', '3'); SET @a = IF( @i IS NULL OR JSON_TYPE(@i) != 'ARRAY', JSON_ARRAY_APPEND( JSON_ARRAY(), '$', @j ), JSON_ARRAY_APPEND( @i, '$', @j ) ); SET @b = IF( @i IS NULL OR JSON_TYPE(@i) != 'ARRAY', JSON_ARRAY_APPEND( JSON_ARRAY(), '$', @j ), JSON_ARRAY_APPEND( @i, '$', JSON_OBJECT('c', '3', 'd', '3')) ); SELECT @j, @a, @b\G *************************** 1. row *************************** @j: {"c": "3", "d": "3"} @a: [{"a": "1", "b": "2"}, "{\"c\": \"3\", \"d\": \"3\"}"] @b: [{"a": "1", "b": "2"}, {"c": "3", "d": "3"}] Suggested fix: Workaround is to always use the text within a JSON object rather than the JSON Object itself. See discussion at https://forums.mysql.com/read.php?176,705914,705914#msg-705914