how to combine two JSON array list into single array list

Kannan Sekar · Mar 2, 2022 6:53AM

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 · Mar 2, 2022 9:04AM

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 · Mar 2, 2022 9:06AM

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 · Mar 2, 2022 10:03AM

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 · Mar 2, 2022 10:15AM

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 · Mar 2, 2022 10:21AM

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 · Mar 2, 2022 2:15PM

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 · Mar 2, 2022 2:47PM

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 · Mar 3, 2022 9:05AM

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 · Mar 5, 2022 4:36PM

How is the json to be updated available?

Is it

A file stored json?

Table column json?

Web service json?

Solomon Yakobson · Mar 6, 2022 3:49AM

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.

Forum Stats

Quick Links

Categories

how to combine two JSON array list into single array list

Answers

Data

Databases

Development

Development Tools

Open Source

Programs

Forum Stats

Categories

how to combine two JSON array list into single array list

Answers