18c Treat as Json

从18C开始，对json数据进行了很多增强，其中之一就是treat as json表达式。可以将带有关键字AS JSON的SQL函数treat应用于SQL表达式，以指定该表达式返回JSON数据。 这在Oracle无法确定结果为JSON数据的情况下很有用。 在要强制将某些文本解释为JSON数据的情况下，它也很有用。 例如，您可以使用它告诉Oracle将{}的VARCHAR2值解释为不是字符串，而是解释为空的JSON对象。

treat as json的语法：

oracle从12c开始支持了json的语法，具体可以参考之前的文章：JSON in Oracle

创建测试表

这里创建一张测试表，并插入一些JSON格式数据，注意现在是并没有加IS JSON约束的情况。

drop table json_test purge;

create table json_test (
    id raw(16) not null,
    document clob,
    CONSTRAINT pk_json_test primary key (id)
);

insert into json_test values (SYS_GUID(), '{"name": "xb", "sex": "male"}');
insert into json_test values (SYS_GUID(), '{"name": "ting", "sex": "female"}');
insert into json_test values (SYS_GUID(), '{"name": "lei", "sex": "male"}');
commit;

常规查询JSON数据

当列中并没有包含IS JSON的约束时，oracle并不知晓里面存在的数据是JSON格式的数据，而当我们用查询JSON的语法去查询数据时，sql是会报错的。

select a,id,a.document.name,a.document.sex
from json_test a
order by 2;

select a,id,a.document.name,a.document.sex
                            *
ERROR at line 1:
ORA-00904: "A"."DOCUMENT"."SEX": invalid identifier

使用Treat AS JSON内联视图

通过内联视图先将document字段当成json数据，再通过json语法进行查询。在原来的字段没有使用IS JSON约束的情况下，外层查询也可以使用点表示法。

col name for a30
col sex for a30
select a.id,a.data.name,a.data.sex
from (
select id, treat(document as json) as data
from json_test) a
order by 2;

ID                               NAME                           SEX
-------------------------------- ------------------------------ ------------------------------
BED184B8FC52F039E055FBD2D67647F4 lei                            male
BED184B8FC51F039E055FBD2D67647F4 ting                           female
BED184B8FC50F039E055FBD2D67647F4 xb                             male

使用Treat AS JSON和WITH从句

col name for a30
col sex for a30
with t as
 (select id, treat(document as json) as data from json_test)
select a.id, a.data.name, a.data.sex from t a order by 2;

ID                               NAME                           SEX
-------------------------------- ------------------------------ ------------------------------
BED184B8FC52F039E055FBD2D67647F4 lei                            male
BED184B8FC51F039E055FBD2D67647F4 ting                           female
BED184B8FC50F039E055FBD2D67647F4 xb                             male

使用视图

create or replace view v_json as
select id, treat(document as json) as data
from json_test;

select a.id,a.data.name,a.data.sex
from v_json a
order by 2;

ID                               NAME                           SEX
-------------------------------- ------------------------------ ------------------------------
BED184B8FC52F039E055FBD2D67647F4 lei                            male
BED184B8FC51F039E055FBD2D67647F4 ting                           female
BED184B8FC50F039E055FBD2D67647F4 xb                             male

Treat AS JSON错误处理

在使用treat as json语法时，插入的数据并没有进行json格式的约束，就会导致列中存在一些并不是JSON格式的数据，而treat as json也不会去做校验。

insert into json_test values (SYS_GUID(), 'name: xb, sex: male');
commit;

col json_data for a80
select id,treat(document as json) json_data
from json_test;

ID                               JSON_DATA
-------------------------------- --------------------------------------------------------------------------------
BED184B8FC50F039E055FBD2D67647F4 {"name": "xb", "sex": "male"}
BED184B8FC51F039E055FBD2D67647F4 {"name": "ting", "sex": "female"}
BED184B8FC52F039E055FBD2D67647F4 {"name": "lei", "sex": "male"}
BED184B8FC56F039E055FBD2D67647F4 name: xb, sex: male

这时如果跟之前一样通过json语法的点表示法来查询数据时，不符合JSON格式的数据就会返回NULL

col name for a30
col sex for a30
select a.id,a.data.name,a.data.sex
from (
select id, treat(document as json) as data
from json_test) a
order by 2;

ID                               NAME                           SEX
-------------------------------- ------------------------------ ------------------------------
BED184B8FC52F039E055FBD2D67647F4 lei                            male
BED184B8FC51F039E055FBD2D67647F4 ting                           female
BED184B8FC50F039E055FBD2D67647F4 xb                             male
BED184B8FC56F039E055FBD2D67647F4

可以通过加IS JSON的条件来过滤掉这些NULL数据

col name for a30
col sex for a30
select a.id,a.data.name,a.data.sex
from (
select id, treat(document as json) as data
from json_test) a
where a.data is json
order by 2;

ID                               NAME                           SEX
-------------------------------- ------------------------------ ------------------------------
BED184B8FC52F039E055FBD2D67647F4 lei                            male
BED184B8FC51F039E055FBD2D67647F4 ting                           female
BED184B8FC50F039E055FBD2D67647F4 xb                             male