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约束的情况。

1
2
3
4
5
6
7
8
9
10
11
12
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是会报错的。

1
2
3
4
5
6
7
8
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约束的情况下,外层查询也可以使用点表示法。

1
2
3
4
5
6
7
8
9
10
11
12
13
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从句

1
2
3
4
5
6
7
8
9
10
11
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

使用视图

1
2
3
4
5
6
7
8
9
10
11
12
13
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也不会去做校验。

1
2
3
4
5
6
7
8
9
10
11
12
13
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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
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数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
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
作者

xbdba

发布于

2021-03-31

更新于

2022-10-31

许可协议

评论