Oracle JSON_ARRAYAGG returns duplicate JSON_OBJECTs

user_12895663 0
Viewed 361

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)
);
insert into test_tbl values('val0', 'val1', 'val2', 'val7');
insert into test_tbl values('val0', 'val3', 'val4', 'val7');
insert into test_tbl values('val0', 'val5', 'val6', 'val7');
insert into test_tbl values('val0', 'val5', 'val6', 'val7');
insert into test_tbl values('val0', 'val5', 'val6', 'val8');
insert into test_tbl values('val1', 'val9', 'val10', 'val7');
insert into test_tbl values('val1', 'val9', 'val10', 'val7');

When Using following query to create Json:

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)))) )
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": "val5",
          "key2": "val6"
        },
        {
          "key1": "val3",
          "key2": "val4"
        },
        {
          "key1": "val1",
          "key2": "val2"
        }
      ]
    },
    {
      "common": "val1",
      "list": [
        {
          "key1": "val9",
          "key2": "val10"
        },
        {
          "key1": "val9",
          "key2": "val10"
        }
      ]
    }

Whereas my expected Json is :

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

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

oracle sql json
1 Answers

Use a sub-query with DISTINCT to remove the duplicates:

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
               )
             )
           )
         )
       )
FROM   (
  SELECT DISTINCT
         test_col1, test_col2, test_col3
  FROM   test_tbl
  WHERE  test_col4 = 'val7'
)
GROUP BY
       test_col1
user_15092640
Related