This post is part of a series that starts with this post.

Having seen REST-calls from the database to get the raw JSON-response it is time to look at how it can be transformed into separate columns.

This is the SQL we use to get data split up.

select object_id
     , owner
     , object_name
  from (select apex_web_service.make_rest_request
            (
              p_url => 'https://.../ords/rest_demo_schema/vw_rest_svc/'
            , p_http_method => 'GET'
            ) response
          from dual) t
        cross join json_table (t.response, '$.items[*]'
                      columns ( object_id   number   path '$.object_id'
                              , owner       varchar2 path '$.owner'
                              , object_name varchar2 path '$.object_name')) j
;

OBJECT_ID OWNER  OBJECT_NAME             
--------- ------ ----------------------- 
      134 SYS    ORA$BASE                
      143 SYS    DUAL                    
      144 PUBLIC DUAL                    
      441 PUBLIC MAP_OBJECT              
      543 SYS    SYSTEM_PRIVILEGE_MAP    
      544 SYS    I_SYSTEM_PRIVILEGE_MAP  
      545 PUBLIC SYSTEM_PRIVILEGE_MAP    
      546 SYS    TABLE_PRIVILEGE_MAP     
      547 SYS    I_TABLE_PRIVILEGE_MAP   
      548 PUBLIC TABLE_PRIVILEGE_MAP     
      549 SYS    USER_PRIVILEGE_MAP      
      550 SYS    I_USER_PRIVILEGE_MAP    
      551 PUBLIC USER_PRIVILEGE_MAP      
      552 SYS    STMT_AUDIT_OPTION_MAP   
      553 SYS    I_STMT_AUDIT_OPTION_MAP 
      554 PUBLIC STMT_AUDIT_OPTION_MAP   
      705 SYS    FINALHIST$              
     1401 SYS    DM$EXPIMP_ID_SEQ        
     1417 SYS    MODELGTTRAW$            
     1716 SYS    AV_DUAL                 
     1790 SYS    STANDARD                
     1792 SYS    DBMS_STANDARD           
     1793 PUBLIC DBMS_STANDARD           
     1794 SYS    PLITBLM                 
     1795 PUBLIC PLITBLM                 

That looks just like the output from an ordinary table. So we have wrapped it up on one side into a REST formatted response and on the receiving end we unpack it into columns again. Pretty neat.

But we only got 25 rows. We’ll get back to why that is. Let us address the different parts in the SQL to understand what goes on here.

Line 4-9 are similar to what we have seen before. It’s an inline view selecting the data from a REST-endpoint. It gives the json that is returned the alias “response” and the inline view an alias of “t” so it can be referenced later.

Line 10-13 is where the magic happens. The cross join is the effect of a join with now where clauses. Each row from the inline view (there will be just one) is joined with the json_table function. In it we first define what json we want to use as input, t.response is what the online view returned. $.items[*] references all rows in the items array. $ means the root of the json, items is the name of the array, brackets are how individual rows in an array are addressed and * say that we want all the rows. The columns-section picks out columns from the json in each entry in the array. The structure is column name we want, datatype, data element in the json-element (each row in the json-array).

Line 1-3 is noting we have not seen before. You can have * there but you get more then. The inline view returns a “column” t.response that has the raw json. When you have converted that into separate columns you will usually not want that returned anymore so you need to name the columns you do want.

That completes this post where we looked at how to go from returned JSON to get the data as the columns they were in the source. In the next post we’ll take a look at paging and how to get all rows returned to a SQL or via a view.