JSON 系列之2：JSON简单查询

本文为Oracle数据库JSON学习系列的第2篇，讲述如何对存储在数据库中的JSON文档进行简单的查询。

创建测试表，插入2条数据：

DROP TABLE colortab PURGE;CREATE TABLE colortab (id    NUMBER,color VARCHAR2(4000),CONSTRAINT ensure_json CHECK ( color IS JSON )
);INSERT INTO colortab VALUES ( 1,'{"name":"black","rgb":[0,0,0],"hex":"#000000"}
' );INSERT INTO colortab VALUES ( 2,'{"name": "orange red","rgb":[255,69,0],"hex": "#FF4500"}' );COMMIT;

查询使用dot notation即可，非常直观：

SQL> SELECT c.color.name from colorTab c;NAME      
----------
black
orange redSQL> SELECT c.color.rgb from colorTab c;RGB       
----------
[0,0,0]
[255,69,0]SQL> SELECT c.color.rgb[0] from colorTab c;RGB       
----------
0
255

可以看到，对于标量和数组都可以查询，数组的第一个元素索引为0。

注意，这个别名是必须的，否则报错：

SQL> SELECT color.name from colorTab;在行: 3 上开始执行命令时出错 -
SELECT color.name from colorTab
错误位于命令行: 3 列: 8
错误报告 -
SQL 错误: ORA-00904: "COLOR"."NAME": 标识符无效https://docs.oracle.com/error-help/db/ora-00904/00904. 00000 -  "%s: invalid identifier"
*Cause:    The identifier or column name entered was invalid.
*Action:   Ensure the followingMore Details :
https://docs.oracle.com/error-help/db/ora-00904/

对于表中一个不存在的列，SELECT会报错；但对于JSON文档中一个不存在的域，则不会报错，返回null。这正是 flexible schema 所需要的。

SQL> SELECT c.color.nofield from colorTab c;NOFIELD   
----------已选择 2 行。

如果你希望报错，可以修改初始化参数json_behavior，默认值是ON_ERROR:NULL：

SQL> alter session set json_behavior='on_error:error';Session已变更。SQL> SELECT c.color.nofield from colorTab c;在行: 5 上开始执行命令时出错 -
SELECT c.color.nofield from colorTab c
错误位于命令行: 5 列: 29
错误报告 -
SQL 错误: ORA-40462: JSON 路径 '$.nofield' 的求值结果为无值。https://docs.oracle.com/error-help/db/ora-40462/40462. 00000 -  "JSON path '%s' evaluated to no value."
*Cause:    The specified JavaScript Object Notation (JSON) path expressiondid not select a value.
*Action:   Correct the JSON path expression.More Details :
https://docs.oracle.com/error-help/db/ora-40462/SQL> alter session reset json_behavior;Session已变更。

参考

• First Steps with JSON: Simple Queries