Using JSON to Implement a SQL Flex-Field Column
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
Want
to Learn More?