how to combine two JSON array list into single array list

Kannan Sekar

We are using Oracle 19C version. we have json like below,

{ "tktKey": "123456", "attlist1": [ { "attkey": "123",   "attval": "abc"  },  { "attkey": "456","attval": "def"  } ],  "attlist2": [{"attkey": "098", "attval": "xyz"  } ]}

form above json i need to combine attlist1 and attlist2 and make it to single attlist.

The Excepted output should be like below,

{ "tktKey": "123456", "attlist": [ { "attkey": "123", "attval": "abc"  },  { "attkey": "456", "attval": "def" },  { "attkey": "098", "attval": "xyz" } ]}

Alex Nuijten

SQL> create table js                                                                       
 2 (str clob                                                                          
 3 )
 4* /                                                                              
                                                                                 
Table JS created.                                                                         
                                                                                 
SQL>                                                                               
SQL> insert into js values ('{ "tktKey": "123456", "attlist1": [ { "attkey": "123",  "attval": "abc" }, { "attkey": "456","attval": "def" } ], "attlist2": [{"attkey": "098", "attval": "xyz" } ]}')                                                             
 2* /                                                                              
                                                                                 
1 row inserted.                                                                          
                                                                                 
SQL> alter table js add constraint json_chk check (str is json)                                                  
 2* /                                                                              
                                                                                 
Table JS altered.                                                                         
                                                                                 
SQL> update js                                                                          
 2   set str= json_transform                                                                  
 3           (js.str                                                                 
 4           ,append '$.attlist1'                                                           
 5                  = json_query (js.str, '$.attlist2[*]')                                           
 6           ,remove '$.attlist2'                                                           
 7            returning clob pretty)
 8* /                                                                              
                                                                                 
1 row updated.                                                                          
                                                                                 
SQL> select *                                                                           
 2  from js                                                                          
 3* /                                                                              
                                                                                 
                                                                                                                  STR                                              
_______________________________________________________________________________________________________________________________________________________________________________________________________________________________________                                              
{                                                                                 
 "tktKey" : "123456",                                                                      
 "attlist1" :
 [
  {
   "attkey" : "123",
   "attval" : "abc"
  },
  {
   "attkey" : "456",
   "attval" : "def"
  },
  {
   "attkey" : "098",
   "attval" : "xyz"
  }
 ]
}   

Alex Nuijten

I missed that you also wanted to rename "attlist1"

update js
   set str= json_transform
                    (js.str
                    ,append '$.attlist1'
                                 = json_query (js.str, '$.attlist2[*]')
                    ,remove '$.attlist2'
                    ,rename '$.attlist1' ='attlist')
/

Kannan Sekar

Thank you Alex. Can we use JSON_TRANFORMATION in select clause? Because json are stored in two different variables and i want combine that json and put into another variable.

var1 := "attlist1": [ { "attkey": "123",   "attval": "abc"  },  { "attkey": "456","attval": "def"  } ]

var2 := "attlist2": [{"attkey": "098", "attval": "xyz"  } ]

Alex Nuijten

The variables are not valid JSON...

When they are, you can probably do something like what I showed you with SELECT FROM DUAL.

Show a complete example of what you have, instead of changing the question.

Alex Nuijten

If they were proper JSON, then you could do this

SQL> declare
  2     var1 varchar2(200);
  3     var2 varchar2(200);
  4     var3 varchar2(200);
  5  begin
  6     var1 := '{"attlist1": [ { "attkey": "123",   "attval": "abc"  },  { "attkey": "456","attval": "def"  } ]}';
  7     var2 := '{"attlist2": [{"attkey": "098", "attval": "xyz"  } ]}';                    
  8     select json_transform
  9                      (var1
 10                      ,append '$.attlist1'
 11                                   = json_query (var2, '$.attlist2[*]')
 12                      ,remove '$.attlist2'
 13                      ,rename '$.attlist1' ='attlist')
 14       into var3
 15       from dual;
 16     sys.dbms_output.put_line ('===>> '||var3);
 17  end;
 18* /
===>>
{"attlist":[{"attkey":"123","attval":"abc"},{"attkey":"456","attval":"def"},{"attkey":"098","attval":"xyz"}]}




PL/SQL procedure successfully completed.

Kannan Sekar

Thanks Alex its working. But it is returning NULL if have more than one array value in variable2 (var2)

  6     var1 := '{"attlist1": [ { "attkey": "123",   "attval": "abc"  },  { "attkey": "456","attval": "def"  } ]}';
  7     var2 := '{"attlist2": [{"attkey": "098", "attval": "xyz"  }, {"attkey": "999", "attval": "pqrs"  } ]}'; 

Solomon Yakobson

SQL solution:

WITH T AS (
           SELECT  JSON_OBJECT(
                               'attlist' VALUE
                               JSON_ARRAYAGG(
                                             NVL(LIST1,LIST2)
                                             FORMAT JSON
                                            )
                              ) LIST
             FROM  TBL,
                   JSON_TABLE(
                              JSON_DOC,
                              '$'
                              COLUMNS(
                                      NESTED PATH '$.attlist1[*]' COLUMNS(LIST1 FORMAT JSON PATH '$[*]'),
                                      NESTED PATH '$.attlist2[*]' COLUMNS(LIST2 FORMAT JSON PATH '$[*]')
                                     )
                             ) J
          )
SELECT  JSON_SERIALIZE(
                       JSON_DOC
                       PRETTY
                      ) JSON_DOC,
        JSON_MERGEPATCH(
                        JSON_MERGEPATCH(
                                        JSON_MERGEPATCH(
                                                        JSON_DOC,
                                                        '{"attlist1":null}'
                                                       ),
                                        '{"attlist2":null}'
                                       ),
                        LIST
                        RETURNING CLOB PRETTY
                       ) NEW_JSON_DOC
  FROM  TBL,
        T
/

JSON_DOC                                 NEW_JSON_DOC
---------------------------------------- ----------------------------------------
{                                        {
  "tktKey" : "123456",                     "tktKey" : "123456",
  "attlist1" :                             "attlist" :
  [                                        [
    {                                        {
      "attkey" : "123",                        "attkey" : "123",
      "attval" : "abc"                         "attval" : "abc"
    },                                       },
    {                                        {
      "attkey" : "456",                        "attkey" : "456",
      "attval" : "def"                         "attval" : "def"
    }                                        },
  ],                                         {
  "attlist2" :                                 "attkey" : "098",
  [                                            "attval" : "xyz"
    {                                        }
      "attkey" : "098",                    ]
      "attval" : "xyz"                   }
    }
  ]
}

SQL>

SY.

Alex Nuijten

The only thing that I can think of is to recreate the whole JSON based on the variables, something similar to what Solomon showed you.

I would do it like this, cause I think it's a little easier to understand than JSON_MERGEPATCH

SQL> declare
  2     var1 varchar2(200);
  3     var2 varchar2(200);
  4     var3 varchar2(600);
  5  begin            
  6     var1 := '{"attlist1": [ { "attkey": "123",   "attval": "abc"  },  { "attkey": "456","attval": "def"  } ]}';
  7     var2 := '{"attlist2": [{"attkey": "098", "attval": "xyz"  }, {"attkey": "999", "attval": "pqrs"  } ]}'; 
  8     select json_object ('attlist' value 
  9                json_arrayagg(json_object ('attkey' value attkey
 10                                          ,'attval' value attval)))
 11       into var3
 12       from (select *
 13               from json_table (var1, '$.attlist1[*]'
 14                                columns attkey varchar2(10) path '$.attkey'
 15                                       ,attval varchar2(10) path '$.attval')
 16              union all
 17             select *
 18               from json_table (var2, '$.attlist2[*]'
 19                               columns attkey varchar2(10) path '$.attkey'
 20                                      ,attval varchar2(10) path '$.attval'));
 21     sys.dbms_output.put_line ('===>> '||var3);
 22  end;
 23  /
===>>
{"attlist":[{"attkey":"123","attval":"abc"},{"attkey":"456","attval":"def"},{"attkey":"098","attval":"xyz"},{"a
ttkey":"999","attval":"pqrs"}]}




PL/SQL procedure successfully completed.

dvohra21

How is the json to be updated available?

Is it

A file stored json?

Table column json?

Web service json?

Solomon Yakobson

https://community.oracle.com/tech/developers/discussion/comment/16827527#Comment_16827527

So you need to merge json arrays in PL/SQL? If so:

declare
    v_var1            varchar2(100);
    v_var2            varchar2(100);
    v_temp_json_array json_array_t;
    v_res_json_array  json_array_t;
    v_res_json_object json_object_t := json_object_t('{"tktKey":"123456"}');
begin
    v_var1            := '{"attlist1": [ { "attkey": "123",   "attval": "abc"  },  { "attkey": "456","attval": "def"  } ]}';
    v_var2            := '{"attlist2": [{"attkey": "098", "attval": "xyz"  }, {"attkey": "999", "attval": "pqrs"  } ]}';
    v_res_json_array  := json_object_t(v_var1).get_array('attlist1');
    v_temp_json_array := json_object_t(v_var2).get_array('attlist2');
    for v_i in 0..v_temp_json_array.get_size - 1 loop
      v_res_json_array.append(v_temp_json_array.get(v_i));
    end loop;
    v_res_json_object.put('attlist',v_res_json_array);
    dbms_output.put_line(v_res_json_object.stringify);
end;
/

{"tktKey":"123456","attlist":[{"attkey":"123","attval":"abc"},{"attkey":"456","attval":"def"},{"attkey":"098","attval":"xyz"},{"attkey":"999","attval":"pqrs"}]}

PL/SQL procedure successfully completed.

SQL>

SY.