JSON in Oracle

JSON（JavaScript Object Notation，JavaScript对象表示法，读作/ˈdʒeɪsən/）是一种由道格拉斯·克罗克福特构想和设计、轻量级的数据交换语言，该语言以易于让人阅读的文字为基础，用来传输由属性值或者序列性的值组成的数据对象。尽管JSON是JavaScript的一个子集，但JSON是独立于语言的文本格式，并且采用了类似于C语言家族的一些习惯。

JSON 数据格式与语言无关。即便它源自JavaScript，但当前很多编程语言都支持 JSON 格式数据的生成和解析。

相对于传统的关系型数据库，一些基于文档存储的NoSQL非关系型数据库选择JSON作为其数据存储格式，比较出名的产品有：MongoDB。

从12.1版本开始，oracle开始支持JSON格式数据，包括事务、索引和视图等。

创建包含JSON数据的表

oracle并没有增加新的字段类型来专门存放JSON数据，而是将其存放在传统的VARCHAR2或CLOB字段中。另外IS JSON约束表明这个字段包含有效的JSON格式数据。

创建一张表，包含判断数据是否为JSON格式的约束

drop table json_test purge;

create table json_test (
    id raw(16) not null,
    document clob
    CONSTRAINT ensure_json CHECK (document IS JSON)
);

这里的IS JSON约束默认情况下都代表弱约束，如果要做严格JSON语法约束，则换成document IS JSON(STRICT)

插入测试的JSON数据

INSERT INTO json_test
  VALUES (SYS_GUID(),
          '{ "PONumber"             : 1600,
          "Reference"            : "ABULL-20140421",
          "Requestor"            : "Alexis Bull",
          "User"                 : "ABULL",
          "CostCenter"           : "A50",
          "ShippingInstructions" : { "name"   : "Alexis Bull",
                                     "Address": { "street"  : "200 Sporting Green",
                                                  "city"    : "South San Francisco",
                                                  "state"   : "CA",
                                                  "zipCode" : 99236,
                                                  "country" : "United States of America" },
                                     "Phone" : [ { "type" : "Office", "number" : "909-555-7307" },
                                                 { "type" : "Mobile", "number" : "415-555-1234" } ] },
          "Special Instructions" : null,
          "AllowPartialShipment" : false,
          "LineItems"            : [ { "ItemNumber" : 1,
                                       "Part"       : { "Description" : "One Magic Christmas",
                                                        "UnitPrice"   : 19.95,
                                                        "UPCCode"     : 13131092899 },
                                       "Quantity"   : 9.0 },
                                     { "ItemNumber" : 2,
                                       "Part"       : { "Description" : "Lethal Weapon",
                                                        "UnitPrice"   : 19.95,
                                                        "UPCCode"     : 85391628927 },
                                       "Quantity"   : 5.0 } ] }');

如果更新数据为无效的JSON数据时，则会出现错误，这里假设去掉一个大括号

update json_test
    set document = '{"User" : "xb"'
where document.User = 'ABULL';

               *
ERROR at line 3:
ORA-01747: invalid user.table.column, table.column, or column specification

查询JSON数据

虽然例子中JSON数据是存放在CLOB字段当中，但是查询的返回值总是VARCAHR2类型

查询PONumber

SQL> col PONumber for a50
SQL> select a.id,a.document.PONumber from json_test a;

ID                               PONUMBER
-------------------------------- --------------------------------------------------
9AAB58F9BDD3DA0AE0536892A8C06893 1600

查询装运须知的Phone

SQL> col ShippingInstructions for a100
SQL> select a.document.ShippingInstructions.Phone from json_test a;

SHIPPINGINSTRUCTIONS
----------------------------------------------------------------------------------------------------
[{"type":"Office","number":"909-555-7307"},{"type":"Mobile","number":"415-555-1234"}]

查询phone的类型

SQL> select a.document.ShippingInstructions.Phone.type from json_test a;

SHIPPINGINSTRUCTIONS
----------------------------------------------------------------------------------------------------
["Office","Mobile"]

JSON字符集

文本化的JSON数据通常采用UNICODE字符集，从这个方面来说JSON数据比XML数据使用起来更简单，这是JSON数据内部交换格式的重要组成部分。对于Oracle中管理的JSON数据而言，所有需要进行字符集转换的操作都是自动进行。

Oracle内部使用UTF-8来操作JSON数据，包括解析、查询等。如果这些数据被传入或者被输出，必须要以非UTF-8的字符集时，则会自动做字符集之间的转换。

字符集自动转换会影响性能。并且在某些情况下会有信息丢失，将传入的数据转换成UTF-8这段过程是没有丢失的，但是转换成输出时如果在输出的字符集中无法显示字符的话则会丢失数据。

当数据库的字符集为Oracle建议的AL32UTF8时,如果文本化的JSON数据以UNICODE格式储存在数据库当中时则不需要做字符集的转换。

不以文本格式存储的JSON数据，永远不需要字符集转换：因为没有字符用来转换，这表示使用BLOB类型存放的JSON数据不需要经历字符集转换。

如果JSON数据是以非Unicode格式存放，使用非Unicode的VARCHAR2或CLOB字段，则需要考虑下面的方法去避免字符集的转换

• 对于Oracle SQL函数返回的值使用NUMBER而不是VARCHAR2,例如json_value
• 在输入时转义特定的Unicode字符

使用JSON的SQL函数和条件

Oracle提供了SQL函数和条件去创建、查询、操作JSON数据，其中一些将JSON路径表达式中作为参数，后面跟着RETURNING子句、WRAPPER子句或者error子句。

• Oracle SQL Conditions IS JSON and IS NOT JSON 测试数据是否是符合语法的JSON数据，通常作为约束检查
• Oracle SQL Condition JSON_EXISTS 测试JSON数据里是否存在特定的值
• Oracle SQL Function JSON_VALUE 从JSON数据中选择一个标量值作为SQL值
• Oracle SQL Function JSON_QUERY 从JSON数据中选择一个或多个值作为SQL串来表示JSON值，通常用来获取JSON文本的片段，特别是JSON对象或数组
• Oracle SQL Function JSON_TABLE 将JSON数据映射成关系型的虚拟表，也可以认为是内联的关系型视图

JSON函数和条件中的子句

子句包含returning、wrapper、和错误处理。每个子句都可以使用在一个或多个SQL函数和条件json_value, json_query, json_table, is json, is not json, and json_exists.

json_value和json_query允许可选的RETURNING子句，表示通过函数返回数据的类型。

对于json_value来说，可以允许VARCAHR2或者NUMBER类型返回值，而对于json_query，只能使用VARCHAR2。

json_value和json_query允许可选的WRAPPER子句，表示通过json_query返回值或json_table相关列的格式

• WITH WRAPPER-用一个字符值表示包含所有JSON数据的数组，数组元素顺序不要求。
• WITHOUT WRAPPER– 用一个字符表示单个JSON对象或者数组，如果路径表达式匹配上一个标量值或者多个值则会报错。
• WITH CONDITIONAL WRAPPER-对于0个值、单个标量值或多个值与WITH WRAPPER一样，对于单个的JSON对象或数组值，则与WITHOUT WRAPPER一样。

默认是WITHOUT WRAPPER

JSON_QUERY Wrapper子句

JSON Values	WITH WRAPPER	WITHOUT WRAPPER	WITH CONDITIONAL WRAPPER
{"id": 38327} (single object)	[{"id": 38327}]	{"id": 38327}	{"id": 38327}
[42, "a", true] (single array)	[[42, "a", true]]	[42, "a", true]	[42, "a", true]
42	[42]	Error (scalar)	[42]
42, "a", true	[42, "a", true]	Error (multiple values)	[42, "a", true]
none	[]	Error (no values)	[]

IS JSON and IS NOT JSON

之前的例子中是将IS JSON写在了约束中，但同时也可以写在查询条件里

drop table json_test purge;

 create table json_test 
 (
 id raw(16) not null,
 data clob,
constraint pk_json primary key(id));

insert into json_test values(sys_guid(), '{"name": "xb"}');
insert into json_test values(sys_guid(), 'dba');
commit;

# JSON_VALUE 使用NULL ON ERROR，对于非JSON数据返回NULL
col name for a10
select id,json_value(a.data, '$.name') as name from json_test a;

ID                               NAME
-------------------------------- ----------
9AFA0E321B112246E0536892A8C0BFD5 xb
9AFA0E321B122246E0536892A8C0BFD5

# 只返回是否包含JSON数据的行
select id,json_value(a.data, '$.name') as name 
from json_test a
where data is json;

ID                               NAME
-------------------------------- ----------
9AFA0E321B112246E0536892A8C0BFD5 xb

select id,json_value(a.data, '$.name') as name 
from json_test a
where data is not json;

ID                               NAME
-------------------------------- ----------
9AFA0E321B122246E0536892A8C0BFD5

JSON_EXISTS

当通过点记法去查询JSON时，不可能分辨出缺失的元素和已有的元素之间的区别，除了一个NULL值以外。以前面第一个例子为例，用户ABULL有一个为NULL的 “Special Instructions”元素，并且没有ADDRESS元素，当通过点记法去查询json_test表时都是返回NULL，而无法判断ADDRESS是不存在的，语句并不会报错。

col user for a10
col PONumber for a20
col Sins for a20
col address for a20
SELECT a.document."User",
       a.document.PONumber,
       a.document."Special Instructions" AS Sins,
       a.document.address AS address
FROM   json_test a
WHERE  a.document."Special Instructions" IS NULL
AND    a.document.address IS NULL;

User       PONUMBER             SINS                 ADDRESS
---------- -------------------- -------------------- --------------------
ABULL      1600

JSON_EXISTS可以分辨出空值和缺失对象的区别

# 查询"Special Instructions"元素存在，但是值为null

SELECT a.document."User",
       a.document.PONumber,
       a.document."Special Instructions" AS Sins
FROM   json_test a
WHERE  a.document."Special Instructions" IS NULL
  and json_exists(a.document, '$."Special Instructions"' FALSE on error);

User       PONUMBER             SINS
---------- -------------------- --------------------
ABULL      1600

# 查询address元素不存在的数据

SELECT a.document."User",
       a.document.PONumber,
       a.document."Special Instructions" AS Sins
FROM   json_test a
WHERE  a.document."Special Instructions" IS NULL
  and not json_exists(a.document, '$.address' FALSE on error)

User       PONUMBER             SINS
---------- -------------------- --------------------
ABULL      1600

默认错误控制为FALSE ON ERROR,另外包含TRUE ON ERROR和ERROR ON ERROR，后者顾名思义表示不会获取任何通过JSON_EXISTS函数生成的错误。

JSON_VALUE

JSON_VALUE函数返回基于JSON路径查询JSON文档的元素

col user for a30
col costcenter for a30

select json_value(a.document, '$."User"') as "user",json_value(a.document, '$.CostCenter') as costcenter from json_test a;

user                           COSTCENTER
------------------------------ ------------------------------
ABULL                          A50

json_value只会返回标量的值，并不会返回嵌套值或数组。默认的错误处理为NULL或ERROR，所以尝试返回非标量值时则返回NULL

col ShippingInstructions for a100
select json_value(a.document, '$.ShippingInstructions') as ShippingInstructions  from json_test a;

SHIPPINGINSTRUCTIONS
----------------------------------------------------------------------------------------------------


# 自定义错误控制
SQL> select json_value(a.document, '$.ShippingInstructions' ERROR ON ERROR) as ShippingInstructions  from json_test a;
select json_value(a.document, '$.ShippingInstructions' ERROR ON ERROR) as ShippingInstructions  from json_test a
                                                                                                     *
ERROR at line 1:
ORA-40456: JSON_VALUE evaluated to non-scalar value


SQL> select a.document.ShippingInstructions from json_test a;

SHIPPINGINSTRUCTIONS
----------------------------------------------------------------------------------------------------
{"name":"Alexis Bull","Address":{"street":"200 Sporting Green","city":"South San Francisco","state":
"CA","zipCode":99236,"country":"United States of America"},"Phone":[{"type":"Office","number":"909-5
55-7307"},{"type":"Mobile","number":"415-555-1234"}]}

JSON支持布尔值，而SQL通常不支持。JSON_VALUE函数转换布尔值为字符串true、false

insert into json_test 
values(sys_guid(), '{"active": false}');
insert into json_test 
values(sys_guid(), '{"active": true}');
commit;

col active for a20
select id,json_value(a.document,'$.active') as active,json_value(a.document,'$.active' returning number) as activenum from json_test a;

ID                               ACTIVE                ACTIVENUM
-------------------------------- -------------------- ----------
9B225A54605370F8E0536892A8C0616F false                         0
9B225A54605470F8E0536892A8C0616F true                          1
9AFA0E321B132246E0536892A8C0BFD5

正如前面所讲，返回值类型可以通过RETURNING来指定，如果没有指定返回或者只指定了VARCHAR2，则默认返回VARCHAR2(4000)

col REFERENCE for a20
col REQUESTOR for a50
select json_value(a.document,'$.Reference' returning varchar2) as Reference,
json_value(a.document,'$.Requestor' returning varchar2(50)) as Requestor from json_test a;

REFERENCE            REQUESTOR
-------------------- --------------------------------------------------


ABULL-20140421       Alexis Bull

JSON_QUERY

JSON_QUERY用来返回一个JSON片段代表一个或多个值

col phone for a200
select json_query(a.document.ShippingInstructions,'$.Phone' WITH WRAPPER) as phone from json_test a; 

PHONE
-------------------------------------------------------------------------------------------------


[[{"type":"Office","number":"909-555-7307"},{"type":"Mobile","number":"415-555-1234"}]]

这里的WITH WRAPPER表示用中括号将片段括起来。

JSON_TABLE

json_table函数合并了JSON_VALUE, JSON_EXISTS和JSON_QUERY的功能，相对来说json_table语法更加复杂些，但是也更加有效率些。

json_table能让json数据看起来更像关系型的数据，这对于基于JSON数据创建view来说更有帮助

create or replace view v_json as
select jt.phone,jt.refrence,jt.name,jt.city,jt.quantity
from json_test,json_table(document,'$'
    COLUMNS (phone varchar2(50 CHAR) PATH '$.PONumber',
            refrence varchar2(50 CHAR) PATH '$.Reference',
            name varchar2(50 CHAR) PATH  '$.ShippingInstructions.name',
            city varchar2(50 CHAR) PATH '$.ShippingInstructions.Address.city',
            quantity varchar2(50 CHAR) PATH '$.LineItems.Quantity'
    )) jt
where id='9AFA0E321B132246E0536892A8C0BFD5';

视图已创建。

col phone for a20
col refrence for a20
col name for a20
col city for a20
col quantity for a20

select * from v_json;

PHONE                REFRENCE             NAME                 CITY                 QUANTITY
-------------------- -------------------- -------------------- -------------------- --------------------
1600                 ABULL-20140421       Alexis Bull          South San Francisco

COLUMNS子句定义了每列数据的定义和展示。也可以在字段中显示JSON格式

col phone for a20
col refrence for a20
col name for a20
col city for a20
col items for a50

select jt.*
from json_test,json_table(document,'$'
    COLUMNS (phone varchar2(50 CHAR) PATH '$.PONumber',
            refrence varchar2(50 CHAR) PATH '$.Reference',
            name varchar2(50 CHAR) PATH  '$.ShippingInstructions.name',
            city varchar2(50 CHAR) PATH '$.ShippingInstructions.Address.city',
            items varchar2(2000 CHAR) format json with wrapper PATH '$.LineItems'
    )) jt
where id='9AFA0E321B132246E0536892A8C0BFD5';

PHONE                REFRENCE             NAME                 CITY                 ITEMS
-------------------- -------------------- -------------------- -------------------- --------------------------------------------------
1600                 ABULL-20140421       Alexis Bull          South San Francisco  [[{"ItemNumber":1,"Part":{"Description":"One Magic
                                                                                     Christmas","UnitPrice":19.95,"UPCCode":1313109289
                                                                                    9},"Quantity":9.0},{"ItemNumber":2,"Part":{"Descri
                                                                                    ption":"Lethal Weapon","UnitPrice":19.95,"UPCCode"
                                                                                    :85391628927},"Quantity":5.0}]]