3 Replies Latest reply on May 19, 2018 5:55 PM by Solomon Yakobson

    Stuffing a JSON list into a UDT

    gizzardqueen

      Using parts of the OE sample schema, I'm trying to update the CUSTOMERS table using some JSON as the source text.  One point I'm getting stuck on is how can I pull out a list of items (json array) and push them into a UDT (composed of a varray())?

       

      CREATE TYPE phone_list_typ
        OID '82A4AF6A4CD2656DE034080020E0EE3D'
        AS VARRAY(5) OF VARCHAR2(25);
      /
      

      CREATE TYPE cust_address_typ
        OID '82A4AF6A4CD1656DE034080020E0EE3D'
        AS OBJECT
          ( street_address     VARCHAR2(40)
          , postal_code        VARCHAR2(10)
          , city               VARCHAR2(30)
          , state_province     VARCHAR2(10)
          , country_id         CHAR(2)
          );
      /
      
      CREATE TABLE customers
          ( customer_id        NUMBER(6)
          , cust_first_name    VARCHAR2(20) CONSTRAINT cust_fname_nn NOT NULL
          , cust_last_name     VARCHAR2(20) CONSTRAINT cust_lname_nn NOT NULL
          , cust_address       cust_address_typ
          , nls_language       VARCHAR2(3)
          , nls_territory      VARCHAR2(30)
          , credit_limit       NUMBER(9,2)
          , cust_email         VARCHAR2(40)
          , account_mgr_id     NUMBER(6)
          , cust_geo_location  MDSYS.SDO_GEOMETRY
          , CONSTRAINT         customer_credit_limit_max
                               CHECK (credit_limit <= 5000)
          , CONSTRAINT         customer_id_min
                               CHECK (customer_id > 0)
          ) ;
      
      declare
  v_json   VARCHAR2(4000) := '{
                           
       "customers": [{
                             
       "customer": {
                                
      "customer_id": 984,
                                
      "customer_first_name": "Mark",
                               
       "customer_last_name": "Twain",
                               
       "cust_address": [{"street": "8768 N State Rd 37",
                                                 
       "zip": "47404",
                                                  
      "city": "Bloomington",
                                                 
       "state": "IN",
                                                  
      "country": "US"}],
                                
      "phone_numbers": ["+1", "812", "123", "4115"],
                               
       "nls_language": "us",
                                
      "nls_territory": "America",
                               
       "credit_limit": "234",
                                
      "cust_email": "FooBar@example.com",
                                
      "account_mgr_id": 139,
                               
       "cust_lat": -80.32,
                                
      "cust_lon": 39.849678
                             
       }
                            
      }]
                         
       }';
  
      v_output varchar2(32767)
      ;begin
  
      INSERT INTO customers (customer_id, cust_first_name, cust_last_name,
                         
      cust_address,
                         phone_numbers,
                         
      nls_language,
                         nls_territory, 
      credit_limit, cust_email, account_mgr_id,
                         
      cust_geo_location)
    
      select
      customer_id,
      customer_first_name,
      
      customer_last_name,
      cust_address_typ(street,zip,city,state,country) as cust_address,
      
      phone_list_typ(phone_numbers) as cust_phone,
     
       nls_language,
      nls_territory,
      
      credit_limit,
      cust_email,
     
       account_mgr_id,
      MDSYS.SDO_GEOMETRY(2001, 8307, MDSYS.SDO_POINT_TYPE(cust_lat, cust_lon, NULL), NULL, NULL) as cust_geo_location
    
      from json_table(v_json, '$.customers[*].customer'
                   
       COLUMNS
                      
      (
                       
       customer_id VARCHAR2(32) PATH '$.customer_id',
                        
      customer_first_name varchar2(32) path '$.customer_first_name',
                       
       customer_last_name VARCHAR2(32) PATH '$.customer_last_name',
                     
       NESTED PATH '$.cust_address[*]'
                        
      COLUMNS (
                           
       street VARCHAR2(4000) PATH '$.street',
                           
       zip VARCHAR2(4000) PATH '$.zip',
                          
        city VARCHAR2(4000) PATH '$.city',
                        
          state VARCHAR2(4000) PATH '$.state',
                          
        country VARCHAR2(4000) PATH '$.country'
                        
        ),
                       
       phone_numbers VARCHAR2(4000) PATH '$.phone_numbers',
             
                 nls_language VARCHAR2(32) PATH '$.nls_language',
               
               nls_territory VARCHAR2(32) PATH '$.nls_territory',
             
                 credit_limit VARCHAR2(32) PATH '$.credit_limit',
                    
          cust_email VARCHAR2(32) PATH '$.cust_email',
                      
        account_mgr_id NUMBER PATH '$.account_mgr_id',
                     
         cust_lat NUMBER PATH '$.cust_lat',
                      
        cust_lon NUMBER PATH '$.cust_lon'
                     
       )
   
       );end;
      
      

       

       

      I tried creating a NESTED PATH for the phone_numbers like so but that caused the pl/sql to try to insert multiple times (one for each element in the phone_numbers array).

       

       

      NESTED PATH '$.phone_numbers[*]'
                              COLUMNS (
                                  phone_numbers VARCHAR2(4000) PATH '$[0]'
                                ),
      

       

      How can I get the phone numbers properly "typed" for table insertion?

        • 1. Re: Stuffing a JSON list into a UDT
          Beda Hammerschmidt-Oracle

          Hi Gizzardqueen,

           

          I am with the Oracle Team that develops the JSON database feature.

          As you correctly observed NESTED PATH will return too many rows. This is because a new row is created for every item in the array (phone number)  with all values outside the array being repeated (e.g. the customer id).

           

          Instead of unnesting the array in side JSON_TABLE you need to unnest it outside.

          So first, you just select the array inside JSON_TABLE (using FORMAT JSON in the columns clause).

          Then the array will be converted to a collection type (using a pl/sql function).

           

          create or replace function getPhoneVarray(phoneArray IN VARCHAR2) return phone_list_typ is

          a JSON_ARRAY_T;

          p phone_list_typ;

          begin

          if phoneArray is null then

             return null;

          end if;

           

           

          p := phone_list_typ();

          a := JSON_ARRAY_T(phoneArray);

           

          -- json_array_t is 0-based, SQL collections are 1-based

          for i in 0 .. (a.get_size-1) loop

             p.extend;

             p(i+1) := a.get_string(i);

          end loop;

          return p; 

          end;

          /

           

           

          Look for FORMAT JSON in the following query:

           

          select customer_id, customer_first_name, getPhoneVarray(phone_numbers_arr)

          from json_table('{                            

          "customers": [{                            

          "customer": {                               

          "customer_id": 984,                                

          "customer_first_name": "Mark",                                

          "customer_last_name": "Twain",                                

          "cust_address": [{"street": "8768 N State Rd 37",                                                 

          "zip": "47404",                                               

          "city": "Bloomington",                                                  

          "state": "IN",                                                   

          "country": "US"}],                                 

          "phone_numbers": ["+1", "812", "123", "4115"],                                

          "nls_language": "us",                                 

          "nls_territory": "America",                                

          "credit_limit": "234",                                 

          "cust_email": "FooBar@example.com",                                

          "account_mgr_id": 139,                                

          "cust_lat": -80.32,                                 

          "cust_lon": 39.849678                             

          }                         

          }]                          

          }' , '$.customers[*].customer' COLUMNS                        

          (                         

             customer_id VARCHAR2(32) PATH '$.customer_id',                          

             customer_first_name varchar2(32) path '$.customer_first_name',                         

             customer_last_name VARCHAR2(32) PATH '$.customer_last_name',                                               

             phone_numbers_arr VARCHAR2(4000) FORMAT JSON PATH '$.phone_numbers'                 

          )     

          );

           

           

           

          hope this helps

          thanks

          b

          • 2. Re: Stuffing a JSON list into a UDT
            Beda Hammerschmidt-Oracle

            in the example the function uses the JSON plsql api (https://docs.oracle.com/en/database/oracle/oracle-database/12.2/adjsn/using-PLSQL-object-types-for-JSON.html#GUID-F05615… )

            to iterate over the array and extract its content,

            one could also use a cursor with a json_table query to do the same

            • 3. Re: Stuffing a JSON list into a UDT
              Solomon Yakobson

              No need to use PL/SQL for unnesting (and, btw your solution will work starting 12.2 only):

               

              INSERT

                INTO customers(

                              customer_id,

                              cust_first_name,

                              cust_last_name,

                              cust_address,

                              phone_numbers,

                              nls_language,

                              nls_territory,

                              credit_limit,

                              cust_email,

                              account_mgr_id,

                              cust_geo_location

                              )

                WITH data AS (

                              SELECT '{

                                      "customers": [

                                                    {"customer":

                                                                {

                                                                  "customer_id": 984,

                                                                  "customer_first_name": "Mark",

                                                                  "customer_last_name": "Twain",

                                                                  "cust_address": [

                                                                                  {

                                                                                    "street": "8768 N State Rd 37",

                                                                                    "zip": "47404",

                                                                                    "city": "Bloomington",

                                                                                    "state": "IN",

                                                                                    "country": "US"

                                                                                  }

                                                                                  ],

                                                                  "phone_numbers": ["+1", "812", "123", "4115"],

                                                                  "nls_language": "us",

                                                                  "nls_territory": "America",

                                                                  "credit_limit": "234",

                                                                  "cust_email": "FooBar@example.com",

                                                                  "account_mgr_id": 139,

                                                                  "cust_lat": -80.32,

                                                                  "cust_lon": 39.849678

                                                                }

                                                    }

                                                    ]

                                      }' json_doc

                                FROM  dual

                            ),

                        t AS (

                              SELECT  customer_id,

                                      customer_first_name,

                                      customer_last_name,

                                      cust_address_typ(street,zip,city,state,country) cust_address,

                                      phone_list,

                                      nls_language,

                                      nls_territory,

                                      credit_limit,

                                      cust_email,

                                      account_mgr_id,

                                      MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(cust_lat,cust_lon,NULL),NULL,NULL) cust_geo_location

                                FROM  data,

                                      JSON_TABLE(

                                                json_doc,

                                                '$.customers[*].customer'

                                                COLUMNS(

                                                        customer_id VARCHAR2(32) PATH '$.customer_id',

                                                        customer_first_name varchar2(32) path '$.customer_first_name',

                                                        customer_last_name VARCHAR2(32) PATH '$.customer_last_name',

                                                        NESTED PATH '$.cust_address[*]'

                                                          COLUMNS(

                                                                  street VARCHAR2(4000) PATH '$.street',

                                                                  zip VARCHAR2(4000) PATH '$.zip',

                                                                  city VARCHAR2(4000) PATH '$.city',

                                                                  state VARCHAR2(4000) PATH '$.state',

                                                                  country VARCHAR2(4000) PATH '$.country'

                                                                  ),

                                                        phone_list VARCHAR2(4000) FORMAT JSON PATH '$.phone_numbers',

                                                        nls_language VARCHAR2(32) PATH '$.nls_language',

                                                        nls_territory VARCHAR2(32) PATH '$.nls_territory',

                                                        credit_limit VARCHAR2(32) PATH '$.credit_limit',

                                                        cust_email VARCHAR2(32) PATH '$.cust_email',

                                                        account_mgr_id NUMBER PATH '$.account_mgr_id',

                                                        cust_lat NUMBER PATH '$.cust_lat',

                                                        cust_lon NUMBER PATH '$.cust_lon'

                                                        )

                                                )

                            )

                SELECT  customer_id,

                        customer_first_name,

                        customer_last_name,

                        cust_address,

                        (

                        SELECT  CAST(

                                      COLLECT(phone_number)

                                      AS phone_list_typ

                                    )

                          FROM  JSON_TABLE(

                                            phone_list,

                                            '$[*]'

                                            COLUMNS(

                                                    phone_number VARCHAR2(25) PATH '$'

                                                  )

                                          )

                        ) phone_list,

                        nls_language,

                        nls_territory,

                        credit_limit,

                        cust_email,

                        account_mgr_id,

                        cust_geo_location

                  FROM  t

              /

              SQL> /

               

              1 row created.

               

              SQL> SELECT  *

                2    FROM  customers

                3  /

               

               

              CUSTOMER_ID CUST_FIRST_NAME      CUST_LAST_NAME

              ----------- -------------------- --------------------

              CUST_ADDRESS(STREET_ADDRESS, POSTAL_CODE, CITY, STATE_PROVINCE, COUNTRY_ID)

              -------------------------------------------------------------------------------------------------------

              PHONE_NUMBERS

              -------------------------------------------------------------------------------------------------------

              NLS NLS_TERRITORY                  CREDIT_LIMIT CUST_EMAIL                              ACCOUNT_MGR_ID

              --- ------------------------------ ------------ ---------------------------------------- --------------

              CUST_GEO_LOCATION(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)

              -------------------------------------------------------------------------------------------------------

                      984 Mark                Twain

              CUST_ADDRESS_TYP('8768 N State Rd 37', '47404', 'Bloomington', 'IN', 'US')

              PHONE_LIST_TYP('+1', '812', '123', '4115')

              us  America                                234 FooBar@example.com                                  139

              SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(-80.32, 39.849678, NULL), NULL, NULL)

               

              SQL>

               

              SY.