Oracle中如何将多行JSON_OBJECT结果合并为单个JSON结构

问题 #

我通过以下SQL语句从表中提取数据并构建JSON：

SELECT JSON_OBJECT(KEY 'data' VALUE
                   JSON_ARRAYAGG(JSON_object(KEY 'code'     VALUE tt.code,
                                             KEY 'name'     VALUE tt.name,
                                             KEY 'location' VALUE
                                             tt.location NULL ON NULL)
                                 RETURNING CLOB),
                   KEY 'header' VALUE
                   JSON_OBJECT(KEY 'vr_code' VALUE 'team1',
                               KEY 'date'    VALUE
                               TO_DATE('2022-12-31', 'YYYY-MM-DD') RETURNING CLOB)
                   RETURNING CLOB) l_json_data
  FROM thetable tt

当前返回的结果是多个独立的JSON行，每行包含一个data数组与相同的header信息，结构如下：

{
  "data": [
    {
      "code": "60",
      "name": "michael",
      "location": "canada"
    },
    {
      "code": "60",
      "name": "ken",
      "location": "united states"
    }
  ],
  "header": {
    "vr_code": "team1",
    "date": "2022-12-31T00:00:00"
  }
};
{
  "data": [
    {
      "code": "70",
      "name": "jim",
      "location": "united states"
    },
    {
      "code": "70",
      "name": "leslie",
      "location": "mexico"
    }
  ],
  "header": {
    "vr_code": "team1",
    "date": "2022-12-31T00:00:00"
  }
}

我需要将这些行合并，使所有data元素整合到一个data数组中，形成如下目标JSON结构：

{
  "data": [
    {
      "code": "60",
      "name": "michael",
      "location": "canada"
    },
    {
      "code": "60",
      "name": "ken",
      "location": "united states"
    },
    {
      "code": "70",
      "name": "jim",
      "location": "united states"
    },
    {
      "code": "70",
      "name": "leslie",
      "location": "mexico"
    }
  ],
  "header": {
    "vr_code": "team1",
    "date": "2022-12-31T00:00:00"
  }
}

请问该如何实现？

解决方案 #

你当前返回多行JSON的原因是SQL中隐含了分组逻辑（比如实际执行时添加了GROUP BY子句），导致数据按分组生成独立的JSON对象。要合并所有data元素，只需直接对全表数据做聚合，不需要分组：

SELECT JSON_OBJECT(
         KEY 'data' VALUE JSON_ARRAYAGG(
                           JSON_OBJECT(
                             KEY 'code' VALUE tt.code,
                             KEY 'name' VALUE tt.name,
                             KEY 'location' VALUE tt.location NULL ON NULL
                           ) RETURNING CLOB
                         ),
         KEY 'header' VALUE JSON_OBJECT(
                             KEY 'vr_code' VALUE 'team1',
                             KEY 'date' VALUE TO_DATE('2022-12-31', 'YYYY-MM-DD') RETURNING CLOB
                           ) RETURNING CLOB
       ) l_json_data
FROM thetable tt;

这段SQL会将thetable中的所有行转换为JSON对象，统一聚合到一个data数组中，再搭配固定的header信息，生成你需要的单一行JSON结果。

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