How to Aggregate distinct values from multiple lists using Oracle JSON_OBJECT & JSON_ARRAYAGG

Question

Need to create a Json_Object which can contain multiple nested Json_objects, Json_arrays & Json_arrayaggs within.

I have Created this table with some dummy data to demo the problem:

 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', 'val7','val11', 'val12');
insert into test_tbl values('val0', 'val5', 'val6', 'val7','val13', 'val14');
insert into test_tbl values('val0', 'val5', 'val6', 'val7','val11', 'val12');
insert into test_tbl values('val0', 'val5', 'val6', 'val8','val11','val12');
insert into test_tbl values('val1', 'val9', 'val10', 'val7','val11', 'val12');
insert into test_tbl values('val1', 'val9', 'val10', 'val7','val13', 'val14');

When Using following query to create a 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

This results in following json with duplicate key, value pairs in the aggregated array -

{
  "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"
        },
        {
          "key1": "val11",
          "key2": "val12"
        },
        {
          "key1": "val11",
          "key2": "val12"
        }
      ]
    },
    {
      "common": "val1",
      "list": [
        {
          "key1": "val9",
          "key2": "val10"
        },
        {
          "key1": "val9",
          "key2": "val10"
        }
      ],
      "anotherlist": [
        {
          "key1": "val11",
          "key2": "val12"
        },
        {
          "key1": "val13",
          "key2": "val14"
        }
      ]
    }
  ]
}

Whereas my expected Json is :

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

Thanks in advance for any suggestions on how to get the expected Json above.

MT0 · Accepted Answer · 2021-08-01 18:53:47Z

Use one DISTINCT sub-query for the first pair of columns and then use a second DISTINCT sub-query for the other pair of columns and JOIN on the common test_col1:

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

Outputs:

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

thank you, i now need to extend this to create similar sibling nodes within "anotherlist" & "list". I am trying to create a sub query within the subquery on the similar lines and using group by clause for the sub-sub query which includes unique keys from both the parents but the final result ends up containing duplicates.

Collectives™ on Stack Overflow

How to Aggregate distinct values from multiple lists using Oracle JSON_OBJECT & JSON_ARRAYAGG

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related