MySQL中JSON_ARRAY_INSERT插入JSON时多余引号与转义符问题排查

原执行脚本 #

SET @j1 = '[{"Documents": []}]';
SET @j2 = '{"rc":{"DisplayName":"Registration Certificate","Extension":"png"}}';
SET @path = '$[0].Documents[0]';
SELECT REPLACE(JSON_ARRAY_INSERT(@j1, @path, @j2), '\\', '');

不同执行结果 #

• 带REPLACE函数的输出：

[{"Documents": ["{"rc":{"DisplayName":"Registration Certificate","Extension":"png"}}"]}]

• 不带REPLACE函数的输出：

[{"Documents": ["{"rc":{"DisplayName":"Registration Certificate","Extension":"png"}}"]}]

• 期望输出：

[{"Documents": [{"rc":{"DisplayName":"Registration Certificate","Extension":"png"}}]}]

问题分析与解决 #

问题根源 #

你传入JSON_ARRAY_INSERT的@j2是字符串类型，而非JSON对象。MySQL会将字符串参数自动转义后作为JSON字符串值插入，因此会给@j2内的双引号添加反斜杠，同时用双引号包裹整个@j2内容，导致它成为数组中的字符串元素，而非嵌套的JSON对象。

解决方法 #

需要将@j2转换为JSON类型后再插入，让MySQL识别为JSON对象而非字符串，有两种常用方式：

方式1：用CAST将字符串转为JSON

修改后的脚本：

SET @j1 = '[{"Documents": []}]';
SET @j2 = CAST('{"rc":{"DisplayName":"Registration Certificate","Extension":"png"}}' AS JSON);
SET @path = '$[0].Documents[0]';
SELECT JSON_ARRAY_INSERT(@j1, @path, @j2);

执行后直接得到期望输出：

[{"Documents": [{"rc":{"DisplayName":"Registration Certificate","Extension":"png"}}]}]

方式2：用JSON_OBJECT构造JSON对象

直接通过MySQL的JSON构造函数生成JSON对象，从根源避免字符串转义问题：

SET @j1 = '[{"Documents": []}]';
SET @j2 = JSON_OBJECT(
    'rc', JSON_OBJECT(
        'DisplayName', 'Registration Certificate',
        'Extension', 'png'
    )
);
SET @path = '$[0].Documents[0]';
SELECT JSON_ARRAY_INSERT(@j1, @path, @j2);

内容的提问来源于stack exchange，提问作者Kumar Kush