Before You Begin
This 15-minute tutorial shows you how to add a JSON flex-field column to a table and use SQL to access individual JSON fields as relational columns.
You will:
- Create a table with employee data
- Add a flex-field column of JSON data
- Query the table to select JSON fields
- Create and query a view that projects JSON fields as relational columns
- Create and query a view that projects individual JSON array elements
Background
A common use case for a JSON column is to provide some schemaless data in a relational context. A JSON column can contain arbitrary JSON data, which can be queried or projected out as relational data.
A JSON flex-field column lets applications add new kinds of data to a table without adding new columns. To accommodate this, no database administrator is needed (no data-definition language changes), and existing queries need not change. There is no downtime for application or database.
This is possible because you can access any parts of a JSON document using Oracle SQL. You can query it directly or project it to a view, which can then be queried as relational data.
To allow for future needs, one way that application developers have sometimes implemented flex fields in SQL is to add extra relational columns of various types (NUMBER
, VARCHAR2
, CLOB
, BLOB
) at table creation time. But the number of such columns is arbitrary and limited, and their names are generally not meaningful. A JSON flex-field column is truly flexible — any number of fields projected any number of ways to relational columns of any type and with meaningful names.
What Do You Need?
- Oracle Database 12.2 or later.
Good to have, but not required:
- Knowledge of SQL
- Knowledge of JSON or JavaScript notation
Create a Table with Employee Data
Create a simple table as a starting point — no JSON column yet. The columns represent the employee number (EMPNO
), employee name (ENAME
), job title (JOB
), manager (MGR
), date of hire (HIREDATE
), salary (SAL
), sales commission (COMM
), and department number (DEPTNO
).
CREATE TABLE emp ( empno NUMBER (4) NOT NULL, ename CHAR (10), job CHAR (9), mgr NUMBER (4), hiredate DATE, sal NUMBER (7, 2), comm NUMBER (7, 2), deptno NUMBER (2) NOT NULL ) BEGIN INSERT INTO emp VALUES (7839, 'KING', 'PRESIDENT', NULL, '17-NOV-11', 500000, NULL, 10); INSERT INTO emp VALUES (7698, 'BLAKE', 'MANAGER', 7839, '1-MAY-11', 285000, NULL, 30); INSERT INTO emp VALUES (7782, 'CLARK', 'MANAGER', 7839, '9-JUN-11', 245000, NULL, 10); INSERT INTO emp VALUES (7566, 'JONES', 'MANAGER', 7839, '2-APR-11', 297500, NULL, 20); INSERT INTO emp VALUES (7654, 'MARTIN', 'SALESMAN', 7698, '28-SEP-11', 125000, 140000, 30); INSERT INTO emp VALUES (7499, 'ALLEN', 'SALESMAN', 7698, '20-FEB-11', 160000, 30000, 30); INSERT INTO emp VALUES (7844, 'TURNER', 'SALESMAN', 7698, '8-SEP-11', 150000, 0, 30); INSERT INTO emp VALUES (7900, 'JAMES', 'CLERK', 7698, '3-DEC-11', 95000, NULL, 30); INSERT INTO emp VALUES (7521, 'WARD', 'SALESMAN', 7698, '22-FEB-11', 125000, 50000, 30); INSERT INTO emp VALUES (7902, 'FORD', 'ANALYST', 7566, '3-DEC-11', 300000, NULL, 20); INSERT INTO emp VALUES (7369, 'SMITH', 'CLERK', 7902, '17-DEC-10', 80000, NULL, 20); INSERT INTO emp VALUES (7788, 'SCOTT', 'ANALYST', 7566, '09-DEC-12', 300000, NULL, 20); INSERT INTO emp VALUES (7876, 'ADAMS', 'CLERK', 7788, '12-JAN-13', 110000, NULL, 20); INSERT INTO emp VALUES (7934, 'MILLER', 'ANALYST', 7782, '23-JAN-12', 130000, NULL, 10); COMMIT; END; 1 row(s) inserted.
Add a Flex-Field Column of JSON Data
The data is stored as CLOB
data. (It could alternatively be stored as BLOB
or VARCHAR2
data.) The IS JSON
check constraint ensures that only well-formed JSON data can be inserted into the column.
ALTER TABLE emp ADD flex CLOB CHECK (flex IS JSON) Table altered.
Populate column FLEX
with cost-center data and skills data for two employees.
UPDATE emp SET flex = '{"costCenter" : 12, "skills" : ["sql", "c", "java"]}' WHERE empno = 7934 1 row(s) updated. UPDATE emp SET flex = '{"costCenter" : 10, "skills" : ["mgt","excel"]}' WHERE empno = 7698 1 row(s) updated. COMMIT Statement processed.
Show the whole table. Column FLEX
contains a JSON object for employees Blake and Miller; it is NULL
for the other employees.
SELECT * FROM emp EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO FLEX -------------------------------------------------------------- 7839 KING PRESIDENT - 17-NOV-81 500000 - 10 - 7698 BLAKE MANAGER 7839 03-DEC-81 285000 - 30 {"costCenter" : 10, "skills" : ["mgt","excel"]} 7782 CLARK MANAGER 7839 09-JUN-81 245000 - 10 - 7566 JONES MANAGER 7839 2-APR-11 297500 - 20 - 7782 MARTIN SALESMAN 7698 28-SEP-11 125000 14000 20 - 7499 ALLEN SALESMAN 7698 20-FEB-11 160000 30000 30 - 7844 TURNER SALESMAN 7698 8-SEP-11 150000 0 30 - 7900 JAMES CLERK 7698 3-DEC-11 95000 - 30 - 7521 WARD SALESMAN 7698 22-FEB-11 125000 50000 30 - 7902 FORD ANALYST 7566 3-DEC-11 300000 - 20 - 7369 SMITH CLERK 7902 17-DEC-10 80000 - 20 - 7788 SCOTT ANALYST 7566 09-DEC-12 300000 - 20 - 7876 ADAMS CLERK 7788 12-JAN-13 110000 - 20 - 7934 MILLER ANALYST 7782 23-JAN-12 130000 - 10 {"costCenter" : 12, "skills" : ["sql", "c", "java"]} 14 rows selected.
Select JSON Fields
Select field costCenter
from the JSON column.
The simple dot notation drills down from the table (EMP
), which must be referenced by a table alias (E
), through the JSON column (FLEX
), to a JSON object field (costCenter
). Unquoted SQL table and column names are case-insensitive (which, in effect, means they are uppercase). but the JSON-data part of the dot notation is case-sensitive, just as is JSON syntax. If we had used e.flex.costcenter
or e.flex.COSTCENTER
or e.flex.Costcenter
then there would be no matches.
SELECT e.flex.costCenter FROM emp e COSTCENTER ---------- - 10 - - - - - - - - - - - 12 14 rows selected.
Select field skills, whose value is an array
.
SELECT e.flex.skills FROM emp e SKILLS ------ - ["mgt","excel"] - - - - - - - - - - - ["sql", "c", "java"] 14 rows selected.
Select only the first element from array skills
. (Array indexing is zero-based in JSON, as it is in JavaScript.)
SELECT e.flex.skills[0] FROM emp e SKILLS ------ - mgt - - - - - - - - - - - sql 14 rows selected.
Create a View that Projects JSON Fields as Columns
The view projects JSON field costCenter
as column CCTR
and field skills
as column SKILLS
.
CREATE VIEW emp-v1 AS SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno, e.flex.costCenter "CCTR", e.flex.skills "SKILLS" FROM emp e View created.
Query view emp-v1
, selecting the name, cost center and skills for each employee whose cost center is greater than 11 (which means only employee Miller).
SELECT ename, costCenter, skills FROM emp-v1 e WHERE cctr > 11 ENAME CCTR SKILLS ------------------ MILLER 12 ["sql", "c", "java"]
Create a View that Projects JSON Array Elements Using JSON_TABLE
SQL function json_table
generates a row of virtual-table data for each JSON value selected by a row pattern. The columns of each generated row are defined by the column patterns of the COLUMNS
clause.
Here the row pattern is $.skills[*]
, which matches elements of the skills
array. The only column pattern used here is $
, which matches an individual element in array skills
. The virtual table is given table alias JT
, which is used in the FROM
list. An implicit lateral join joins the virtual table with table EMP
(alias E
).
SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno, e.flex.costCenter cctr, jt.skill FROM emp e, json_table(e.flex.skills[*] COLUMNS ("SKILL" VARCHAR2(20) PATH '$')) jt EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO CCTR SKILL -------------------------------------------------------------------- 7698 BLAKE MANAGER 7839 03-DEC-81 285000 - 30 10 mgt 7698 BLAKE MANAGER 7839 03-DEC-81 285000 - 30 10 excel 7934 MILLER ANALYST 7782 23-JAN-12 130000 - 10 12 sql 7934 MILLER ANALYST 7782 23-JAN-12 130000 - 10 12 c 7934 MILLER ANALYST 7782 23-JAN-12 130000 - 10 12 java 5 rows selected.
To be able to see rows for all employees, including those that have a NULL
value for column FLEX
, use a left outer join between the two tables. Here is the same query, but with that outer join.
SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno, e.flex.costCenter cctr, jt.skill FROM emp e LEFT OUTER JOIN json_table(e.flex, '$.skills[*]' COLUMNS ("SKILL" VARCHAR2(20) PATH '$')) jt ON (1=1) EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO CCTR SKILL --------------------------------------------------------------- 7839 KING PRESIDENT - 17-NOV-81 500000 - 10 - - 7698 BLAKE MANAGER 7839 03-DEC-81 285000 - 30 10 mgt 7698 BLAKE MANAGER 7839 03-DEC-81 285000 - 30 10 excel 7782 CLARK MANAGER 7839 09-JUN-81 245000 - 10 - - 7566 JONES MANAGER 7839 2-APR-11 297500 - 20 - - 7782 MARTIN SALESMAN 7698 28-SEP-11 125000 14000 20 - - 7499 ALLEN SALESMAN 7698 20-FEB-11 160000 30000 30 - - 7844 TURNER SALESMAN 7698 8-SEP-11 150000 0 30 - - 7900 JAMES CLERK 7698 3-DEC-11 95000 - 30 - - 7521 WARD SALESMAN 7698 22-FEB-11 125000 50000 30 - - 7902 FORD ANALYST 7566 3-DEC-11 300000 - 20 - - 7369 SMITH CLERK 7902 17-DEC-10 80000 - 20 - - 7788 SCOTT ANALYST 7566 09-DEC-12 300000 - 20 - - 7876 ADAMS CLERK 7788 12-JAN-13 110000 - 20 - - 7934 MILLER ANALYST 7782 23-JAN-12 130000 - 10 12 sql 7934 MILLER ANALYST 7782 23-JAN-12 130000 - 10 12 c 7934 MILLER ANALYST 7782 23-JAN-12 130000 - 10 12 java 17 rows selected.
Finally, wrapping that query with CREATE VIEW ... AS SELECT
gives us a view (emp-v2
) of relational data covering all of the employees. All of the JSON data has been projected out as relational data.
CREATE VIEW emp-v2 AS SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno, e.flex.costCenter cctr, jt.skill FROM emp e LEFT OUTER JOIN json_table(e.flex, '$.skills[*]' COLUMNS ("SKILL" VARCHAR2(20) PATH '$')) jt ON (1=1) View created. SELECT ename FROM emp-v2 WHERE skill = 'sql' ENAME ----- MILLER