Forum Stats

  • 3,851,765 Users
  • 2,264,024 Discussions
  • 7,904,843 Comments

Discussions

how to combine two JSON array list into single array list

Kannan Sekar
Kannan Sekar Member Posts: 56 Blue Ribbon

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" } ]}

«1

Answers

  • Alex Nuijten
    Alex Nuijten Member Posts: 242 Silver Badge
    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
    Alex Nuijten Member Posts: 242 Silver Badge

    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
    Kannan Sekar Member Posts: 56 Blue Ribbon

    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
    Alex Nuijten Member Posts: 242 Silver Badge

    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
    Alex Nuijten Member Posts: 242 Silver Badge

    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
    Kannan Sekar Member Posts: 56 Blue Ribbon

    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
    Solomon Yakobson Member Posts: 19,722 Red Diamond

    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
    Alex Nuijten Member Posts: 242 Silver Badge

    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
    dvohra21 Member Posts: 14,664 Gold Crown

    How is the json to be updated available?

    Is it

    A file stored json?

    Table column json?

    Web service json?

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,722 Red Diamond
    edited Mar 6, 2022 3:50AM

    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.

    Alex Nuijten