Oracle Live SQL - Script: Using JSON for Flexfields

Statement 1

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
)

Table created.

Statement 2

BEGIN
   INSERT INTO emp
        VALUES (7839,
                'KING',
                'PRESIDENT',
                NULL,
                '17-NOV-81',
                5000,
                NULL,
                10);

   INSERT INTO emp
        VALUES (7698,
                'BLAKE',
                'MANAGER',
                7839,
                '1-MAY-81',
                2850,
                NULL,
                30);

   INSERT INTO emp
        VALUES (7782,
                'CLARK',
                'MANAGER',
                7839,
                '9-JUN-81',
                2450,
                NULL,
                10);

   INSERT INTO emp
        VALUES (7566,
                'JONES',
                'MANAGER',
                7839,
                '2-APR-81',
                2975,
                NULL,
                20);

   INSERT INTO emp
        VALUES (7654,
                'MARTIN',
                'SALESMAN',
                7698,
                '28-SEP-81',
                1250,
                1400,
                30);

   INSERT INTO emp
        VALUES (7499,
                'ALLEN',
                'SALESMAN',
                7698,
                '20-FEB-81',
                1600,
                300,
                30);

   INSERT INTO emp
        VALUES (7844,
                'TURNER',
                'SALESMAN',
                7698,
                '8-SEP-81',
                1500,
                0,
                30);

   INSERT INTO emp
        VALUES (7900,
                'JAMES',
                'CLERK',
                7698,
                '3-DEC-81',
                950,
                NULL,
                30);

   INSERT INTO emp
        VALUES (7521,
                'WARD',
                'SALESMAN',
                7698,
                '22-FEB-81',
                1250,
                500,
                30);

   INSERT INTO emp
        VALUES (7902,
                'FORD',
                'ANALYST',
                7566,
                '3-DEC-81',
                3000,
                NULL,
                20);

   INSERT INTO emp
        VALUES (7369,
                'SMITH',
                'CLERK',
                7902,
                '17-DEC-80',
                800,
                NULL,
                20);

   INSERT INTO emp
        VALUES (7788,
                'SCOTT',
                'ANALYST',
                7566,
                '09-DEC-82',
                3000,
                NULL,
                20);

   INSERT INTO emp
        VALUES (7876,
                'ADAMS',
                'CLERK',
                7788,
                '12-JAN-83',
                1100,
                NULL,
                20);

   INSERT INTO emp
        VALUES (7934,
                'MILLER',
                'CLERK',
                7782,
                '23-JAN-82',
                1300,
                NULL,
                10);

   COMMIT;
END;

1 row(s) inserted.

Statement 3

Now I need to track the shoe size of some employees. So I can ask my DBA to add a column for that characteristic, but then what happens when I need another attribute for some other employees? And with each DDL change to the table, there is a chance that users will also have to experience downtime on the deployment of the change.

ALTER TABLE emp   
   ADD shoesize NUMBER

Table altered.

Statement 4

The traditional relational solution, known as "flexfields", is to add, say, ten string columns, 10 number columns, 5 date columns, etc., plus an indicator of which column is being used. And then the fun really begins, with cumbersome, hard to optimize and maintain code.

ALTER TABLE emp ADD /* intentional failure so we don't have to drop these! */ FAIL! 
  ( 
   flex_vc1 VARCHAR2(4000), 
   flex_vc2 VARCHAR2(4000), 
   flex_vc3 VARCHAR2(4000), 
   flex_vc4 VARCHAR2(4000), 
   flex_vc5 VARCHAR2(4000), 
   flex_num1 NUMBER, 
   flex_num2 NUMBER, 
   flex_num3 NUMBER 
  )

ORA-00902: invalid datatype

More Details: https://docs.oracle.com/error-help/db/ora-00902

Statement 5

OK, let's abandon this approach and instead look at an alternative using JSON.

ALTER TABLE emp 
   DROP COLUMN shoesize

Table altered.

Statement 6

We will add a new column, but this time we specify via a check constraint that it is a JSON document. One big CLOB to hold everything. But how do we know what's in there and how do we get our hands on it in our application? Read on!

ALTER TABLE emp 
   ADD flex CLOB CHECK (flex IS JSON)

Table altered.

Statement 7

The JSON document does not have any elements named "shoesize", but the default behavior in SQL JSON is *not* to raise errors in these situations. Instead, NULL is simply passed back.

SELECT e.flex.shoesize 
  FROM emp e


SHOESIZE
 - 
 - 
 - 
 - 
 - 
 - 
 - 
 - 
 - 
 - 
 - 
 - 
 - 
 - 

14 rows selected.

SHOESIZE
-
-
-
-
-
-
-
-
-
-
-
-
-
-

Statement 8

SELECT e.flex.skills
  FROM emp e


SKILLS
 - 
 - 
 - 
 - 
 - 
 - 
 - 
 - 
 - 
 - 
 - 
 - 
 - 
 - 

14 rows selected.

SKILLS
-
-
-
-
-
-
-
-
-
-
-
-
-
-

Statement 9

"xyz" is not valid JSON - this demonstrates one of the key benefits of using the IS JSON constraint.

UPDATE emp 
   SET flex = 'xyz' 
 WHERE empno = 7934

ORA-02290: check constraint (SQL_OMNDCVADQYWQUREHQJRPMBGSV.SYS_C002531338) violated ORA-06512: at "SYS.DBMS_SQL", line 1721

More Details: https://docs.oracle.com/error-help/db/ora-02290

Statement 10

I set up two flexfields for empno 7934: shoeSize (notice the case!) and skills. shoeSize is a single number literal, while skills is an array. This sort of flexibility is exactly what makes JSON a perfect fit for flexfields.

UPDATE emp 
   SET flex = '{"shoeSize":12, "skills":["oracle", "c", "java"]}' 
 WHERE empno = 7934

1 row(s) updated.

Statement 11

UPDATE emp
   SET flex = '{"shoeSize":10.5, "skills":["hiring","firing"]}'
 WHERE empno = 7902

1 row(s) updated.

Statement 12

COMMIT

Statement processed.

Statement 13

JSON names are case-sensitive. So even though in general SQL is case-insensitive, when you want to reference the name of a name-value pair in a JSON document, you must be careful about case.

SELECT e.flex.shoesize 
  FROM emp e


SHOESIZE
 - 
 - 
 - 
 - 
 - 
 - 
 - 
 - 
 - 
 - 
 - 
 - 
 - 
 - 

14 rows selected.

SHOESIZE
-
-
-
-
-
-
-
-
-
-
-
-
-
-

Statement 14

Now with the correct case for the name (shoeSize) we see data!

SELECT e.flex.shoeSize  
  FROM emp e


SHOESIZE
 - 
 - 
 - 
 - 
 - 
 - 
 - 
 - 
 - 
10.5
 - 
 - 
 - 
12

14 rows selected.

SHOESIZE
-
-
-
-
-
-
-
-
-
10.5
-
-
-
12

Statement 15

Notice the display of the two arrays of skills. The rest are NULL.

SELECT e.flex.skills 
  FROM emp e


SKILLS
 - 
 - 
 - 
 - 
 - 
 - 
 - 
 - 
 - 
["hiring","firing"]
 - 
 - 
 - 
["oracle","c","java"]

14 rows selected.

SKILLS
-
-
-
-
-
-
-
-
-
["hiring","firing"]
-
-
-
["oracle","c","java"]

Statement 16

SELECT e.flex.skills[0]
  FROM emp e


SKILLS
 - 
 - 
 - 
 - 
 - 
 - 
 - 
 - 
 - 
hiring
 - 
 - 
 - 
oracle

14 rows selected.

SKILLS
-
-
-
-
-
-
-
-
-
hiring
-
-
-
oracle

Statement 17

I can create views that expose a certain set of known flexfields.

CREATE VIEW empv1 
AS 
   SELECT empno, 
          ename, 
          job, 
          mgr, 
          hiredate, 
          sal, 
          comm, 
          deptno, 
          e.flex.shoeSize "SHOESIZE", 
          e.flex.skills "SKILLS" 
     FROM emp e

View created.

Statement 18

SELECT ename, shoesize, skills 
  FROM empv1 
 WHERE shoesize > 11


ENAME SHOESIZE SKILLS
MILLER    12 ["oracle","c","java"]

ENAME	SHOESIZE	SKILLS
MILLER	12	["oracle","c","java"]

Statement 19

JSON_TABLE allows us to un-nest the JSON array so that individual values become column values. This increases the number of rows and also repeats the values outside of the array. But here we do not see any rows where the column of the flex is null.

SELECT empno,   
       ename,   
       job,   
       mgr,   
       hiredate,   
       sal,   
       comm,   
       deptno,   
       e.flex.shoeSize shoesize,   
       jt.skill   
  FROM emp e, json_table(e.flex , '$.skills[*]' columns (   
         "SKILL" varchar2(20) path '$')) jt


EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO SHOESIZE SKILL
7902 FORD      ANALYST  7566 03-DEC-81 3000  - 20 10.5 hiring
7902 FORD      ANALYST  7566 03-DEC-81 3000  - 20 10.5 firing
7934 MILLER    CLERK    7782 23-JAN-82 1300  - 10 12 oracle
7934 MILLER    CLERK    7782 23-JAN-82 1300  - 10 12 c
7934 MILLER    CLERK    7782 23-JAN-82 1300  - 10 12 java

5 rows selected.

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO	SHOESIZE	SKILL
7902	FORD	ANALYST	7566	03-DEC-81	3000	-	20	10.5	hiring
7902	FORD	ANALYST	7566	03-DEC-81	3000	-	20	10.5	firing
7934	MILLER	CLERK	7782	23-JAN-82	1300	-	10	12	oracle
7934	MILLER	CLERK	7782	23-JAN-82	1300	-	10	12	c
7934	MILLER	CLERK	7782	23-JAN-82	1300	-	10	12	java

Statement 20

Notice that with an OUTER JOIN, we can also how see those rows which do not have a flex field value set.

SELECT empno,  
       ename,  
       job,  
       mgr,  
       hiredate,  
       sal,  
       comm,  
       deptno,  
       emp.flex.shoeSize "SHOESIZE",  
       jt.skill  
  FROM emp emp LEFT OUTER JOIN json_table(emp.flex , '$.skills[*]' columns (  
         "SKILL" varchar2(20) path '$')) jt  
       ON (1=1)


EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO SHOESIZE SKILL
7839 KING      PRESIDENT  - 17-NOV-81 5000  - 10  -  - 
7698 BLAKE     MANAGER  7839 01-MAY-81 2850  - 30  -  - 
7782 CLARK     MANAGER  7839 09-JUN-81 2450  - 10  -  - 
7566 JONES     MANAGER  7839 02-APR-81 2975  - 20  -  - 
7654 MARTIN    SALESMAN 7698 28-SEP-81 1250 1400 30  -  - 
7499 ALLEN     SALESMAN 7698 20-FEB-81 1600 300 30  -  - 
7844 TURNER    SALESMAN 7698 08-SEP-81 1500 0 30  -  - 
7900 JAMES     CLERK    7698 03-DEC-81 950  - 30  -  - 
7521 WARD      SALESMAN 7698 22-FEB-81 1250 500 30  -  - 
7902 FORD      ANALYST  7566 03-DEC-81 3000  - 20 10.5 hiring
7902 FORD      ANALYST  7566 03-DEC-81 3000  - 20 10.5 firing
7369 SMITH     CLERK    7902 17-DEC-80 800  - 20  -  - 
7788 SCOTT     ANALYST  7566 09-DEC-82 3000  - 20  -  - 
7876 ADAMS     CLERK    7788 12-JAN-83 1100  - 20  -  - 
7934 MILLER    CLERK    7782 23-JAN-82 1300  - 10 12 oracle
7934 MILLER    CLERK    7782 23-JAN-82 1300  - 10 12 c
7934 MILLER    CLERK    7782 23-JAN-82 1300  - 10 12 java

17 rows selected.

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO	SHOESIZE	SKILL
7839	KING	PRESIDENT	-	17-NOV-81	5000	-	10	-	-
7698	BLAKE	MANAGER	7839	01-MAY-81	2850	-	30	-	-
7782	CLARK	MANAGER	7839	09-JUN-81	2450	-	10	-	-
7566	JONES	MANAGER	7839	02-APR-81	2975	-	20	-	-
7654	MARTIN	SALESMAN	7698	28-SEP-81	1250	1400	30	-	-
7499	ALLEN	SALESMAN	7698	20-FEB-81	1600	300	30	-	-
7844	TURNER	SALESMAN	7698	08-SEP-81	1500	0	30	-	-
7900	JAMES	CLERK	7698	03-DEC-81	950	-	30	-	-
7521	WARD	SALESMAN	7698	22-FEB-81	1250	500	30	-	-
7902	FORD	ANALYST	7566	03-DEC-81	3000	-	20	10.5	hiring
7902	FORD	ANALYST	7566	03-DEC-81	3000	-	20	10.5	firing
7369	SMITH	CLERK	7902	17-DEC-80	800	-	20	-	-
7788	SCOTT	ANALYST	7566	09-DEC-82	3000	-	20	-	-
7876	ADAMS	CLERK	7788	12-JAN-83	1100	-	20	-	-
7934	MILLER	CLERK	7782	23-JAN-82	1300	-	10	12	oracle
7934	MILLER	CLERK	7782	23-JAN-82	1300	-	10	12	c
7934	MILLER	CLERK	7782	23-JAN-82	1300	-	10	12	java

Statement 21

When we create a view on top of that JSON logic, then all values from the JSON column can be presented as SQL values. A user of the view doesn't need to know that the data comes from a JSON document containing all sorts of different values.

CREATE VIEW empv2   
AS   
   SELECT empno,   
          ename,   
          job,   
          mgr,   
          hiredate,   
          sal,   
          comm,   
          deptno,   
          e.flex.shoeSize "SHOESIZE",   
          jt.skill   
     FROM emp e LEFT OUTER JOIN json_table(e.flex , '$.skills[*]' columns (   
         "SKILL" varchar2(20) path '$')) jt   
       ON (1=1)

View created.

Statement 22

SELECT ename
  FROM empv2
 WHERE skill = 'oracle'


ENAME
MILLER

ENAME
MILLER

Statement 23

PL/SQL support for JSON in 12.2 allows us to quickly look for (and return) a value from the JSON data.

CREATE OR REPLACE FUNCTION getfield (flex IN CLOB, name IN VARCHAR)  
   RETURN VARCHAR  
IS  
   val   VARCHAR2 (4000);  
   o     json_object_t;  
BEGIN  
   IF flex IS NULL  
   THEN  
      RETURN NULL;  
   END IF;  
  
   o := NEW json_object_t (flex);  
   val := o.get_string (name);  
   RETURN val;  
END;

Function created.

Statement 24

SELECT ename, getfield (flex, 'shoeSize')
  FROM emp
 WHERE flex IS NOT NULL


ENAME GETFIELD(FLEX,'SHOESIZE')
FORD      10.5
MILLER    12

2 rows selected.

ENAME	GETFIELD(FLEX,'SHOESIZE')
FORD	10.5
MILLER	12

Statement 25

You cannot directly update JSON documents in SQL; you must instead (as of 12.2) use PL/SQL APIs implemented in object types. Here is a function that accepts a JSON document, updates the specified name-value pair, and then returns it.

CREATE OR REPLACE FUNCTION setfield (flex       IN CLOB,  
                                     name       IN VARCHAR2,  
                                     newvalue   IN VARCHAR2)  
   RETURN CLOB  
IS  
   o   json_object_t;  
   s   VARCHAR2 (4000);  
BEGIN  
   IF flex IS NULL  
   THEN  
      o := NEW json_object_t;  
   ELSE  
      o := NEW json_object_t (flex);  
   END IF;  
  
   o.put (name, newvalue);  
   s := o.to_string;  
   RETURN s;  
END;

Function created.

Statement 26

And then the function can be called in SQL.

UPDATE emp 
   SET flex = setfield (flex, 'id', empno)

14 row(s) updated.

Statement 27

SELECT * FROM emp


EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO FLEX
7839 KING      PRESIDENT  - 17-NOV-81 5000  - 10 {"id":"7839"}
7698 BLAKE     MANAGER  7839 01-MAY-81 2850  - 30 {"id":"7698"}
7782 CLARK     MANAGER  7839 09-JUN-81 2450  - 10 {"id":"7782"}
7566 JONES     MANAGER  7839 02-APR-81 2975  - 20 {"id":"7566"}
7654 MARTIN    SALESMAN 7698 28-SEP-81 1250 1400 30 {"id":"7654"}
7499 ALLEN     SALESMAN 7698 20-FEB-81 1600 300 30 {"id":"7499"}
7844 TURNER    SALESMAN 7698 08-SEP-81 1500 0 30 {"id":"7844"}
7900 JAMES     CLERK    7698 03-DEC-81 950  - 30 {"id":"7900"}
7521 WARD      SALESMAN 7698 22-FEB-81 1250 500 30 {"id":"7521"}
7902 FORD      ANALYST  7566 03-DEC-81 3000  - 20 {"shoeSize":10.5,"skills":["hiring","firing"],"id":"7902"}
7369 SMITH     CLERK    7902 17-DEC-80 800  - 20 {"id":"7369"}
7788 SCOTT     ANALYST  7566 09-DEC-82 3000  - 20 {"id":"7788"}
7876 ADAMS     CLERK    7788 12-JAN-83 1100  - 20 {"id":"7876"}
7934 MILLER    CLERK    7782 23-JAN-82 1300  - 10 {"shoeSize":12,"skills":["oracle","c","java"],"id":"7934"}

14 rows selected.

Using JSON for Flexfields

Additional Information