【博客文章2024】Oracle数据库中的JSON数据介绍
Author: Bo Tang
1. JSON是什么：
JSON是JavaScript对象表示法 (JavaScript Object Notation）的简称。 JSON数据都是由一行行的键值对组成。每个 键值对中的两端用分号“:”分割开。两端的内容都要用双引号括起来。不能有重复的键值对。 JSON数据最外层必须由“{}” 包裹。 为了展示包裹层次， JSON数据中只会出现中括号“[]”和花括号“{}”。中括号“[]”包裹花括号“{}” 。中括号“[]”表示数组。 为了可读性，建议下一层括号比上一层括号统一缩进两个字符，比如下面这个书店销售订单的例子。这个例子依次按照如下顺序：红色-黄色-绿色-灰色，来展示4层包裹关系。
2. JSON的用途：
它是互联网应用之间信息交换的主要数据格式。由于它比XML简单得多的，但同时又能准确无误地描述清楚半结构化和非结构化数据，这几年，它大有取代XML的趋势。我们可以在许多地方找到JSON，比如在自动化运维Ansible的yml文件中，又比如在Oracle Apex的输出中，再比如在AI运行框架Ollama的输入中。
3. 如何将JSON数据存储在Oracle RDBMS中：
从Oracle Database 12c开始，Oracle就添加了在数据库内部存储JSON的支持。也添加了对其进行查询以及为其编制索引的支持。新的check语法允许添加JSON检查约束，这种约束使得数据库强制JSON数据遵循JSON规则。下面举例说明：首先创建一个表，用以存放某个书店的销售订单，其中的销售订单详情是JSON数据：
[oracle@station1 ~]$ sqlplus /nolog
SQL*Plus: Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems on Tue Dec 31 00:18:10 2024
Version 23.5.0.24.07
Copyright (c) 1982, 2024, Oracle. All rights reserved.
SQL> conn hr/cloud_4U@pdb1_1
Connected.
SQL> create table j1 (id number(3) not null,
2 doc clob check ( doc is json));
Table created.
|
查询约束信息：
SELECT constraint_name, constraint_type, search_condition
FROM user_constraints
WHERE table_name='J1';
| CONSTRAINT_NAME |
CONSTRAINT_TYPE |
SEARCH_CONDITION |
| SYS_C0012897 |
C |
"ID" IS NOT NULL |
| SYS_C0012898 |
C |
doc is json |
|
插入一行JSON数据：
INSERT INTO j1 VALUES
(1,
'{
"Number":1,
"Reference":"赵先生-20241201",
"Requestor":"赵先生",
"User":"SIRZHAO",
"CostCenter":"A",
"ShippingInstructions":
{
"name":"赵先生",
"Address":
{
"street":"第1街道",
"city":"哈尔滨",
"province":"黑龙江",
"zipCode":150000,
"country":"中国"
},
"Phone":
[
{
"type":"固定电话",
"number":"0451-86779817"
}
]
},
"Special Instructions":"平寄邮件",
"LineItems":
[
{
"ItemNumber":1,
"Part":
{
"Description":"《哲学与社会——老年沉思录》",
"UnitPrice":19.95,
"UPCCode":43396040144
},
"Quantity":7.0
},
{
"ItemNumber":2,
"Part":
{
"Description":"《陈平原文集》",
"UnitPrice":19.95,
"UPCCode":43396040145
},
"Quantity":7.0
},
{
"ItemNumber":3,
"Part":
{
"Description":"《蘑菇猎人：探寻北美野生蘑菇的地下世界》",
"UnitPrice":19.95,
"UPCCode":12236101345
},
"Quantity":1.0
},
{
"ItemNumber":4,
"Part":
{
"Description":"《楼兰新史（增订本）》",
"UnitPrice":19.95,
"UPCCode":85391756323
},
"Quantity":8.0
},
{
"ItemNumber":5,
"Part":
{
"Description":"《人类思想发展史：关于古代近东思辨思想的讨论》",
"UnitPrice":19.95,
"UPCCode":13023009592
},
"Quantity":8.0
},
{
"ItemNumber":6,
"Part":
{
"Description":"《韩家往事》",
"UnitPrice":19.95,
"UPCCode":27616864451
},
"Quantity":8.0
}
]
}'
);
commit;
|
4.使用JSON运算符查询JSON数据：
4.1 确认以上插入的数据是JSON数据：
SQL> conn hr/cloud_4U@pdb1_1
Connected.
SQL> select count(*) from j1 where doc is json;
COUNT(*)
----------
1
|
如果数据不是JSON数据，“where XXX is json”语法能够很好地识别：
SQL> insert into j1 values(2, null);
1 row created.
SQL> commit;
Commit complete.
SQL> select count(*) from j1 where doc is json;
COUNT(*)
----------
1
SQL> select count(*) from j1 ;
COUNT(*)
----------
2
|
让我们试验一下，什么样的数据算是最简单的JSON数据？
SQL> insert into j1 values(3,'{}');
1 row created.
SQL> commit;
Commit complete.
SQL> select id, count(*) from j1 where doc is json group by id;
ID COUNT(*)
---------- ----------
1 1
3 1
|
看来，只要满足前面所说的“JSON数据最外层必须由“{}” 包裹”这个基本条件，数据就会被认定为JSON数据。 再让我们试验一下，JSON约束的作用：
SQL> insert into j1 values(4,'AAAA') ;
insert into j1 values(4,'AAAA')
*
ERROR at line 1:
ORA-02290: check constraint (HR.SYS_C0012900) violated
Help: https://docs.oracle.com/error-help/db/ora-02290/
|
看来JSON约束是起到作用的：如果插入的不是JSON数据，（除非插入空值），就会报错。空值代表“不确定”，约束是不会对空值作限制的。
4.2 使用路径表达式从JSON数据中选择一个标量值： 查询JSON数据需要使用JSON运算符，例如“IS JSON” 、“JSON_VALUE”、 “JSON_QUERY” 、“JSON_EXISTS”或“JSON_TABLE”。其中最常用的是“JSON_VALUE”。使用“JSON_VALUE”时，不可避免地，要使用路径表达式从JSON数据中选择一个标量值。
SELECT JSON_VALUE (doc, '$.Number') NUM,
JSON_VALUE (doc, '$.Reference') Ref,
JSON_VALUE (doc, '$.Requestor') Requestor,
JSON_VALUE (doc, '$.User') account_name,
JSON_VALUE (doc, '$.CostCenter') CC,
JSON_VALUE (doc, '$."Special Instructions"')
FROM j1 where id=1;
| NUM |
REF |
REQUESTOR |
ACCOUNT_NAME |
CC |
JSON_VALUE(DOC,'$."SPECIALINSTRUCTIONS"') |
|---|
| 1 |
赵先生-20241201 |
赵先生 |
SIRZHAO |
A |
平寄邮件 |
|
请注意：虽然SQL语句不分大小写，但是JSON键值在路径表达式已经用单引号括起来了，要明确区分大小下：
SELECT JSON_VALUE (doc, '$.Number') NUM,
JSON_VALUE (doc, '$.Reference') Ref,
JSON_VALUE (doc, '$.Requestor') Requestor,
JSON_VALUE (doc, '$.User') account_name,
JSON_VALUE (doc, '$.CostCenter') CC,
JSON_VALUE (doc, '$."special Instructions"')
FROM j1 where id=1;
| NUM |
REF |
REQUESTOR |
ACCOUNT_NAME |
CC |
JSON_VALUE(DOC,'$."SPECIALINSTRUCTIONS"') |
|---|
| 1 |
赵先生-20241201 |
赵先生 |
SIRZHAO |
A |
|
|
“JSON_VALUE”返回的只能是标量值。 BFILE是标量值，所以“JSON_VALUE”支持返回BFILE访问文件系统中的文件。在这种情况下，文件内容必须为Unicode。 CLOB是标量值，所以“JSON_VALUE”支持CLOB，但是它以UTF16/UCS2格式存储值，而不是UTF8，这样会导致返回字节数加倍。 “JSON_VALUE”将在需要时自动执行字符集转换。JSON查询引擎支持编码为UTF8和UTF16的Unicode。所以想要避免“JSON_VALUE”转换和乱码，数据库字符集请尽量选择AL32UTF8，如下：
select * from database_properties where property_name like '%CHAR%';
| PROPERTY_NAME |
PROPERTY_VALUE |
DESCRIPTION |
|---|
| NLS_NCHAR_CONV_EXCP |
FALSE |
NLS conversion exception |
| NLS_NUMERIC_CHARACTERS |
., |
Numeric characters |
| NLS_NCHAR_CHARACTERSET |
AL16UTF16 |
NCHAR Character set |
| NLS_CHARACTERSET |
AL32UTF8 |
Character set |
|
|
