IBM DB2 11.1中JSON_ARRAYAGG使用报错，求实现嵌套JSON输出

报错原因 #

DB2 11.1版本的JSON_ARRAYAGG函数不支持在函数内部直接使用ORDER BY子句，这是触发SQLCODE=-104语法错误的核心原因。该版本的JSON_ARRAYAGG仅接受聚合表达式作为参数，无法直接追加排序逻辑。此外原SQL还存在其他语法问题：

• 错误用VALUES和JSON_ARRAY嵌套包裹查询结果，造成结构冗余
• FORMAT JSON的位置不符合语法规范
• JSON键名大小写与预期输出不匹配（原SQL用大写，预期为小写）

修正后的SQL语句 #

基础版本（生成预期JSON结构） #

SELECT JSON_OBJECT(
         'id' VALUE c.ID,
         'name' VALUE c.NAME,
         'addresses' VALUE JSON_ARRAYAGG(
           JSON_OBJECT(
             'address' VALUE ca.ADDRESS,
             'city' VALUE ca.CITY
           )
         )
       ) AS customer_json
FROM CUSTOMER c
JOIN CUSTOMER_ADDRESS ca ON ca.ADDRESS_CUSTOMER_ID = c.ID
GROUP BY c.ID, c.NAME
ORDER BY c.ID;

带地址排序的版本 #

由于DB2 11.1不支持JSON_ARRAYAGG直接追加ORDER BY，可通过子查询预先排序关联表数据，实现地址数组按ADDRESS排序：

SELECT JSON_OBJECT(
         'id' VALUE c.ID,
         'name' VALUE c.NAME,
         'addresses' VALUE JSON_ARRAYAGG(
           JSON_OBJECT(
             'address' VALUE ca.ADDRESS,
             'city' VALUE ca.CITY
           )
         )
       ) AS customer_json
FROM CUSTOMER c
JOIN (
       SELECT ADDRESS_CUSTOMER_ID, ADDRESS, CITY
       FROM CUSTOMER_ADDRESS
       ORDER BY ADDRESS_CUSTOMER_ID, ADDRESS
     ) ca ON ca.ADDRESS_CUSTOMER_ID = c.ID
GROUP BY c.ID, c.NAME
ORDER BY c.ID;

关键说明 #

• 排序逻辑：通过子查询预先对CUSTOMER_ADDRESS按客户ID分组、地址字段排序，确保聚合后的数组元素顺序符合要求
• 格式匹配：将JSON键名调整为小写（id、name、addresses），与预期输出完全一致
• 语法规范：移除冗余的VALUES和JSON_ARRAY包裹，直接查询生成目标JSON对象，GROUP BY逻辑符合DB2语法要求

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