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.