JSON_ARRAYAGG(expression)

# 查询省份表中所有的省份名称的JSON数组
select JSON_ARRAYAGG(province_name ) from com_province;

["北京", "天津", "河北省", "山西省", "内蒙古自治区", "辽宁省", "吉林省", "黑龙江省", "上海", "江苏省", "浙江省", "安徽省", "福建省", "江西省", "山东省", "河南省", "湖北省", "湖南省", "广东省", "广西壮族自治区", "海南省", "重庆", "四川省", "贵州省", "云南省", "西藏自治区", "陕西省", "甘肃省", "青海省", "宁夏回族自治区", "新疆维吾尔自治区"]

JSON_OBJECT(key1, value1, key2, value2, ...)

# 使用JSON_OBJECT函数创建一个包含省份名称和省份编码的JSON对象
select JSON_OBJECT('省份名称',province_name,'省份编码',province_code ) from com_province;

SELECT
    JSON_ARRAYAGG(
        JSON_OBJECT(
            'code',
            T1.province_code,
            'name',
            T1.province_name,
            'children',
            (
            SELECT
                JSON_ARRAYAGG( JSON_OBJECT( 'code', T2.city_code, 'name', T2.city_name,'children',
                    (
            SELECT
                JSON_ARRAYAGG( JSON_OBJECT( 'code', T3.area_code, 'name', T3.area_name ) ) AS test3 
            FROM
                com_area T3 
            WHERE
                T3.city_code = T2.city_code ORDER BY T3.sort asc
            ) 
            ) ) AS test2 
            FROM
                com_city T2 
            WHERE
                T2.province_code = T1.province_code  ORDER BY T2.sort asc
            ) 
        ) 
    ) AS test 
FROM
    com_province T1   ORDER BY  T1.sort asc