Pretty JSON in 21c

Oracle 21c now enables JSON as a datatype

12.2, 18c, 19c:

SQL> SELECT json_object(*)
from scott.emp
where ename='SCOTT';

JSON_OBJECT(*)
--------------
{"EMPNO":7788,"ENAME":"SCOTT","JOB":"ANALYST","MGR":7566,"HIREDATE":"1987-04-19T00:00:00","SAL":3000,"COMM":null,"DEPTNO":20}

21c:

SQL> SELECT json_object(* returning json)
from scott.emp
where ename='SCOTT';

JSON_OBJECT(*RETURNINGJSON)
---------------------------
{"EMPNO":7788,"ENAME":"SCOTT","JOB":"ANALYST","MGR":7566,"HIREDATE":"1987-04-19T00:00:00","SAL":3000,"COMM":null,"DEPTNO":20}

Ok, it looks similar, but it’s a no longer a string (varchar2 or clob), it is a json object.

SQL> create table t(j json);
SQL> insert into t values('{"x":1}');
SQL> select t.j.x from t t;

X
----------
1


SQL> desc t

 Name              Null?    Type
 ----------------- -------- ------------
 J                          JSON

What’s more, sqlplus can prettyprint the json


SQL> set jsonprint xxx
SP2-0158: unknown SET option "xxx"
Usage: SET JSONPRINT {NORMAL | PRETTY | ASCII}
SQL> set jsonpr pret
SQL> sho jsonpr
jsonprint PRETTY
SQL> SELECT json_object(* returning json) from scott.emp where ename='SCOTT';

JSON_OBJECT(*RETURNINGJSON)
--------------------------------------------------
{
  "EMPNO" : 7788,
  "ENAME" : "SCOTT",
  "JOB" : "ANALYST",
  "MGR" : 7566,
  "HIREDATE" : "1987-04-19T00:00:00",
  "SAL" : 3000,
  "COMM" : null,
  "DEPTNO" : 20
}

By Laurent Schneider

Oracle Certified Master

Leave a comment

Your email address will not be published.