需要创建一个 Json_Object，其中可以包含多个嵌套的 Json_objects、Json_arrays & Json_arrayaggs。

我用一些虚拟数据创建了这个表来演示这个问题：

 create table test_tbl(
test_col1 varchar2(20),test_col2 varchar2(20),test_col3 varchar2(20),test_col4 varchar2(20),test_col5 varchar2(20),test_col6 varchar2(20)

);

insert into test_tbl values('val0','val1','val2','val7','val11','val12');
insert into test_tbl values('val0','val3','val4','val5','val6','val13','val14');
insert into test_tbl values('val0','val8','val12');
insert into test_tbl values('val1','val9','val10','val14');

使用以下查询创建 Json_object 时：

  SELECT JSON_OBJECT (
         'output' VALUE JSON_ARRAYAGG(
           JSON_OBJECT(
             'common' VALUE test_col1,'list'   VALUE JSON_ARRAYAGG(
               JSON_OBJECT(
                 'key1' VALUE test_col2,'key2' VALUE test_col3
               )
             ),'anotherlist' VALUE JSON_ARRAYAGG(
               JSON_OBJECT(
                 'key1' VALUE test_col5,'key2' VALUE test_col6
               )
             )
           )
         )
       )
FROM   (
  SELECT DISTINCT
         test_col1,test_col2,test_col3,test_col5,test_col6
  FROM   test_tbl
  WHERE  test_col4 = 'val7'
)
GROUP BY
       test_col1

这会导致以下json在聚合数组中具有重复的键值对-

{
  "output": [
    {
      "common": "val0","list": [
        {
          "key1": "val5","key2": "val6"
        },{
          "key1": "val3","key2": "val4"
        },{
          "key1": "val1","key2": "val2"
        },{
          "key1": "val5","key2": "val6"
        }
      ],"anotherlist": [
        {
          "key1": "val13","key2": "val14"
        },{
          "key1": "val11","key2": "val12"
        },"key2": "val12"
        }
      ]
    },{
      "common": "val1","list": [
        {
          "key1": "val9","key2": "val10"
        },{
          "key1": "val9","key2": "val10"
        }
      ],"anotherlist": [
        {
          "key1": "val11",{
          "key1": "val13","key2": "val14"
        }
      ]
    }
  ]
}

而我预期的 Json 是：

{
  "output": [
    {
      "common": "val0","key2": "val2"
        }
      ],"key2": "val14"
        }
      ]
    }
  ]
}

预先感谢您提供有关如何获得上述预期 Json 的任何建议。