如何通过单条MySQL查询合并JSON列中的所有JSON类型值

我来帮你梳理下这个问题的解决思路，你遇到的核心问题是没区分不同JSON类型的处理逻辑，加上之前的GROUP_CONCAT拼接方式导致了JSON格式错误，下面一步步拆解解决：

一、先修复对象合并的报错问题 #

你之前给对象加了单引号包裹，导致json_merge_preserve识别到无效JSON。正确的做法是直接拼接对象的内部内容（去掉首尾的{}），再用合并函数处理：

-- 临时调整GROUP_CONCAT最大长度，避免长JSON被截断
SET @original_group_concat_len = @@group_concat_max_len;
SET @@group_concat_max_len = 4294967295;

-- 合并所有OBJECT类型的值为一个单一对象（和你结果1里的合并逻辑一致）
SELECT JSON_MERGE_PRESERVE(
    '{}',
    GROUP_CONCAT(
        CASE WHEN JSON_TYPE(arbitraryjsonvalues) = 'OBJECT'
             THEN SUBSTRING(arbitraryjsonvalues, 2, LENGTH(arbitraryjsonvalues) - 2)
             ELSE ''
        END SEPARATOR ','
    )
) INTO @merged_object
FROM ITEMS
WHERE JSON_TYPE(arbitraryjsonvalues) = 'OBJECT';

-- 恢复原GROUP_CONCAT长度
SET @@group_concat_max_len = @original_group_concat_len;

json_merge_preserve会自动帮你合并相同键的数组（比如yeah会变成数组）、合并对象的子属性（比如foo.big里的键会合并），完全符合结果1的对象要求。

二、处理标量和数组值 #

接下来要把标量直接加入数组，数组需要展开元素（而不是把整个数组作为单个元素）：

1. 收集所有标量值（字符串、数字、布尔、null） #

SELECT JSON_ARRAYAGG(jt.value) INTO @scalar_elements
FROM ITEMS,
     JSON_TABLE(
         arbitraryjsonvalues,
         '$' COLUMNS(value JSON PATH '$')
     ) jt
WHERE JSON_TYPE(arbitraryjsonvalues) IN ('STRING', 'NUMBER', 'BOOLEAN', 'NULL');

2. 展开并收集所有数组元素 #

SELECT JSON_ARRAYAGG(jt.element) INTO @array_elements
FROM ITEMS,
     JSON_TABLE(
         arbitraryjsonvalues,
         '$[*]' COLUMNS(element JSON PATH '$')
     ) jt
WHERE JSON_TYPE(arbitraryjsonvalues) = 'ARRAY';

三、合并所有部分得到结果1 #

把标量元素、数组元素、合并后的对象组合成一个数组：

SELECT JSON_MERGE_PRESERVE(
    COALESCE(@scalar_elements, '[]'),
    COALESCE(@array_elements, '[]'),
    JSON_ARRAY(COALESCE(@merged_object, '{}'))
) AS arbitraryjsonvaluesmerged;

这个查询会生成你想要的结果1：标量和数组元素平铺，合并后的对象作为最后一个元素。

四、生成结果2（空结构数组） #

如果需要把所有值替换为对应结构的空数组，我们可以用JSON_TRANSFORM（MySQL 8.0.14+支持）修改合并后的对象，再组合空元素：

-- 把合并后的对象所有值替换为空数组
SET @empty_struct_object = JSON_TRANSFORM(
    @merged_object,
    '$.foo.lover = []',
    '$.foo.boo = []',
    '$.foo.big.cylinder = []',
    '$.foo.big.cone = []',
    '$.foo.big.cat = []',
    '$.foo.big.dog = []',
    '$.foo.small = []',
    '$.goo = []',
    '$.yeah = []'
);

-- 组合成结果2的数组
SELECT JSON_ARRAY(
    '[]', -- 对应标量和数组的空数组占位
    @empty_struct_object
) AS arbitraryjsonvaluesmerged_empty;

关键注意点 #

1. JSON格式合法性：拼接JSON内容时不要加多余引号，json_merge_preserve需要纯JSON字符串，不是带引号的字符串。
2. 区分JSON类型：必须用JSON_TYPE()判断值的类型，分别处理标量、数组、对象，否则会出现格式错误。
3. GROUP_CONCAT长度限制：一定要临时调整group_concat_max_len，避免长JSON被截断导致格式错误。

内容的提问来源于stack exchange，提问作者amachree tamunoemi