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
}