在MySQL/MariaDB中从JSON数组生成JSON对象

针对MySQL 8及支持JSON_TABLE的MariaDB版本（如10.5+），可以通过JSON_TABLE拆分数组 + JSON_OBJECTAGG聚合键值对的方式实现需求，具体步骤如下：

核心思路 #

1. 用JSON_TABLE将两个JSON数组分别拆分为带索引的行数据，索引对应数组元素的位置；
2. 通过索引关联两个拆分后的数据集，确保键和值的位置匹配；
3. 使用JSON_OBJECTAGG将匹配后的键值对聚合为目标JSON对象。

示例SQL（假设表名为test_table，列名为col_a、col_b） #

SELECT JSON_OBJECTAGG(k.key_item, v.val_item) AS result_json
FROM test_table
-- 拆分Col A为索引+键值
JOIN JSON_TABLE(
    col_a,
    '$[*]' COLUMNS (
        item_idx FOR ORDINALITY, -- 获取数组元素的位置索引（从1开始）
        key_item VARCHAR(255) PATH '$'
    )
) k
-- 拆分Col B为索引+数值
JOIN JSON_TABLE(
    col_b,
    '$[*]' COLUMNS (
        item_idx FOR ORDINALITY,
        val_item INT PATH '$'
    )
) v ON k.item_idx = v.item_idx;

注意事项 #

• 需保证col_a和col_b的数组长度一致，否则会出现键值不匹配或数据遗漏的情况；
• 如果数组中包含特殊字符或非字符串类型的键，需调整key_item的数据类型适配实际场景。

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