Store and manipulate json data in Oracle database 12.2.0.1.0

July 22, 2018 admin OracleDatabase12C, 1

Store and manipulate json data in Oracle database 12.2.0.1.0

For the purpose of the post, I use the json-document generated in the previous post, see https://linecode-notes.com/2018/07/16/install-and-use-ords-with-oracle-database12c/

First, we create a table to store our json-document.
JSON_TABLE creates a relational view of JSON data. You can query the result returned by the function as a virtual relational table using SQL.
JSON_TABLE is only specifeid  in the FROM clause of a SELECT statement

---

• Let’s return rows with salary less than 4500 by specifying json_value in the where-clause.

SELECT
        p.employee_id,
        p.first_name,
        p.salary,
        p.department_id
FROM
    json_employee,
    JSON_TABLE ( po_document,’$’
                COLUMNS (
                     employee_id NUMBER PATH ‘$.employee_id’,
                     first_name VARCHAR2 ( 32 CHAR ) PATH ‘$.first_name’,
                     last_name NUMBER PATH ‘$.last_name’,
                     email VARCHAR2 ( 32 CHAR ) PATH ‘$.email’,
                     phone_number VARCHAR2 ( 32 CHAR ) PATH ‘$.phone_number’,
                     hire_date VARCHAR2 ( 32 CHAR ) PATH ‘$.hire_date’,
                     job_id VARCHAR2 ( 32 CHAR ) PATH ‘$.job_id’,
                     salary NUMBER PATH ‘$.salary’,
                     commission_pct NUMBER PATH ‘$.commission_pct’,
                     manager_id NUMBER PATH ‘$.manager_id’,
                     department_id NUMBER PATH ‘$.department_id’
                         )
                 ) AS p
WHERE
     JSON_VALUE(po_document,’$.salary’) < 4500;

• Only one row is returned: 

EMPLOYEE_ID FIRST_NAME   SALARY      DEPARTMENT_ID
———– ———- ———- ————-
200          Jennifer    4400               10

Install and use ORDS with Oracle Database12c Use SQL Developer Command-Line (SQLcl) to run SQL-scripts