Understanding the JSON_TABLE Query

The APEX engine turns a REST Data Source’s JSON response payload into rows and column using the Data Profile information.

It dynamically produces a SELECT statement using the JSON_TABLE() operator to map JSON data into relational query results. For example, in a REST Data Source based on an Employees REST API, assume its Data Profile defines three columns:

Table 3 Data Profile Columns

Name	Data Type	Format Mask	Selector
EMPNO	NUMBER		empno
ENAME	VARCHAR2		ename
HIREDATE	DATE	YYYY-MM-DD"T"HH24:MI:SS"Z"	hiredate

At runtime, the APEX engine retrieves a JSON response payload that looks like this:

{
    "items": [
        {
            "empno": 7839,
            "ename": "KING",
            "job": "PRESIDENT",
            "mgr": null,
            "hiredate": "1981-11-17T00:00:00Z",
            "sal": 5000,
            "comm": null,
            "deptno": 10
        },
        ⋮
        {
            "empno": 7788,
            "ename": "SCOTT",
            "job": "ANALYST",
            "mgr": 7566,
            "hiredate": "1982-12-09T00:00:00Z",
            "sal": 3000,
            "comm": null,
            "deptno": 20
        }
    ]
}

It then uses the above response_payload CLOB in a SQL statement like the following:

select "EMPNO",
       "ENAME",
       "HIREDATE"
from (
    select *
    from (
    /* Cast extracted data to declared data type here */
    select **to_number(**"EMPNO"**)**                    as "EMPNO",
           "ENAME"                               as "ENAME",
           **to_date(**"HIREDATE",
                   '**YYYY-MM-DD"T"HH24:MI:SS"Z"**'**)** as "HIREDATE"
        from
            /* Use the row selector and column selectors here */
            json_table ( **response_payload** format json,'$."items"[*]'
                columns (
                    "**EMPNO**"    varchar2 ( 4000 ) path '$."**empno**"',
                    "**ENAME**"    varchar2 ( 4000 ) path '$."**ename**"',
                    "**HIREDATE**" varchar2 ( 4000 ) path '$."**hiredate**"'
                )
            )
    )
)

This statement produces the relational result:

     EMPNO ENAME      HIREDATE
---------- ---------- ---------
      7839 KING       17-NOV-81
      7698 BLAKE      01-MAY-81
      7782 CLARK      09-JUN-81
      7566 JONES      02-APR-81
      7788 SCOTT      09-DEC-82