JSON（JavaScript Object Notation）是完全独立于语言的文本格式，是一种轻量级的数据交换格式。
JSONB（JavaScript Object Notation Binary）与 JSON 基本类似，区别在于 JSON 将数据保存为文本格式，而 JSONB 将数据保存为二进制格式。
DM 数据库支持对 JSON 数据进行存储和查询。在 DM 数据库中 JSON 数据以字符串形式存储。DM 建议用户在插入 JSON 数据之前，使用 IS JSON 来验证输入 JSON 数据的正确性，请参考 18.5 使用 IS JSON/IS NOT JSON 条件。
本章中的例子，除了特殊说明之外，建表语句都请参考 18.7 一个简单的例子。
18.1 数据类型
JSON 支持的数据类型包括：字符串 string、数字 number、布尔值 true 和 false、 null、对象 object 和数组 array。JSON 的各种数据类型可以嵌套使用。
图例
JSON 数据类型
18.1.1 string
字符串长度需要大于等于 0，可以为空串。一般建议使用引号将 string 括起，DM 支持双引号和单引号。string 作为 object 中的名称时，在 IS JSON(LAX)情况下可以不用引号，但作为值时必须使用引号。语法图中第一个反斜杠“\”表示转义字符。
图例
JSON string
使用说明
字符串必须以 a-z 或 A-Z 开始，后续字符可以包含 0-9；如果不遵守这个规则或者包含其他字符，则必须以引号括起。只有空格能够以“”的形式出现在字符串中，其他特殊字符则不可以。
举例说明
drop table t_json_string CASCADE;
create table t_json_string (c1 varchar2(100) CHECK (c1 IS JSON(LAX)));
例 1 在 IS JSON(LAX)情况下，string 作为名称可以不用引号，但作为值必须使用引号括起来。
insert into t_json_string values('{a:"bc123"}');
insert into t_json_string values('{"a":"bc123"}');
例 2 string 作为名称或值时，均允许为空串。
insert into t_json_string values('{"":""}');
查询测试表。
select * from t_json_string;
查询结果如下：
行号 C1
---------- -------------
1 {a:"bc123"}
2 {"a":"bc123"}
3 {"":""}
18.1.2 number
数字不支持八进制和十六进制。支持数字作为 object 中的值。
图例
JSON number
举例说明
drop table t_json_number CASCADE;
create table t_json_number (c1 varchar2(100) CHECK (c1 IS JSON(LAX)));
支持数字作为 object 中的值。
insert into t_json_number values('{a:123}');
查询测试表。
select * from t_json_number;
查询结果如下：
行号 C1
---------- ----------
1 {a:123}
18.1.3 true、false
true 和 false 代表布尔值，使用时不需要加引号。一般作为 object 中的值，但也可以作为 object 中的名称。
插入 JSON 列时，必须注意以下 2 点：
- true 和 false 作为值。IS JSON (strict)时必须是小写，否则报错；IS JSON (lax)时，则不区分大小写，如：TRUE、True、tRue 均是合法的。但查询语句中必须以小写形式才能有返回值；
- true 和 false 作为名称。这是一种特殊的用法，一般不建议这样用。仅在 IS JSON (lax)时支持，否则报错。因为用法特殊，不管在名称中大小写与否，但在查询语句中都只有小写才能有返回值。
举例说明
drop table t_json_boolean CASCADE;
create table t_json_boolean(c1 int,
c2 varchar2(100) CHECK (c2 IS JSON(STRICT)),
c3 varchar2(100) CHECK (c3 IS JSON(LAX))
);
例 1 使用 TURE 替代 true
insert into t_json_boolean values(1,'{"dameng":TRUE}',NULL);
执行结果报错：
[-6604]:违反CHECK约束[CONS134218972].
需要将 TRUE 修改为 true。
例 2 在 LAX 时使用 TURE 替代 true
insert into t_json_boolean values(2,NULL,'{"dameng":TRUE}');
插入成功，LAX 不区分大小写。
例 3 在 STRICT 时使用 true 和 false 作为名称
insert into t_json_boolean values(3,'{true:1}',NULL);
执行结果报错：
[-6604]:违反CHECK约束[CONS134218972].
STRICT 时 true 和 false 不能作为名称。
例 4 在 LAX 时使用 true 和 false 作为名称
insert into t_json_boolean values(4,NULL,'{TRUE:1}');
插入成功，LAX 时 true 和 false 可以作为名称且不区分大小写。
例 5 对上述操作的结果进行查询
1）
select C1,json_value(c3, '$.dameng') from t_json_boolean;
查询结果如下：
行号 C1 JSON_VALUE(C3,'$.dameng')
---------- ----------- -------------------------
1 2 true
2 4 NULL
2）
select C1,json_value(c3, '$.dameng' returning number) from t_json_boolean;
查询结果如下：
行号 C1 JSON_VALUE(C3,'$.dameng'RETURNINGNUMBER)
---------- ----------- ----------------------------------------
1 2 1
2 4 NULL
select C1,json_value(c3, '$.true') from t_json_boolean;
查询结果如下：
行号 C1 JSON_VALUE(C3,'$.true')
---------- ----------- -----------------------
1 2 NULL
2 4 1
select C1,json_value(c3, '$.TRUE') from t_json_boolean;
查询结果如下：
行号 C1 JSON_VALUE(C3,'$.TRUE')
---------- ----------- -----------------------
1 2 NULL
2 4 NULL
18.1.4 null
null 代表 JSON 数据为空，它与 SQL 语句中的值为 NULL 是不同的。null 使用时不需要加引号，一般作为 object 中的值，但也可以作为 object 中的名称。
插入 JSON 列时，必须注意以下 3 点：
- null 作为值。IS JSON (STRICT)时必须是小写，否则报错；IS JSON (LAX)时，则不区分大小写，如：NULL、nUll、nULL 均是合法的；
- json_value 时，null 以 SQL 语句中的值 NULL 的形式返回，此时无法区分是 SQL 的 NULL 还是 json 数据的 null；json_query 时，null 必须以指定 WITH WRAPPER 的形式返回，如[null]，查询语句中必须以小写形式才能有返回值；
- null 作为名称。这是一种特殊的用法，一般不建议这样用。仅在 IS JSON (LAX)时支持，否则报错。因为用法特殊，不管名称中的大小写，查询语句中都只有小写才能返回对应的值。
从上可以看出，null 的使用规则(1)(3)与 true、false 的使用规则基本一致。
举例说明
drop table t_json_null;
create table t_json_null(c1 int,
c2 varchar2(100) CHECK (c2 is json));
insert into t_json_null values(1,null); //SQL语句的null
insert into t_json_null values(2,'{"dameng":null}'); //json数据的null
insert into t_json_null values(3,NULL); //SQL语句的null
insert into t_json_null values(4,'{"dameng":NULL}'); //json数据的null
commit;
例 1 使用 json_value 对 t_json_null 进行查询
select json_value(c2, '$.dameng') from t_json_null;
查询结果如下：
行号 JSON_VALUE(C2,'$.dameng')
---------- -------------------------
1 NULL
2 NULL
3 NULL
4 NULL
结果中第 2、4 行全部转化为 SQL 的 NULL。和第 1、3 行一样。
例 2 以指定 WITH WRAPPER 形式进行查询
select json_query(c2, '$.dameng' WITH WRAPPER) from t_json_null;
查询结果如下：
行号 json_query(C2,'$.dameng'WITHWRAPPER)
---------- ------------------------------------
1 NULL
2 [null]
3 NULL
4 [null]
查询可以看出四行数据的不同。2、4 行为 json 数据。
18.1.5 object
对象由 0 个或多个名称/值对组成，在{}中书写。名称/值对的书写格式：<string>：<value>。
JSON 和 JSONB 在保存 OBJECT 类型数据时的区别为：JSON 会保留“名称”中间的空格以及各“名称”的顺序和重复的“名称”；JSONB 则自动对“名称”进行排序去重。
语法格式
JSON object
<string>：对象名称。支持的数据类型包括 string、true、false、null。
<value>：对象值。支持本章节介绍的所有数据类型。
举例说明
drop table t_json_object CASCADE;
create table t_json_object (c1 varchar2(100) CHECK (c1 IS JSON(LAX)));
向测试表中插入一个 JSON 对象。
insert into t_json_object values('{a:100, b:200, c:{d:300, e:400}}');
查询测试表。
SELECT * FROM t_json_object;
查询结果如下：
行号 C1
---------- --------------------------------
1 {a:100, b:200, c:{d:300, e:400}}
18.1.6 array
数组是 < 值 > 的有序集合，数组在[]中书写。
语法格式
JSON array
\
<value>：支持本章节介绍的所有数据类型。
举例说明
drop table t_json_array CASCADE;
create table t_json_array (c1 varchar2(100) CHECK (c1 IS JSON(LAX)));
数组中的值支持本章节介绍的所有数据类型。
insert into t_json_array values('{a:["a",1,true,false,null,{b:2},[3,4]]}');
查询测试表。
SELECT * FROM t_json_array;
查询结果如下：
行号 C1
---------- ---------------------------------------
1 {a:["a",1,true,false,null,{b:2},[3,4]]}
18.2 函数
为方便用户查阅，将 JSON 相关函数依据函数名称分为 JSON 函数、JSONB 函数以及其他函数三个章节。
18.2.1 JSON 函数
18.2.1.1 to_json
to_json 将输入参数转换为 JSON 字符串。
语法格式
<to_json函数> ::= to_json(<exp>)
<to_jsonb函数> ::= to_jsonb(<exp>)
参数
<exp>：待转换数据，支持任意数据类型。
返回值
转换后的 JSON 字符串。若参数<exp> 的数据类型为 NUMBER 或 CLOB，则返回值的数据类型与参数<exp> 的数据类型保持一致。否则，返回值的数据类型为 VARCHAR。
使用说明
- 当参数<exp> 为 NULL 时，返回 NULL；
- 当参数<exp> 为 TRUE 时，返回字符串“1”；
- 当参数<exp> 为 FALSE 时，返回字符串“0”；
- 当参数<exp> 中包含转义字符时，将转义字符改写为“\”表示的转义字符；
- to_json 函数可以与 <JSON_exp1>::JSON 操作符配合使用，写作 to_json(<JSON_exp1>::JSON)，功能等同于单独使用操作符 <JSON_exp1>::JSON，关于 <JSON_exp1>::JSON 的详细介绍请参考 18.4.1 json_exp1 :: JSON。
举例说明
以下示例 to_json(<exp>)和 to_jsonb(<exp>)的查询结果均一致，仅以 to_jsonb(<exp>)为例进行举例说明。
例 1 参数<exp> 为 NULL 时，返回 NULL。
select to_json(null);
查询结果如下：
行号 TO_JSON(NULL)
---------- --------------
1 NULL
例 2 参数<exp> 为 TRUE 时，返回字符串“1”。
select to_json(true);
查询结果如下：
行号 TO_JSON(TRUE)
---------- --------------
1 "1"
例 3 参数<exp> 为 FALSE 时，返回字符串“0”。
select to_json(false);
查询结果如下：
行号 TO_JSON(FALSE)
---------- ---------------
1 "0"
例 4 参数<exp> 的数据类型为 NUMBER 时，返回值的数据类型为 NUMBER。
select to_json(-12.3);
查询结果如下：
行号 TO_JSON(-12.3)
---------- ---------------
1 -12.3
例 5 参数<exp> 中包含转义字符时，将转义字符改写为“\”表示的转义字符。
select to_json('{"b":1, "a":1, "a":3, "a":2}');
查询结果如下：
行号 TO_JSON('{"b":1,"a":1,"a":3,"a":2}')
---------- -------------------------------------
1 "{\"b\":1, \"a\":1, \"a\":3, \"a\":2}"
例 6 to_json 函数与 <JSON_exp1>::JSON 操作符配合使用，即 to_json(<JSON_exp1>::JSON)，功能等同于单独使用操作符 <JSON_exp1>::JSON。
select to_json('-12.3'::json);
行号 TO_JSON('-12.3'::JSON)
---------- ----------------------
1 -12.3
select to_json('true'::json);
行号 TO_JSON('true'::JSON)
---------- ---------------------
1 true
select to_json('"str"'::json);
行号 TO_JSON('"str"'::JSON)
---------- ----------------------
1 "str"
select to_json('{"b":1,"a":1,"a":3,"a":2}'::json);
行号 TO_JSON('{"b":1,"a":1,"a":3,"a":2}'::JSON)
---------- ------------------------------------------
1 {"b":1,"a":1,"a":3,"a":2}
18.2.1.2 json_value
json_value 函数的返回值必须是单值且是标量数据类型。
语法格式
<json_value函数> ::= JSON_VALUE(<JSON_exp1>, <path_exp2> [<RETURNING 项>] [ASCII] [<ERROR项>])
<RETURNING 项> :: = RETURNING VARCHAR | VARCHAR2 | NUMBER | DATE | DATETIME | VARBINARY
<ERROR项> :: = <NULL | ERROR | <DEFAULT项>> ON ERROR
<DEFAULT项> :: = DEFAULT '<value>'
参数
<JSON_exp1>：表示 JSON 的字符串，数据类型为 VARCHAR 或 CLOB，其内容必须对应 json 数据类型的 object 或 array 数据，否则函数报错。
<path_exp2>：路径表达式，请参考 18.3.1 路径表达式。
<RETURNING 项 >：返回值类型，默认为 VARCHAR(8188)。
ASCII：以\uXXXX 十六进制的形式显示非 Unicode 字符，请参考 18.3.2 PRETTY 和 ASCII。
<ERROR 项 >：指定出错时的返回值，请参考 18.3.4 ERROR 项。
<DEFAULT 项 >：返回值必须与 <RETURNING 项 > 中定义的类型匹配。当 RETURNING 字符串时，默认值可以是数字或字符串。
使用说明
- <JSON_exp1> 为 NULL 时，返回空结果集；
- 数据类型中的 NUMBER 对应 DM 的 dec 类型，可以有多种写法，如：DECIMAL，也可以指写精度，例如:DEC(10,5)；
- true 和 false 在指定 returning NUMBER 时返回值对应 1 和 0。
举例说明：
例 创建测试表并插入数据。
create table t_json_value (c1 varchar2(100) CHECK (c1 IS JSON));
insert into t_json_value(c1) values('{"b":2}');
insert into t_json_value(c1) values('{"c":2}');
insert into t_json_value(c1) values('{"c":"3"}');
insert into t_json_value(c1) values('{"c":null}');
insert into t_json_value(c1) values('{"c":true}');
insert into t_json_value(c1) values('{"c":10,"c":20}');
insert into t_json_value(c1) values('{"c":"a\tb"}');
insert into t_json_value(c1) values('{"c":"abc"}');
insert into t_json_value(c1) values('[1,"a",true]');
insert into t_json_value(c1) values('[null,true,{"a":1}]');
例 1 使用 json_value 查询对象的值。
SELECT json_value(c1, '$.c') FROM t_json_value;
查询结果如下：
行号 JSON_VALUE(C1,'$.c')
---------- --------------------
1 NULL
2 2
3 3
4 NULL
5 true
6 10
7 a b
8 abc
9 NULL
10 NULL
例 2 使用 json_value 查询数组的值。
SELECT json_value(c1, '$[1]') FROM t_json_value;
查询结果如下：
行号 JSON_VALUE(C1,'$[1]')
---------- ---------------------
1 NULL
2 NULL
3 NULL
4 NULL
5 NULL
6 NULL
7 NULL
8 NULL
9 a
10 true
18.2.1.3 json_query
json_query 的返回结果是一个或多个 JSON 数据。多值返回时必须指定 with wrapper。单值返回时，标量类型必须指定 with wrapper，object 或 array 则不需要。
语法格式
<json_query函数> ::= json_query(<JSON_exp1>, <path_exp2> [<RETURNING项>] [PRETTY] [ASCII] [<WRAPPER项>] [<ERROR项>])
<RETURNING 项> :: = RETURNING VARCHAR |
VARCHAR2 |
NUMBER |
DATE |
DATETIME |
VARBINARY
<WRAPPER项> :: = WITH[CONDITIONAL|UNCONDITIONAL][ARRAY] WRAPPER|
WITHOUT[ARRAY]WRAPPER
<ERROR项> :: = < NULL | ERROR | EMPTY> ON ERROR
参数
<JSON_exp1>：表示 JSON 的字符串，数据类型为 VARCHAR 或 CLOB，其内容必须对应 json 数据类型的 object 或 array 数据，否则函数报错。
<path_exp2>： 路径表达式，请参考 18.3.1 路径表达式。
<RETURNING 项 >：返回值类型，默认为 VARCHAR(8188)。
PRETTY：以缩进的形式显示字符，请参考 18.3.2 PRETTY 和 ASCII。
ASCII：以\uXXXX 十六进制的形式显示非 Unicode 字符，请参考 18.3.2 PRETTY 和 ASCII。
<WRAPPER 项 >：指定查询结果的返回形式，请参考 18.3.3 WRAPPER 项。
<ERROR 项 >：指定出错时的返回值，请参考 18.3.4 ERROR 项。
使用说明
请参考 18.2.1.2 json_value。
举例说明
例 创建测试表并插入数据。
create table t_json_query (c1 varchar2(100) CHECK (c1 IS JSON));
insert into t_json_query(c1) values('{"b":2}');
insert into t_json_query(c1) values('{"c":2}');
insert into t_json_query(c1) values('{"c":"3"}');
insert into t_json_query(c1) values('{"c":{"a":1}}');
insert into t_json_query(c1) values('{"c":[1,true]}');
insert into t_json_query(c1) values('{"c":[1,2],"c":{"a":3}}');
使用 json_query，以数组的形式返回 JSON 数据。
SELECT json_query(c1, '$.c' returning varchar2 with wrapper) FROM t_json_query;
查询结果如下：
行号 JSON_QUERY(C1,'$.c'WITHWRAPPER)
---------- -------------------------------
1 NULL
2 [2]
3 ["3"]
4 [{"a":1}]
5 [[1,true]]
6 [[1,2]]
18.2.1.4 json_table
json_table 的返回数据是表记录。
语法格式
<json_table函数> ::= json_table(<JSON_exp1>, <path_exp2> [<ERROR项>] COLUMNS (<column_clause>{,<column_clause>}))
<column_clause>::=<column_name> <datatype> PATH <path_exp3>[<ERROR项>]
<ERROR项> :: = <NULL | ERROR | <DEFAULT项>> ON ERROR
<DEFAULT项> :: = DEFAULT '<value>'
参数
<JSON_exp1>：表示 JSON 的字符串，数据类型为 VARCHAR 或 CLOB，其内容必须对应 json 数据类型的 object 或 array 数据，否则函数报错。
<path_exp2>：路径表达式，表示所有列的公共路径。请参考 18.3.1 路径表达式。
<column_name>：列名。
<datatype>：列的数据类型。
<path_exp3>：路径表达式，表示列独有路径，其结果基于 <path_exp2> 路径下的数据。请参考 18.3.1 路径表达式。
<ERROR 项 >：指定出错时的返回值，请参考 18.3.4 ERROR 项。
返回值
表记录。
使用说明
1．<JSON_exp1> 为 NULL 时，返回空结果集；
2．各个列值需要先进行 <path_exp2> 查询，随后进行 <path_exp3> 查询。
举例说明
例 查询指定路径下的值。
SELECT * FROM JSON_TABLE('{a:100, b:200, c:{d:300, e:400}}', '$.c' COLUMNS(C1 NUMBER PATH '$.d', C2 NUMBER PATH '$.e'));
查询结果如下：
行号 C1 C2
----- --- ---
1 300 400
其中，“$.c”是所有列共有的路径，而“$.d”是 C1 列的路径，“$.e”是 C2 列的路径，两列的数据类型均为 NUMBER 类型。
18.2.1.5 json_overlaps
json_overlaps 检查两个 JSON 数据是否存在交集。
语法格式
<json_overlaps函数> ::= json_overlaps(<JSON_exp1>, <JSON_exp2>)
参数
<JSON_exp1>：表示 JSON 的字符串，数据类型为 VARCHAR 或 CLOB，其内容必须对应 JSON 数据类型的 string、number、true、false、null、object、array。
<JSON_exp2>：表示 JSON 的字符串，数据类型为 VARCHAR 或 CLOB，其内容必须对应 JSON 数据类型的 string、number、true、false、null、object、array。
返回值
0：不存在交集；
1：存在交集。
使用说明
- 若 <JSON_exp1> 或 <JSON_exp2> 为 NULL，则返回 NULL；
- 若 <JSON_exp1> 或 <JSON_exp2> 中包含 object 对象，则默认对 object 对象名称/值对中的“名称”进行去重后检查是否存在交集，去重时仅保留输入的最后一个名称/值对；
- 若 JSON 数据存在嵌套情况，例如 array 中包含 object 或 array 等各种类型的数据，仅检查最外层 JSON 数据是否存在交集。
举例说明
例 1 <JSON_exp1> 和 <JSON_exp2> 均为 NULL，返回 NULL。
SELECT JSON_OVERLAPS(NULL,NULL);
查询结果如下：
行号 JSON_OVERLAPS(NULL,NULL)
---------- ------------------------
1 NULL
例 2 <JSON_exp1> 的 JSON 数据类型为 object，首先对 object 对象名称/值对中的“名称”进行去重后检查是否存在交集。
在语句中查询{"a":1}。
SELECT JSON_OVERLAPS('{"a":1,"a":2,"a":3,"b":4}', '{"a":1}');
查询结果如下：
行号 JSON_OVERLAPS('{"a":1,"a":2,"a":3,"b":4}','{"a":1}')
-------- ----------------------------------------------------
1 0
在语句中查询{"a":3}。
SELECT JSON_OVERLAPS('{"a":1,"a":2,"a":3,"b":4}', '{"a":3}');
查询结果如下：
行号 JSON_OVERLAPS('{"a":1,"a":2,"a":3,"b":4}','{"a":3}')
--------- ----------------------------------------------------
1 1
本例中，首先对 <JSON_exp1> 中的 object 进行去重，去重时仅保留输入的最后一个名称/值对，<JSON_exp1> 去重后为“{"a":3,"b":4}”。
例 3 <JSON_exp1> 的 JSON 数据类型为 object，并且名称/值对中的“值”也为 object，则对该“值”也将进行去重操作。
在语句中查询{"a":{"b":2}}。
SELECT JSON_OVERLAPS('{"a":{"b":2,"b":3}}', '{"a":{"b":2}}');
查询结果如下：
行号 JSON_OVERLAPS('{"a":{"b":2,"b":3}}','{"a":{"b":2}}')
---------- ----------------------------------------------------
1 0
在语句中查询{"a":{"b":3}}。
SELECT JSON_OVERLAPS('{"a":{"b":2,"b":3}}', '{"a":{"b":3}}');
查询结果如下：
行号 JSON_OVERLAPS('{"a":{"b":2,"b":3}}','{"a":{"b":3}}')
---------- ----------------------------------------------------
1 1
例 4 <JSON_exp1> 的 JSON 数据类型为 array，并且 array 中包含 object，则对该 object 对象名称/值对中的“名称”也将进行去重操作。
在语句中查找{"a":2}。
SELECT JSON_OVERLAPS('[1,{"a":2,"a":3}]','{"a":2}');
查询结果如下：
行号 JSON_OVERLAPS('[1,{"a":2,"a":3}]','{"a":2}')
---------- --------------------------------------------
1 0
在语句中查找{"a":3}。
SELECT JSON_OVERLAPS('[1,{"a":2,"a":3}]','{"a":3}');
查询结果如下：
行号 JSON_OVERLAPS('[1,{"a":2,"a":3}]','{"a":3}')
---------- --------------------------------------------
1 1
例 5 <JSON_exp1> 的 JSON 数据类型为 array，仅检查最外层 JSON 数据是否存在交集。
在语句中查找 1。
SELECT JSON_OVERLAPS('[1,[2,3],{"a":2,"b":4}]','1');
查询结果如下：
行号 JSON_OVERLAPS('[1,[2,3],{"a":2,"b":4}]','1')
---------- --------------------------------------------
1 1
在语句中查找 2。
SELECT JSON_OVERLAPS('[1,[2,3],{"a":2,"b":4}]','2');
查询结果如下：
行号 JSON_OVERLAPS('[1,[2,3],{"a":2,"b":4}]','2')
---------- --------------------------------------------
1 0
在语句中查询[2,3]。
SELECT JSON_OVERLAPS('[1,[2,3],{"a":2,"b":4}]','[2,3]');
查询结果如下：
行号 JSON_OVERLAPS('[1,[2,3],{"a":2,"b":4}]','[2,3]')
---------- ------------------------------------------------
1 0
在语句中查询[[2,3]]。
SELECT JSON_OVERLAPS('[1,[2,3],{"a":2,"b":4}]','[[2,3]]');
查询结果如下：
行号 JSON_OVERLAPS('[1,[2,3],{"a":2,"b":4}]','[[2,3]]')
---------- --------------------------------------------------
1 1
在语句中查找[[3,2]]。
SELECT JSON_OVERLAPS('[1,[2,3],{"a":2,"b":4}]','[[3,2]]');
查询结果如下：
行号 JSON_OVERLAPS('[1,[2,3],{"a":2,"b":4}]','[[3,2]]')
---------- --------------------------------------------------
1 0
在语句中查找{"a":2}。
SELECT JSON_OVERLAPS('[1,[2,3],{"a":2,"b":4}]','{"a":2}');
查询结果如下：
行号 JSON_OVERLAPS('[1,[2,3],{"a":2,"b":4}]','{"a":2}')
---------- --------------------------------------------------
1 0
在语句中查找{"a":2,"b":4}。
SELECT JSON_OVERLAPS('[1,[2,3],{"a":2,"b":4}]','{"a":2,"b":4}');
查询结果如下：
行号 JSON_OVERLAPS('[1,[2,3],{"a":2,"b":4}]','{"a":2,"b":4}')
---------- --------------------------------------------------------
1 1
本例中，<JSON_exp1> 中共包含三个数组元素，即 number 类型数据“1”，array 类型数据“[2,3]”以及 object 类型数据“{"a":2,"b":4}”，因此仅当 <JSON_exp2> 中至少包含上述三个数据中的任意一个时，<JSON_exp1> 和 <JSON_exp2> 才会存在交集。
18.2.1.6 json_set
json_set 替换或新增 JSON 字符串中用户指定的项。
语法格式
<json_set函数> ::= json_set(<JSON_exp1>, <path_exp2>, <value_exp3>{, <path_exp4>, <value_exp5>})
参数
<JSON_exp1>：表示目标 JSON 字符串，数据类型为 VARCHAR 或 CLOB，其内容必须对应的 JSON 数据类型的 OBJECT 或 ARRAY。
<path_exp2>：路径表达式，具体书写规则请参考 18.3.1 路径表达式。
<value_exp3>：指定替换后的值或新增项的值，数据类型为 NUMBER、VARCHAR、TRUE 或 FALSE。当 <path_exp2> 指定的项存在时，则替换指定项的值；当 <path_exp2> 指定的项不存在时，新增 <path_exp2> 指定的项，并将值设为 <value_exp3>。
返回值
替换或新增指定项后的 JSON 字符串，数据类型与参数 <JSON_exp1> 的数据类型保持一致，为 VARCHAR 或 CLOB。
使用说明
- 当参数 <JSON_exp1> 为 NULL 时，返回 NULL；
- 当参数 <value_exp3> 为 TRUE 时，将替换为字符串“1”；
- 当参数 <value_exp3> 为 FALSE 时，将替换为字符串“0”；
- 支持同时替换或新增 JSON 字符串中的多项。
举例说明
例 1 参数 <JSON_exp1> 为 NULL。
select json_set(NULL,'$.f1.a',15);
查询结果如下：
行号 JSON_SET(NULL,'$.f1.a',15)
---------- --------------------------
1 NULL
例 2 替换 JSON 字符串中的一项，参数 <value_exp3> 的数据类型为 NUMBER。
select json_set('{"f1":{"a":1}}','$.f1.a',15);
查询结果如下：
行号 JSON_SET('{"f1":{"a":1}}','$.f1.a',15)
---------- --------------------------------------
1 {"f1":{"a":15}}
例 3 替换 JSON 字符串中的一项，参数 <value_exp3> 的数据类型为 VARCHAR。
select json_set('{"f1":{"a":1}}','$.f1.a','str');
查询结果如下：
行号 JSON_SET('{"f1":{"a":1}}','$.f1.a','str')
---------- -----------------------------------------
1 {"f1":{"a":"str"}}
例 4 替换 JSON 字符串中的一项，参数 <value_exp3> 为 TRUE。
select json_set('[{"f1":{"a":1}}]','$[0]',TRUE);
查询结果如下：
行号 JSON_SET('[{"f1":{"a":1}}]','$[0]',TRUE)
---------- ----------------------------------------
1 ["1"]
例 5 同时替换 JSON 字符串中的多项。
select json_set('[{"f1":{"a":1}},2,"b"]','$[0].f1.a',15,'$[1]','c');
查询结果如下：
行号 JSON_SET('[{"f1":{"a":1}},2,"b"]','$[0].f1.a',15,'$[1]','c')
---------- ------------------------------------------------------------
1 [{"f1":{"a":15}},"c","b"]
例 6 <path_exp2> 指定的项不存在时，新增 <path_exp2> 指定的项，并将值设为 <value_exp3>。
select json_set('{"f1":{"a":1}}','$.f1.b',15);
查询结果如下：
行号 JSON_SET('{"f1":{"a":1}}','$.f1.b',15)
---------- --------------------------------------
1 {"f1":{"a":1,"b":15}}
18.2.1.7 json_replace
json_replace 替换 JSON 字符串中用户指定的项。该函数与 json_set 替换 JSON 字符串中用户指定项的功能相同。
语法格式
<json_replace函数> ::= json_replace(<JSON_exp1>, <path_exp2>, <value_exp3>{, <path_exp4>, <value_exp5>})
参数
<JSON_exp1>：表示目标 JSON 字符串，数据类型为 VARCHAR 或 CLOB，其内容必须对应的 JSON 数据类型的 OBJECT 或 ARRAY。
<path_exp2>：路径表达式，具体书写规则请参考 18.3.1 路径表达式。
<value_exp3>：指定替换后的值，数据类型为 NUMBER、VARCHAR、TRUE 或 FALSE。当 <path_exp2> 指定的项不存在时，则不进行替换。
返回值
替换后的 JSON 字符串，数据类型与参数 <JSON_exp1> 的数据类型保持一致，为 VARCHAR 或 CLOB。
使用说明
请参考 18.2.1.6 json_set。
举例说明
例 替换 JSON 字符串中的多项。
select json_replace('[{"f1":{"a":1}},2,"b"]','$[0].f1.a',15,'$[1]','c');
查询结果如下：
行号 JSON_REPLACE('[{"f1":{"a":1}},2,"b"]','$[0].f1.a',15,'$[1]','c')
---------- ----------------------------------------------------------------
1 [{"f1":{"a":15}},"c","b"]
18.2.1.8 json_insert
json_insert 新增 JSON 字符串中用户指定的项。该函数与 json_set 新增 JSON 字符串中用户指定项的功能相同。
语法格式
<json_insert函数> ::= json_insert(<JSON_exp1>, <path_exp2>, <value_exp3>{, <path_exp4>, <value_exp5>})
参数
<JSON_exp1>：表示目标 JSON 字符串，数据类型为 VARCHAR 或 CLOB，其内容必须对应的 JSON 数据类型的 OBJECT 或 ARRAY。
<path_exp2>：路径表达式，具体书写规则请参考 18.3.1 路径表达式。
<value_exp3>：指定新增项的值，数据类型为 NUMBER、VARCHAR、TRUE 或 FALSE。当 <path_exp2> 指定的项存在时，则不新增。
返回值
新增指定项后的 JSON 字符串，数据类型与参数 <JSON_exp1> 的数据类型保持一致，为 VARCHAR 或 CLOB。
使用说明
请参考 18.2.1.6 json_set。
举例说明
例 新增 JSON 字符串中的多项。
select json_insert('[{"f1":{"a":1}},2,"b"]','$[0].f1.a[1]',2,'$[3]','c');
查询结果如下：
行号 JSON_INSERT('[{"f1":{"a":1}},2,"b"]','$[0].f1.a[1]',2,'$[3]','c')
---------- -----------------------------------------------------------------
1 [{"f1":{"a":[1,2]}},2,"b","c"]
18.2.1.9 json_extract
json_extract 获取 JSON 字符串中指定“名称”对应的“值”。
语法格式
<json_extract函数> ::= json_extract(<JSON_exp1>, <path_exp2>)
参数
<JSON_exp1>：表示目标 JSON 字符串，数据类型为 VARCHAR 或 CLOB，其内容必须对应的 JSON 数据类型的 OBJECT 或 ARRAY。
<path_exp2>：路径表达式，具体书写规则请参考[18.3.1 路径表达式](#18.3.1 路径表达式)。
返回值
JSON 字符串中指定“名称”对应的“值”。
使用说明
- 当 JSON 字符串中指定的“名称”不存在时，返回 NULL；
- json_extract 函数会将输入的 JSON 字符串当作 JSONB 字符串处理，即首先对 OBJECT 名称/值对中的“名称”进行排序去重，然后返回指定“名称”对应的“值”。
举例说明
例 创建测试表并插入数据。
create table t_json_extract (c1 varchar2(100) CHECK (c1 IS JSON));
insert into t_json_extract(c1) values('{"b":2}');
insert into t_json_extract(c1) values('{"c":2}');
insert into t_json_extract(c1) values('{"c":"3"}');
insert into t_json_extract(c1) values('{"c":null}');
insert into t_json_extract(c1) values('{"c":true}');
insert into t_json_extract(c1) values('{"c":10,"c":20}');
insert into t_json_extract(c1) values('{"c":"a\tb"}');
insert into t_json_extract(c1) values('{"c":"abc"}');
insert into t_json_extract(c1) values('{"c":[1,2]}');
insert into t_json_extract(c1) values('{"c":{"f":1,"a":2}}');
获取测试表 t_json_extract 的 c1 列中，名称“c”对应的值。
select json_extract(c1,'$.c') from t_json_extract;
查询结果如下：
行号 JSON_EXTRACT(C1,'$.c')
---------- ----------------------
1 NULL
2 2
3 "3"
4 null
5 true
6 20
7 "a\tb"
8 "abc"
9 [1,2]
10 {"a":2,"f":1}
18.2.1.10 json_unquote
json_unquote 取消 JSON 对象名称/值对中的“值”外层的双引号，并将结果作为字符串返回。
语法格式
<json_unquote函数> ::= json_unquote(<json_value>)
参数
<json_value>：JSON 对象名称/值对中的“值”。
返回值
不加双引号的 <json_value>，数据类型为字符串。
使用说明
- 当 <json_value> 为 NULL 时，返回 NULL；
- 当 <json_value> 中包含转义字符时，对转义字符进行转义后输出。
举例说明
例 创建测试表并插入数据。
create table t_json_unquote (c1 varchar2(100) CHECK (c1 IS JSON));
insert into t_json_unquote(c1) values('{"b":2}');
insert into t_json_unquote(c1) values('{"c":2}');
insert into t_json_unquote(c1) values('{"c":"3"}');
insert into t_json_unquote(c1) values('{"c":null}');
insert into t_json_unquote(c1) values('{"c":true}');
insert into t_json_unquote(c1) values('{"c":10,"c":20}');
insert into t_json_unquote(c1) values('{"c":"a\tb"}');
insert into t_json_unquote(c1) values('{"c":"abc"}');
insert into t_json_unquote(c1) values('{"c":[1,2]}');
insert into t_json_unquote(c1) values('{"c":{"f":1,"a":2}}');
json_unquote 函数通常搭配 json_extract 函数一起使用，首先利用 json_extract 函数获取名称/值对中的“值”，然后利用 json_unquote 函数去掉“值”外层的双引号。
select json_unquote(json_extract(c1,'$.c')) from t_json_unquote;
查询结果如下：
行号 JSON_UNQUOTE(JSON_EXTRACT(C1,'$.c'))
---------- ------------------------------------
1 NULL
2 2
3 3
4 null
5 true
6 20
7 a b
8 abc
9 [1,2]
10 {"a":2,"f":1}
18.2.1.11 json_array
json_array 从可变的参数列表（可以为空）中创建 JSON 数组并返回。
语法格式
<json_array函数> ::= json_array([<value_exp1>[, <value_exp2>]...])
参数
<value_exp>：传入的参数列表。可以传入任意类型的任意数量的参数。
返回值
表示 JSON 数组的字符串。
使用说明
若参数列表中的任一参数不能转化成数字、字符串、布尔值、数组、对象、NULL 等 JSON 类型则报错。
举例说明
例 使用 json_array 函数创建 JSON 数组。
SELECT JSON_ARRAY(1,'1',FALSE,NULL,'[1,2]','{SS:1600}');
查询结果如下：
行号 JSON_ARRAY(1,'1',FALSE,NULL,'[1,2]','{ss:1600}')
---------- ------------------------------------------------
1 [1,"1","0",null,"[1,2]","{ss:1600}"]
18.2.1.12 json_object
json_object 返回一个用给定键值对创建的 JSON OBJECT。
语法格式
<json_object函数> ::= json_object([<key_exp1>, <value_exp1>[, <key_exp1>, <value_exp2>]...])
参数
<key_exp>：字符串、数字或布尔值，函数会自动将其转化成字符串。
<value_exp>：任意类型。
返回值
表示 JSON OBJECT 的字符串。
使用说明
1．传入参数需为偶数个，若传入奇数个参数则报错。
2．取作键的参数不是字符串、数字、布尔值；或取作值的参数不能转化成数字、字符串、布尔值、数组、对象、null 等 JSON 类型则报错。作为键或值的参数的布尔值将被转换为"0"/"1"。
3．json_object 默认对返回的键值对结果进行去重排序。
举例说明
例 1 参数的键为字符串、数字、布尔值时，使用 json_object 函数。
SELECT JSON_OBJECT('A',2,3,4,TRUE,5);
查询结果如下：
行号 JSON_OBJECT('A',2,3,4,TRUE,5)
---------- -----------------------------
1 {"1":5,"3":4,"A":2}
例 2 参数的键为其他类型时，使用 json_object 函数。
SELECT JSON_OBJECT([1,2],5);
查询结果报错：
[-2007]: 语法分析出错.
例 3 传入参数为奇数个。
SELECT JSON_OBJECT('1',false,5);
查询结果报错：
[-5402]:参数个数不匹配.
18.2.1.13 json_contains_path
json_contains_path 返回是否能根据给定的路径在 JSON 中查找到数据。
语法格式
<json_contains_path函数> ::= JSON_CONTAINS_PATH(<json_exp1>, <one_or_all项>, <path_exp2>[, <path_exp3>] ...)
<one_or_all项> :: = ONE | ALL
参数
<json_exp1>：合法 JSON 的字符串。
<one_or_all 项 >：取'ONE'表示给定的 path 中存在一个能查找到即可返回 1(true)；取'ALL'表示给定的 path 需要全部查找到才可返回 1(true)。
<path_exp>：路径表达式，具体书写规则请参考[18.3.1 路径表达式](#18.3.1 路径表达式)。
返回值
返回 1 或 0。
使用说明
1．任一参数为 NULL 时返回 NULL。
2．若 json_doc 不是合法 JSON；或 path 参数不是合法 path 表达式；或 <one_or_all> 不是取'one'或'all'时报错。
举例说明
例 1 <one_or_all 项 > 取'ONE'。
SELECT JSON_CONTAINS_PATH('{"a":1,"b":2,"c":{"d":4}}', 'one', '$.a', '$.e');
查询结果如下：
行号 JSON_CONTAINS_PATH('{"a":1,"b":2,"c":{"d":4}}','one','$.a','$.e')
---------- -----------------------------------------------------------------
1 1
例 2 <one_or_all 项 > 取'ALL'。
SELECT JSON_CONTAINS_PATH('{"a":1,"b":2,"c":{"d":4}}', 'all', '$.a', '$.e');
查询结果报错：
行号 JSON_CONTAINS_PATH('{"a":1,"b":2,"c":{"d":4}}','all','$.a','$.e')
---------- -----------------------------------------------------------------
1 0
例 3 <one_or_all 项 > 取其他值。
SELECT JSON_CONTAINS_PATH('{"a":1,"b":2,"c":{"d":4}}', 'any', '$.a', '$.e');
查询结果报错：
[-2206]:无效的参数值.
18.2.1.14 json_keys
json_keys 将 JSON OBJECT 的最外层键以 JSON 数组形式返回，若指定了 path 参数，则将给定 path 查找到的 JSON OBJECT 的最外层键以 JSON 数组形式返回。
语法格式
<json_key函数> ::= JSON_KEYS(<json_exp>[, <path_exp>])
参数
<json_exp>：合法 JSON 的字符串。
<path_exp>：路径表达式，具体书写规则请参考[18.3.1 路径表达式](#18.3.1 路径表达式)。
返回值
表示 JSON 数组的字符串。
使用说明
1．任一参数为 NULL，或 json_doc 不是 JSON OBJECT，或给定 path 查找到的不是 JSON OBJECT 时返回 NULL。
2．若 json_doc 不是合法 JSON；或 path 参数不是合法 path 表达式或含有*和**通配符。
举例说明
例 1 不指定 path 参数。
SELECT JSON_KEYS('{"a":1, "b":{"c":2, "d":3}}');
查询结果如下：
行号 JSON_KEYS('{"a":1,"b":{"c":2,"d":3}}')
---------- --------------------------------------
1 ["a","b"]
例 2 指定 path 参数。
SELECT JSON_KEYS('{"a":1, "b":{"c":2, "d":3}}', '$.b');
查询结果如下：
行号 JSON_KEYS('{"a":1,"b":{"c":2,"d":3}}','$.b')
---------- --------------------------------------------
1 ["c","d"]
例 3 path 不合法。
SELECT JSON_KEYS('{"a":1, "b":{"c":2, "d":3}}', 'b');
查询结果报错：
[-3102]:JSON路径表达式语法错误.
18.2.1.15 json_build_array
json_build_array 从可变的参数列表（可以为空）中创建 JSON 数组并返回。
语法格式
<json_build_array函数> ::= JSON_BUILD_ARRAY(<exp>)
参数
返回值
表示 JSON 数组的字符串。
使用说明
任一参数不能转化成数字、字符串、布尔值、数组、对象、null 等 JSON 类型则报错。
举例说明
例 使用 json_build_array 函数创建 JSON 数组。
SELECT JSON_BUILD_ARRAY(1,'1',FALSE,NULL,'[1,2]','{ss:1600}');
查询结果如下：
行号 JSON_BUILD_ARRAY(1,'1',FALSE,NULL,'[1,2]','{ss:1600}')
---------- ------------------------------------------------------
1 [1,"1","0",null,"[1,2]","{ss:1600}"]
18.2.1.16 json_build_object
json_build_object 从可变的参数列表（可以为空）中创建 JSON 数组并返回。
语法格式
<json_build_object函数> ::= json_build_object([<key_exp1>, <value_exp1>[, <key_exp2>, <value_exp2>]...])
参数
<key_exp>：字符串，还可以是数字或布尔值，函数会将其转化成字符串。
<value_exp>：任意类型。
返回值
表示 JSON OBJECT 的字符串。
使用说明
1．传入参数需为偶数个，若传入奇数个参数则报错。
2．取作键的参数不是字符串、数字、布尔值；或取作值的参数不能转化成数字、字符串、布尔值、数组、对象、null 等 JSON 类型则报错。作为键或值的参数的布尔值将被转换为"1"/"0"。
3．json_build_object 函数不对结果键值对进行去重排序。
举例说明
例 1 参数的键为字符串、数字、布尔值时，使用 json_build_object 函数。
SELECT JSON_BUILD_OBJECT('a',2,3,4,true,5);
查询结果如下：
行号 JSON_BUILD_OBJECT('a',2,3,4,TRUE,5)
---------- -----------------------------------
1 {"a":2,"3":4,"1":5}
例 2 参数的键为其他类型时，使用 json_build_object 函数。
SELECT json_build_object([1,2],5);
查询结果报错：
[-2007]:语法分析出错.
例 3 传入参数为奇数个。
SELECT JSON_BUILD_OBJECT('1',false,5);
查询结果报错：
[-5402]:参数个数不匹配.
18.2.1.17 json_object_keys
json_object_keys 返回顶层 JSON OBJECT 键的集合。
语法格式
<json_object_keys函数> ::= JSON_OBJECT_KEYS(<json_exp1>)
参数
<json_exp1>：合法的 JSON 字符串。
返回值
字符串集合。
使用说明
1．此函数在查询中须位于 from 项。
2．参数表示的不是 JSON OBJECT 时报错。
举例说明
例 1 使用 json_object_keys 返回 JSON OBJECT 键的集合。
SELECT * FROM JSON_OBJECT_KEYS('{"F1":"ABC","F2":{"F3":"A","F4":"B"}}');
查询结果如下：
行号 JSON_OBJECT_KEYS
---------- ----------------
1 f1
2 f2
例 2 json_object_keys 的参数不是 JSON OBJECT。
SELECT * FROM JSON_OBJECT_KEYS('[1,2,3]');
查询结果报错：
[-3117]:只能在对象上调用此函数.
例 3 在查询项中使用 json_object_keys。
SELECT JSON_OBJECT_KEYS('{"f1":"abc","f2":{"f3":"a","f4":"b"}}');
查询结果报错：
[-2207]:无法解析的成员访问表达式[JSON_OBJECT_KEYS].
18.2.1.18 json_type
json_type 返回指定 JSON 数据的 JSON 类型。
语法格式
<json_type函数> ::= JSON_TYPE(<json_exp1>)
参数
<json_exp1>：表示 JSON 的字符串，数据类型为 VARCHAR 或 CLOB。
返回值
返回 <json_exp1> 对应的 JSON 类型。
使用说明
1．当 <json_exp1> 为 NULL 时，返回 NULL；
2．当 <json_exp1> 为'null'时，返回 NULL；
3．当 <json_exp1> 为'NULL'时，报错返回；
4．当 <json_exp1> 表示整数时，返回 INTEGER；
5．当 <json_exp1> 表示小数时，返回 DOUBLE；
6．当 <json_exp1> 为非法的 JSON 字符串时，报错返回。
举例说明
例 1 <json_exp1> 为 NULL 或者'NULL'。
SELECT JSON_TYPE(NULL);
--查询结果如下
行号 JSON_TYPE(NULL)
---------- ---------------
1 NULL
SELECT JSON_TYPE('null');
--查询结果如下
行号 JSON_TYPE('null')
---------- -----------------
1 NULL
SELECT JSON_TYPE('NULL');
[-3105]:JSON值语法错误.
例 2 <json_exp1> 表示数值类型。
SELECT JSON_TYPE('1');
--查询结果如下
行号 JSON_TYPE('1')
---------- --------------
1 INTEGER
SELECT JSON_TYPE('1.23');
--查询结果如下
行号 JSON_TYPE('1.23')
---------- -----------------
1 DOUBLE
例 3 <json_exp1> 表示字符串、布尔类型、object、array。
SELECT JSON_TYPE('"abc"');
--查询结果如下
行号 JSON_TYPE('"abc"')
---------- ------------------
1 STRING
SELECT JSON_TYPE('true');
--查询结果如下
行号 JSON_TYPE('true')
---------- -----------------
1 BOOLEAN
SELECT JSON_TYPE('{"a":1}');
--查询结果如下
行号 JSON_TYPE('{"a":1}')
---------- --------------------
1 OBJECT
SELECT JSON_TYPE('[1,2,3]');
--查询结果如下
行号 JSON_TYPE('[1,2,3]')
---------- --------------------
1 ARRAY
例 4 <json_exp1> 为非法的 JSON 字符串时，报错返回。
SELECT JSON_TYPE('abc');
[-3105]:JSON值语法错误.
18.2.1.19 json_typeof
json_typeof 返回指定 JSON 数据的 JSON 类型。
语法格式
<json_typeof函数> ::= JSON_TYPEOF(<json_exp1>)
参数
<json_exp1>：表示 JSON 的字符串，数据类型为 VARCHAR 或 CLOB。
返回值
返回 <json_exp1> 对应的 JSON 类型。
使用说明
1．当 <json_exp1> 为 NULL 时，返回 NULL；
2．当 <json_exp1> 为'null'时，返回 null；
3．当 <json_exp1> 为'NULL'时，报错返回；
4．当 <json_exp1> 表示整数或者小数时，均返回 number；
5．当 <json_exp1> 为非法的 JSON 字符串时，报错返回。
举例说明
例 1 <json_exp1> 为 NULL 或者'NULL'。
SELECT JSON_TYPEOF(NULL);
//查询结果如下
行号 JSON_TYPEOF(NULL)
---------- -----------------
1 NULL
SELECT JSON_TYPEOF('null');
//查询结果如下
行号 JSON_TYPEOF('null')
---------- -------------------
1 null
SELECT JSON_TYPEOF('NULL');
[-3105]:JSON值语法错误.
例 2 <json_exp1> 表示数值类型。
SELECT JSON_TYPEOF('1');
//查询结果如下
行号 JSON_TYPEOF('1')
---------- ----------------
1 number
SELECT JSON_TYPEOF('1.23');
//查询结果如下
行号 JSON_TYPEOF('1.23')
---------- -------------------
1 number
例 3 <json_exp1> 表示字符串、布尔类型、object、array。
SELECT JSON_TYPEOF('"abc"');
//查询结果如下
行号 JSON_TYPEOF('"abc"')
---------- --------------------
1 string
SELECT JSON_TYPEOF('true');
//查询结果如下
行号 JSON_TYPEOF('true')
---------- -------------------
1 boolean
SELECT JSON_TYPEOF('{"a":1}');
//查询结果如下
行号 JSON_TYPEOF('{"a":1}')
---------- ----------------------
1 object
SELECT JSON_TYPEOF('[1,2,3]');
//查询结果如下
行号 JSON_TYPEOF('[1,2,3]')
---------- ----------------------
1 array
例 4 <json_exp1> 为非法的 JSON 字符串时，报错返回。
SELECT JSON_TYPEOF('abc');
[-3105]:JSON值语法错误.
18.2.1.20 json_contains
json_contains 函数判断 json 间的包含关系。判断根据路径 <PATH_exp> 在 <JSON_exp1> 中查找到的 JSON 是否包含 <JSON_exp2>，未给定 <PATH_exp> 参数时，判断 <JSON_exp1> 是否包含 <JSON_exp2>。
语法格式
<json_contains函数> ::= json_contains(<JSON_exp1>, <JSON_exp2>[, <PATH_exp>])
参数
<JSON_exp1>：表示 JSON 的字符串，数据类型为 VARCHAR 或 CLOB。
<JSON_exp2>：表示 JSON 的字符串，数据类型为 VARCHAR 或 CLOB。
<path_exp>：路径表达式，具体书写规则请参考[18.3.1 路径表达式](#18.3.1 路径表达式)。
返回值
返回值为数值类型，1 表示包含，0 表示不包含。
使用说明
-
参数 <JSON_exp1> 或 <JSON_exp2> 不是表示合法 JSON 的字符串时，报错；
-
任一参数为 NULL，或 <PATH_exp> 在 <JSON_exp1> 中无法查找到数据时，返回 NULL；
-
path 表达式含有多重路径，例如：通配符、数组下标范围时报错；
-
两标量在当且仅当它们可比较且相等时可视为包含；可比较的情况：JSON 类型相同可比较，json_int64 和 json_decimal 可比较；
-
下述为视为包含关系的情况，除下述情况外均不是包含关系：
- 一个 JSON 数组包含于另一个 JSON 数组，当且仅当前者每个元素都被后者的某个元素包含；
- 一个非数组 JSON 包含于一个 JSON 数组，当且仅当前者被后者的某个元素包含；
- 一个 JSON OBJECT 包含于另一个 JSON OBJECT，当且仅当前者的所有键都在后者中有同名的键，且前者键对应的值，均包含于后者相应键对应的值；
举例说明
例 1 任一参数为 NULL 时，返回 NULL。
SELECT JSON_CONTAINS('1','1',NULL);
查询结果如下：
行号 JSON_CONTAINS('1','1',NULL)
---------- ---------------------------
1 NULL
例 2 标量间的包含关系。
SELECT JSON_CONTAINS('1.0', '1');
查询结果如下：
行号 JSON_CONTAINS('1.0','1')
---------- ------------------------
1 1
例 3 非标量间的包含关系。
SELECT JSON_CONTAINS('{"a":[{"b":[1]}, ["c"]]}', '[{"b":1}, "c"]', '$.a');
查询结果如下：
行号 JSON_CONTAINS('{"a":[{"b":[1]},["c"]]}','[{"b":1},"c"]','$.a')
---------- --------------------------------------------------------------
1 1
18.2.2 JSONB 函数
18.2.2.1 to_jsonb
to_jsonb 将输入参数转换为 JSONB 字符串。
语法格式
<to_jsonb函数> ::= to_jsonb(<exp>)
参数
<exp>：待转换数据，支持任意数据类型。
返回值
转换后的 JSONB 字符串。若参数<exp> 的数据类型为 NUMBER 或 CLOB，则返回值的数据类型与参数<exp> 的数据类型保持一致。否则，返回值的数据类型为 VARCHAR。
使用说明
- 当参数<exp> 为 NULL 时，返回 NULL；
- 当参数<exp> 为 TRUE 时，返回字符串“1”；
- 当参数<exp> 为 FALSE 时，返回字符串“0”；
- 当参数<exp> 中包含转义字符时，将转义字符改写为“\”表示的转义字符；
- to_jsonb 函数可以与 <JSONB_exp1>::JSONB 操作符配合使用，写作 to_jsonb(<JSONB_exp1>::JSONB)，功能等同于单独使用操作符 <JSONB_exp1>::JSONB，关于 <JSONB_exp1>::JSONB 的详细介绍请参考[18.4.2 <JSON_exp1> :: JSONB](#18.4.2 <JSON_exp1> :: JSONB)。
举例说明
例 1 参数<exp> 为 NULL 时，返回 NULL。
select to_jsonb(null);
查询结果如下：
行号 TO_JSONB(NULL)
---------- --------------
1 NULL
例 2 参数<exp> 为 TRUE 时，返回字符串“1”。
select to_jsonb(true);
查询结果如下：
行号 TO_JSONB(TRUE)
---------- --------------
1 "1"
例 3 参数<exp> 为 FALSE 时，返回字符串“0”。
select to_jsonb(false);
查询结果如下：
行号 TO_JSONB(FALSE)
---------- ---------------
1 "0"
例 4 参数<exp> 的数据类型为 NUMBER 时，返回值的数据类型为 NUMBER。
select to_jsonb(-12.3);
查询结果如下：
行号 TO_JSONB(-12.3)
---------- ---------------
1 -12.3
例 5 参数<exp> 中包含转义字符时，将转义字符改写为“\”表示的转义字符。
select to_jsonb('{"b":1, "a":1, "a":3, "a":2}');
查询结果如下：
行号 TO_JSONB('{"b":1,"a":1,"a":3,"a":2}')
---------- -------------------------------------
1 "{\"b\":1, \"a\":1, \"a\":3, \"a\":2}"
例 6 to_jsonb 函数与 <JSONB_exp1>::JSONB 操作符配合使用，即 to_jsonb(<JSONB_exp1>::JSONB) ，功能等同于单独使用操作符 <JSONB_exp1>::JSONB。
select to_jsonb('-12.3'::jsonb);
行号 TO_JSONB('-12.3'::JSONB)
---------- ------------------------
1 -12.3
select to_jsonb('true'::jsonb);
行号 TO_JSONB('true'::JSONB)
---------- -----------------------
1 true
select to_jsonb('"str"'::jsonb);
行号 TO_JSONB('"str"'::JSONB)
---------- ------------------------
1 "str"
select to_jsonb('{"b":1,"a":1,"a":3,"a":2}'::jsonb);
行号 TO_JSONB('{"b":1,"a":1,"a":3,"a":2}'::JSONB)
---------- --------------------------------------------
1 {"a":2,"b":1}
18.2.2.2 jsonb_each
jsonb_each 将最外层 JSON 对象扩展为一组键/值对。
语法格式
<jsonb_each函数> ::= jsonb_each(<JSON_exp1>)
参数
<JSON_exp1>： 表示 JSON 的字符串，数据类型为 VARCHAR 或 CLOB。
返回值
返回值表的结构为（key，value）。
key：JSON 对象名称/值对中的“名称”，数据类型与参数 <JSON_exp1> 的数据类型保持一致，为 VARCHAR 或 CLOB。
value：key 对应 JSON 的字符串，数据类型与参数 <JSON_exp1> 的数据类型保持一致，为 VARCHAR 或 CLOB。
使用说明
- 当参数 <JSON_exp1> 为 NULL 时，返回的结果集为空集；
- 参数 <JSON_exp1> 对应的 JSON 数据类型必须为 OBJECT，否则报错。
举例说明
例 1 使用 jsonb_each，将最外层 JSON 对象扩展为键/值对。
select * from jsonb_each('{"a":1,"b":true,"c":null,"d":"str\ttest","e":[1,2,3],"f":{"name1":"aaa\ttest","name2":"bbb\ttest"}}');
查询结果如下 ：
行号 KEY VALUE
---------- --- -----------------------------------------
1 a 1
2 b true
3 c null
4 d "str\ttest"
5 e [1,2,3]
6 f {"name1":"aaa\ttest","name2":"bbb\ttest"}
例 2 当参数 <JSON_exp1> 为 NULL 时，返回的结果集为空集。
select * from jsonb_each(null);
查询结果如下：
未选定行
例 3 当参数 <JSON_exp1> 对应的 JSON 数据类型不是 OBJECT 时，报错。
select * from jsonb_each('[1,2,3]');
查询结果报错：
[-3117]:只能在对象上调用此函数.
18.2.2.3 jsonb_each_text
jsonb_each_text 将最外层 JSON 对象扩展为一组键/值对。
语法格式
<jsonb_each_text函数> ::= jsonb_each_text(<JSON_exp1>)
参数
<JSON_exp1>： 表示 JSON 的字符串，数据类型为 VARCHAR 或 CLOB。
返回值
返回值表的结构为（key，value）。
key：JSON 对象名称/值对中的“名称”，数据类型与参数 <JSON_exp1> 的数据类型保持一致，为 VARCHAR 或 CLOB。
value：key 对应 JSON 的字符串，数据类型与参数 <JSON_exp1> 的数据类型保持一致，为 VARCHAR 或 CLOB。
使用说明
jsonb_each_text 与 jsonb_each 的区别仅在于：jsonb_each_text 当 value 的返回结果为 JSON STRING 类型时，将其转换为相应值的字符串，例如将其中的转义字符“\t”转换为 tab 键。
举例说明
例 使用 jsonb_each_text，将最外层 JSON 对象扩展为键/值对。
select * from jsonb_each_text('{"a":1,"b":true,"c":null,"d":"str\ttest","e":[1,2,3],"f":{"name1":"aaa\ttest","name2":"bbb\ttest"}}');
查询结果如下：
行号 KEY VALUE
---------- --- -----------------------------------------
1 a 1
2 b true
3 c null
4 d str test
5 e [1,2,3]
6 f {"name1":"aaa\ttest","name2":"bbb\ttest"}
其中，对于字符串“str\ttext”，字符串中的转义字符“\t”被改写成了对应的 tab 键。
18.2.2.4 jsonb_array_elements
jsonb_array_elements 将 JSON 数组扩展为 JSON 值的集合。
语法格式
<jsonb_array_elements函数> ::= jsonb_array_elements(<JSON_exp1>)
参数
<JSON_exp1>： 表示 JSON 的字符串，数据类型为 VARCHAR 或 CLOB。
返回值
返回值表的结构为（value）。
value：数组下标对应 JSON 的字符串，数据类型与参数 <JSON_exp1> 的数据类型保持一致，为 VARCHAR 或 CLOB。
使用说明
- 当参数 <JSON_exp1> 为 NULL 时，返回的结果集为空集；
- 参数 <JSON_exp1> 对应的 JSON 数据类型必须为 ARRAY，否则报错。
举例说明
例 1 使用 jsonb_array_elements，将 JSON 数组扩展为 JSON 值的集合。
select * from jsonb_array_elements('[1, true, null, "str\ttest", [1,2,3], {"name1":"aaa\ttest","name2":"bbb\ttest"}]');
查询结果如下：
行号 VALUE
---------- -----------------------------------------
1 1
2 true
3 null
4 "str\ttest"
5 [1,2,3]
6 {"name1":"aaa\ttest","name2":"bbb\ttest"}
例 2 当参数 <JSON_exp1> 为 NULL 时，返回的结果集为空集。
select * from jsonb_array_elements(null);
查询结果如下：
未选定行
例 3 当参数 <JSON_exp1> 对应的 JSON 数据类型不是 ARRAY 时，报错。
select * from jsonb_array_elements('1');
查询结果如下：
[-3118]:只能从数组中提取元素.
18.2.2.5 jsonb_array_elements_text
jsonb_array_elements_text 将 JSON 数组扩展为 JSON 值的集合。
语法格式
<jsonb_array_elements_text函数> ::= jsonb_array_elements_text(<JSON_exp1>)
参数
<JSON_exp1>： 表示 JSON 的字符串，数据类型为 VARCHAR 或 CLOB。
返回值
返回值表的结构为（value）。
value：数组下标对应 JSON 的字符串，数据类型与参数 <JSON_exp1> 的数据类型保持一致，为 VARCHAR 或 CLOB。
使用说明
jsonb_array_elements_text 与 jsonb_array_elements 的区别仅在于：jsonb_array_elements_text 当 value 的返回结果为 JSON STRING 类型时，将其转换为相应值的字符串，例如将其中的转义字符“\t”转换为 tab 键。
举例说明
例 使用 jsonb_array_elements_text，将 JSON 数组扩展为 JSON 值的集合。
select * from jsonb_array_elements_text('[1, true, null, "str\ttest", [1,2,3], {"name1":"aaa\ttest","name2":"bbb\ttest"}]');
查询结果如下：
行号 VALUE
---------- -----------------------------------------
1 1
2 true
3 null
4 str test
5 [1,2,3]
6 {"name1":"aaa\ttest","name2":"bbb\ttest"}
其中，对于字符串“str\ttext”，字符串中的转义字符“\t”被改写成了对应的 tab 键。
18.2.2.6 jsonb_strip_nulls
当 JSON 对象 OBJECT 名称/值对中的值为 NULL 时，jsonb_strip_nulls 忽略该名称/值对，并返回处理后的 JSON 字符串。
语法格式
<jsonb_strip_nulls函数> ::= jsonb_strip_nulls(<JSON_exp1>)
参数
<JSON_exp1>： 表示 JSON 的字符串，数据类型为 VARCHAR 或 CLOB。
返回值
返回结果为 JSON 字符串，数据类型与参数 <JSON_exp1> 的数据类型保持一致，为 VARCHAR 或 CLOB。
使用说明
- 当参数 <JSON_exp1> 为 NULL 时，返回 NULL；
- 该函数不会忽略除 OBJECT 类型外其他 JSON 数据类型中的 NULL 值。
举例说明
例 1 使用 jsonb_strip_nulls，忽略“值”为 NULL 的 OBJECT 名称/值对。
select jsonb_strip_nulls('[1, null, "str", [1,2,null], {"a":null}, {"b":2, "c":null}]') from dual;
查询结果如下：
行号 JSONB_STRIP_NULLS('[1,null,"str",[1,2,null],{"a":null},{"b":2,"c":null}]')
---------- --------------------------------------------------------------
1 [1,null,"str",[1,2,null],{},{"b":2}]
例 2 当参数 <JSON_exp1> 为 NULL 时，返回 NULL。
select jsonb_strip_nulls(null) from dual;
查询结果如下：
行号 JSONB_STRIP_NULLS(NULL)
---------- -----------------------
1 NULL
18.2.2.7 jsonb_set
jsonb_set 替换 JSONB 字符串中用户指定的项。
语法格式
<jsonb_set函数> ::= jsonb_set(<JSONB_exp1>, <path_exp2>, <JSONB_exp3>[, <exp4>])
参数
<JSONB_exp1>：表示目标 JSONB 的字符串，数据类型为 VARCHAR 或 CLOB，其内容必须对应的 JSONB 数据类型的 OBJECT 或 ARRAY。
<path_exp2>：路径表达式，以“{”开始，并以“}”结束。函数通过 <path_exp2> 指定的路径在 <JSONB_exp1> 中查找相应的项并进行替换。该路径表达式的语法图如下：
“路径标识符”表示 JSONB 数组 ARRAY 的下标，或者 JSONB 对象 OBJECT 的名称/值对的“名称”。当“路径标识符”表示 JSONB 数组 ARRAY 的下标且为负整数时，表示从 JSONB 数组的末尾开始计数，-1 表示数组末尾的第一位。路径表达式中可以包含多个“路径标识符”，以“,”分隔。
<JSONB_exp3>：表示新替换 JSONB 的字符串，数据类型为 VARCHAR 或 CLOB。
<exp4>：表示当 <path_exp2> 指定的项不存在时，是否新增 <path_exp2> 指定的项，并将值设为 <JSONB_exp3>，取值为 TRUE（是）、FALSE（否），缺省为 TRUE。
返回值
替换后的 JSONB 字符串，数据类型与参数 <JSONB_exp1> 的数据类型保持一致，为 VARCHAR 或 CLOB。
使用说明
- 当参数 <JSONB_exp1> 为 NULL 时，返回 NULL；
- 当参数 <path_exp2> 的“路径标识符”对应项的 JSONB 类型是 ARRAY 时，该“路径标识符”必须为整数，否则报错；
- 当参数 <path_exp2> 的“路径标识符”对应项的 JSONB 类型是 OBJECT 时，该“路径标识符”无论是数值还是字符串都将作为 OBJECT 名称/值对中的“名称”处理；
- 当 OBJECT 名称/值对中的“名称”包含转义字符时，“路径标识符”描述该“名称”时需要将转义字符改写为相应字符。例如名称/值对中的“名称”为“a\tb”，“路径标识符”需要写成“a b”（中间为 tab 键），才能成功查找到指定项。
举例说明
例 1 使用 jsonb_set，替换 JSONB 字符串中的一项。
1.替换为 NUMBER 类型
select jsonb_set('[{"f1":{"a":1}}]','{0,f1,a}','15');
查询结果如下：
行号 JSONB_SET('[{"f1":{"a":1}}]','{0,f1,a}','15')
---------- ---------------------------------------------
1 [{"f1":{"a":15}}]
2.替换为 STRING 类型
select jsonb_set('[{"f1":{"a":1}}]','{0,f1,a}','"str"');
查询结果如下：
行号 JSONB_SET('[{"f1":{"a":1}}]','{0,f1,a}','"str"')
---------- ------------------------------------------------
1 [{"f1":{"a":"str"}}]
3.<path_exp2> 指定项不存在，且<exp4> 为 TRUE，则 <JSONB_exp1> 新增一项
select jsonb_set('[{"f1":{"a":1}}]','{0,f1,b}','15',true);
查询结果如下：
行号 JSONB_SET('[{"f1":{"a":1}}]','{0,f1,b}','15',TRUE)
---------- --------------------------------------------------
1 [{"f1":{"a":1,"b":15}}]
- <path_exp2> 指定项不存在，且<exp4> 为 FALSE，则 <JSONB_exp1> 保持不变
select jsonb_set('[{"f1":{"a":1}}]','{0,f1,b}','15',false);
查询结果如下：
行号 JSONB_SET('[{"f1":{"a":1}}]','{0,f1,b}','15',FALSE)
---------- ---------------------------------------------------
1 [{"f1":{"a":1}}]
例 2 当参数 <JSONB_exp1> 为 NULL 时，返回 NULL。
select jsonb_set(null,'{0,f1,a}','15');
查询结果如下：
行号 JSONB_SET(NULL,'{0,f1,a}','15')
---------- -------------------------------
1 NULL
例 3 当参数 <JSONB_exp1> 对应的 JSONB 数据类型不是 OBJECT 或 ARRAY 时，报错。
select jsonb_set('1','{}','15');
查询结果如下：
[-3114]:无法在标量中设置路径.
18.2.2.8 jsonb_object_agg
jsonb_object_agg 将两个参数聚合成一个 JSON 对象 OBJECT。
语法格式
<jsonb_object_agg函数> ::= jsonb_object_agg(<name>, <value>[, IS_JSONB])
参数
<name>：作为 JSON 对象 OBJECT 名称/值对中的“名称”，数据类型为 VARCHAR。
<value>：作为 JSON 对象 OBJECT 名称/值对中的“值”，支持的数据类型包括：VARCHAR、CLOB、VARBINARY、BLOB、INT、BIGINT、DEC 以及 JSON 类型。
IS_JSONB：表示参数<value> 是否为 JSONB 字符串，取值为 0（否）、1（是），缺省为 0。IS_JSONB 仅在参数<value> 的数据类型为 VARCHAR 或 CLOB 时才有效，其他情况下忽略该参数值。
返回值
返回结果为 CLOB 数据类型，表示聚合成的 JSON 字符串。
使用说明
- 参数<name> 的值不能为 NULL，否则报错；
- 如果参数<name> 或<value> 的值中包含转义字符，则函数将转义字符改写为“\”表示的转义字符。例如<value> 的值为“a b”（中间为 tab 键），则聚合时改写为“a\tb”。
举例说明
例 1 使用 jsonb_object_agg，将表中的两列聚合成 JSON 对象 OBJECT。
drop table test;
create table test(c1 varchar(20),c2 varchar(20));
insert into test values('a',1);
insert into test values('b','true');
insert into test values('c',null);
insert into test values('d','[1,2,3]');
insert into test values('e','str\ttest');
insert into test values('f','"str test"');
insert into test values('str test','{"name":"str test"}');
select jsonb_object_agg(c1,c2) from test;
查询结果如下：
行号 JSONB_OBJECT_AGG(C1,C2)
---------- -------------------------------
1 {"a":"1","b":"true","c":null,"d":"[1,2,3]","e":"str\\ttest","f":"\"str\ttest\"","str\ttest":"{\"name\":\"str\ttest\"}"}
可以看出，c1 和 c2 列中的“\”、“"”以及 tab 键聚合时分别被改写成了“\\”、“"”以及“\t”。
例 2 参数<name> 的值为 NULL，报错。
SQL> drop table test;
SQL> create table test(c1 varchar(20),c2 varchar(20));
SQL> insert into test values(null,1);
SQL> select jsonb_object_agg(c1,c2) from test;
[-3116]:字段名能为空.
18.2.2.9 jsonb_concat
jsonb_concat 将两个 JSONB 字符串合并成一个 JSONB 字符串。
语法格式
<jsonb_concat函数> ::= jsonb_concat(<JSONB_exp1>,<JSONB_exp2>)
参数
<JSONB_exp1>：表示 JSONB 的字符串，数据类型为 VARCHAR 或 CLOB。
<JSONB_exp2>：表示 JSONB 的字符串，数据类型为 VARCHAR 或 CLOB。
返回值
合并后的 JSONB 字符串。如果 <JSON_exp1> 和 <JSON_exp2> 的数据类型均为 VARCHAR，则返回值的数据类型也为 VARCHAR；否则，返回值的数据类型为 CLOB。
使用说明
- 当参数 <JSONB_exp1> 或 <JSONB_exp2> 为 NULL 时，则返回 NULL；
- 当参数 <JSONB_exp1> 或 <JSONB_exp2> 不是 JSON 字符串时，则报错；
- 标量类型数据（非 object 或 array）与标量类型数据的合并结果类型为 array；
- 不支持标量类型数据与 object 类型数据进行合并；
- 标量类型数据与 array 类型数据的合并结果类型为 array；
- object 类型数据与 array 类型数据的合并结果类型为 array；
- object 类型数据与 object 类型数据的合并结果类型为 object；
- array 类型数据与 array 类型数据的合并结果类型为 array。
举例说明
例 1 参数 <JSONB_exp1> 为 NULL 时，返回 NULL。
select jsonb_concat(null,'1');
查询结果如下：
行号 JSONB_CONCAT(NULL,'1')
---------- ----------------------
1 NULL
例 2 标量类型数据与标量类型数据的合并结果类型为 array。
select jsonb_concat('1','"abc"');
查询结果如下：
行号 JSONB_CONCAT('1','"abc"')
---------- -------------------------
1 [1,"abc"]
例 3 不支持标量类型数据与 object 类型数据进行合并。
select jsonb_concat('1','{"b":2}');
查询结果报错：
[-3119]:JSONB对象的无效串接.
例 4 jsonb_concat 函数与 jsonb_object_agg 函数配合使用。
- jsonb_concat 函数的结果作为 jsonb_object_agg 函数的参数
//jsonb_concat函数的结果作为jsonb_object_agg函数的参数
select jsonb_object_agg('a',jsonb_concat('1', '2'));
查询结果如下：
行号 JSONB_OBJECT_AGG('a',JSONB_CONCAT('1','2'))
---------- -------------------------------------------
1 {"a":[1,2]}
- 普通字符串作为 jsonb_object_agg 函数的参数
//普通字符串作为jsonb_object_agg函数的参数
select jsonb_object_agg('a','[1,2]');
查询结果如下：
行号 JSONB_OBJECT_AGG('a','[1,2]')
---------- -----------------------------
1 {"a":"[1,2]"}
可以看到，将 jsonb_concat 函数的结果作为 jsonb_object_agg 函数的<value> 参数时，返回结果 JSON 对象 OBJECT 名称/值对中的“值”没有双引号。
18.2.2.10 jsonb_build_object
jsonb_build_object 根据指定的“名称”和“值”创建 JSONB 对象。
语法格式
<jsonb_build_object函数> ::= jsonb_build_object(<exp1>, <exp2> {, <exp3>, <exp4>})
参数
<exp1>：指定“名称”，数据类型为 VARCHAR。
<exp2>：指定“值”，数据类型可以为任意类型。
返回值
JSONB 对象。
使用说明
支持指定多个“名称”和“值”，jsonb_build_object 参数的个数必须为偶数。
举例说明
例 根据表 TEST_JSONB_BUILD_OBJECT 中的各列数据创建 JSONB 对象。
DROP TABLE TEST_JSONB_BUILD_OBJECT CASCADE;
CREATE TABLE TEST_JSONB_BUILD_OBJECT(NAME VARCHAR, AGE INT, CLASS VARCHAR);
INSERT INTO TEST_JSONB_BUILD_OBJECT VALUES('张三', 10, '一班');
INSERT INTO TEST_JSONB_BUILD_OBJECT VALUES('李四', 11, '一班');
INSERT INTO TEST_JSONB_BUILD_OBJECT VALUES('王五', 11, '二班');
INSERT INTO TEST_JSONB_BUILD_OBJECT VALUES('赵六', 9, '三班');
SELECT JSONB_BUILD_OBJECT('NAME', NAME, 'AGE', AGE, 'CLASS', CLASS) FROM TEST_JSONB_BUILD_OBJECT;
查询结果如下：
行号 JSONB_BUILD_OBJECT('NAME',NAME,'AGE',AGE,'CLASS',"CLASS")
---------- ---------------------------------------------------------
1 {"AGE":10,"NAME":"张三","CLASS":"一班"}
2 {"AGE":11,"NAME":"李四","CLASS":"一班"}
3 {"AGE":11,"NAME":"王五","CLASS":"二班"}
4 {"AGE":9,"NAME":"赵六","CLASS":"三班"}
18.2.2.11 jsonb_agg
jsonb_agg 为集函数，将指定数据聚合成一个 JSONB 数组。
语法格式
<jsonb_agg函数> ::= jsonb_agg(<exp>)
参数
<exp>：指定数据，数据类型可以为任意类型。
返回值
JSONB 数组。
使用说明
支持在<exp> 参数前指定 DISTINCT 关键字，即 jsonb_agg(DISTINCT <exp>)，表示对<exp> 进行去重操作。
举例说明
例 1 将表 TEST_JSONB_AGG 中的 NAME 列数据聚合成一个 JSONB 数组。
DROP TABLE TEST_JSONB_AGG CASCADE;
CREATE TABLE TEST_JSONB_AGG(NAME VARCHAR, AGE INT, CLASS VARCHAR);
INSERT INTO TEST_JSONB_AGG VALUES('张三', 10, '一班');
INSERT INTO TEST_JSONB_AGG VALUES('李四', 11, '一班');
INSERT INTO TEST_JSONB_AGG VALUES('王五', 11, '二班');
INSERT INTO TEST_JSONB_AGG VALUES('赵六', 9, '三班');
SELECT JSONB_AGG(NAME) FROM TEST_JSONB_AGG;
查询结果如下：
行号 JSONB_AGG(NAME)
---------- -------------------------------------
1 ["张三","李四","王五","赵六"]
例 2 将表 TEST_JSONB_AGG 中的 CLASS 列数据聚合成一个 JSONB 数组，并通过指定 DISTINCT 关键字对 CLASS 列数据进行去重。
SELECT JSONB_AGG(DISTINCT CLASS) FROM TEST_JSONB_AGG;
查询结果如下：
行号 JSONB_AGG(DISTINCT"CLASS")
---------- ----------------------------
1 ["一班","二班","三班"]
例 3 jsonb_agg 和 jsonb_build_object 函数配合使用，将 jsonb_build_object 返回的 JSONB 对象聚合成一个 JSONB 数组。
SELECT JSONB_AGG(JSONB_BUILD_OBJECT('NAME', NAME, 'AGE', AGE, 'CLASS', CLASS)) FROM TEST_JSONB_AGG;
查询结果如下：
行号 JSONB_AGG(JSONB_BUILD_OBJECT('NAME',NAME,'AGE',AGE,'CLASS',"CLASS")) --------------------------------------------------------------------------
1 [{"AGE":10,"NAME":"张三","CLASS":"一班"},{"AGE":11,"NAME":"李四","CLASS":"一班"},{"AGE":11,"NAME":"王五","CLASS":"二班"},{"AGE":9,"NAME":"赵六","CLASS":"三班"}]
18.2.2.12 jsonb_build_array
json_build_array 从可变的参数列表（可以为空）中创建 JSON 数组并返回。。
语法格式
<json_build_array函数> ::= json_build_array (<exp>)
参数
返回值
表示 JSONB 数组的字符串。
使用说明
jsonb_build_array 函数与 json_build_array 函数功能一致，故可参考[18.2.1.15 json_build_array](#18.2.1.15 json_build_array)小节。
18.2.2.13 jsonb_object_keys
jsonb_object_keys 返回顶层 JSON OBJECT 键的集合。
语法格式
<jsonb_object_keys函数> ::= JSONB_OBJECT_KEYS(<jsonb_exp>)
参数
<jsonb_exp>：合法的 JSONB 字符串。
返回值
字符串集合。
使用说明
1．此函数在查询中须位于 from 项。
2．参数表示的不是 JSONB OBJECT 时报错。
3．jsonb_object_keys 相比于 json_object_keys 会对 key 进行去重和排序。
举例说明
例 1 使用 jsonb_object_keys 返回 JSON OBJECT 键的集合。会对结果的 key 进行排序。
SELECT * FROM JSONB_OBJECT_KEYS('{"F2":"ABC","F1":{"F3":"A","F4":"B"}}');
查询结果如下：
行号 JSONB_OBJECT_KEYS
---------- -----------------
1 F1
2 F2
例 2 使用 jsonb_object_keys 返回 JSON OBJECT 键的集合。会对结果的 key 进行去重。
SELECT * FROM JSONB_OBJECT_KEYS('{"F1":"ABC","F1":"X"}');
查询结果如下：
SELECT * FROM JSONB_OBJECT_KEYS('{"F1":"ABC","F1":"X"}');
其余示例与 json_object_keys 函数类似，可以参考[18.2.1.17 json_object_keys](#18.2.1.17 json_object_keys)小节。
18.2.2.14 jsonb_typeof
jsonb_typeof 返回指定 JSON 数据的 JSON 类型。
语法格式
<jsonb_typeof函数> ::= JSONB_TYPEOF(<json_exp1>)
参数
<json_exp1>：表示 JSON 的字符串，数据类型为 VARCHAR 或 CLOB。
返回值
返回 <json_exp1> 对应的 JSON 类型。
使用说明
jsonb_typeof 的用法以及示例与 json_typeof 一致，请参考[18.2.1.19 json_typeof](#18.2.1.19 json_typeof)。
18.2.3 其他函数
18.2.3.1 cast
用户可使用 cast 函数将任意类型参数转换为 JSON 类型。具体使用规则与 INI 参数 JSON_MODE 的取值有关。
1. 当 JSON_MODE=0 或 1 时。
语法格式
<cast函数> ::= CAST(<EXP> AS JSON|JSONB)
参数
返回值
表示 JSON 或 JSONB 的字符串。
使用说明
参数
举例说明
例 1 对 JSON 对象使用 CAST 函数，目标转换为 JSONB。
select cast('{"a":1}' as jsonb);
查询结果如下：
行号 CAST('{"a":1}'ASJSONB)
---------- ----------------------
1 {"a":1}
例 2 对含双引号的
select cast('"\\\\"' as json);
查询结果如下：
行号 CAST('"\\\\"'ASJSON)
---------- --------------------
1 "\\\\"
例 3 分别将目标转换为 JSON 和 JSONB。
select cast('{"a":1, "a":1}' as json);//转换为JSON
行号 CAST('{"a":1,"a":1}'ASJSONB)
---------- ----------------------------
1 {"a":1}
2 {"a":1}
select cast('{"a":1, "a":1}' as jsonb);//转换为JSONB
行号 CAST('{"a":1,"a":1}'ASJSONB)
---------- ----------------------------
1 {"a":1}
2. 当 JSON_MODE=2 时
语法格式
< cast函数> ::= CAST(<EXP> AS JSON)
参数
<EXP>：任意类型参数。
返回值
表示 JSON 的字符串。
使用说明
1．参数<EXP> 不能转化成数字、字符串、布尔值、数组、对象、null 等 JSON 类型则报错。
2．对 JSON 词法解析的规则为：
- 双引号内规则：
未配对的''后跟''，配对'\'视为''；
未配对的''后跟 b，报错；
未配对的''后跟其他的字符，忽略''；
未配对的''结尾，报错；
- 双引号外规则：
''后跟 t,n,r，视为空白符，直接用空格替换；
''后跟其他的字符，忽略''；
''结尾，报错。
举例说明
例 1 对数字使用 CAST 函数，目标转换为 JSON。
select cast(1 as json);
查询结果如下：
行号 CAST(1ASJSON)
---------- --------------------
1 1
例 2 对于含有''但不含双引号的<exp> 使用 CAST 函数。
select cast('\n\r\t1\n\r\t' as json);
查询结果如下：
行号 CAST('\n\r\t1\n\r\t'ASJSON)
---------- ---------------------------
1 1
例 3 对于双引号内含有''的<exp> 使用 CAST 函数。
select cast('\n\r\t[\n\r\t"\\\\"]' as json);
查询结果如下：
行号 CAST('\n\r\t[\n\r\t"\\\\"]'ASJSON)
---------- ----------------------------------
1 ["\\"]
例 4 对于含有''但不能转换为合法 JSON 的<exp> 使用 CAST 函数。
select cast('\n\r\t1\n\r\t2' as json);
查询结果报错：
[-3105]:JSON值语法错误.
18.3 函数参数详解
18.3.1 路径表达式
JSON 数据的查询需要使用路径表达式。路径表达式为 object 和 array 范围。使用规则如下：
- object 和 array 必须以“$”开始；
- object 紧跟“.”，则表明对象是 object，且需要指定 < 名称 >；
- object 紧跟通配符“*”，则表示 object 的所有 < 名称 >；
- array 紧跟“[”，以“]”结束，可以使用通配符“[*]”查找数组所有 < 值 >；
- array 索引可以是 0, 1, 2,…，起始值为 0；
- array 中的范围必须非递减，例如：[3, 8 to 10, 12]、[1,2,3,3 to 3,3]；
- 对于不是 array 的数据, $[0]表示本身；
- 路径最后一项为数组类型但对应的 json 非数组类型时，会创建新的数组，元素为原有 json 和加入的 json；
例
select json_set('{"a":1}','$.a[1]',2);
查询结果如下：
行号 JSON_SET('{"a":1}','$.a[1]',2)
---------- ------------------------------
1 {"a":[1,2]}
- 路径同一层不能指定多个元素路径，不能使用通配符。
例 1 路径同一层使用通配符的情况
select json_set('["abc"]','$.*','de');
查询结果报错：
[-3113]:JSON 处理错误.
例 2 路径同一层指定多个元素路径的情况
select json_set('["abc"]','$[1,2]','de');
查询结果报错：
[-3113]:JSON 处理错误.
一个正确的示例如下：
select json_value('{"a":{"b":[0,{"c":true}]}}','$.a.b[1].c');
查询结果如下：
行号 JSON_VALUE('{"a":{"b":[0,{"c":true}]}}','$.a.b[1].c')
---------- -----------------------------------------------------
1 true
18.3.2 PRETTY 和 ASCII
PRETTY 以缩进的形式显示字符，ASCII 以\uXXXX 十六进制的形式显示非 Unicode 字符，使用原则如下：
- 默认为非 PRETTY；
- 两者一起使用时，PRETTY 必须在 ASCII 之前；
- json_value 和 json_query 都可以使用 ASCII；
- 只有 json_query 可以使用 PRETTY。
举例说明
SET KEEPDATA ON
//返回结果中的换行符不用空格替代
//j_purchaseorder表的创建语句以及数据插入语句请参考18.7一个简单的例子
SELECT json_query(po_document,'$.ShippingInstructions' RETURNING VARCHAR PRETTY WITH WRAPPER ERROR ON ERROR) from j_purchaseorder;
查询结果如下：
json_query(PO_DOCUMENT,'$.ShippingInstructions'PRETTYWITHWRAPPERERROR
ONERROR)
---------- ------------------------------------------------------------
[
{
"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"
}
]
}
]
18.3.3 WRAPPER 项
只有 json_query 可以使用 <WRAPPER 项 >。缺省为 WITHOUT WRAPPER。
- WITH WRAPPER：以 array 的形式返回字符串，显示匹配路径表达式下的所有 JSON 数据，array 元素的顺序不是固定的；
- WITHOUT WRAPPER：只返回匹配路径表达式的单个 JSON object 或 array。如果是标量类型（非 object 或 array）或多于 1 条数据则报错返回；
- WITH CONDITIONAL WRAPPER：单个 JSON object 或 array 时，等价于 WITHOUT WRAPPER；其他情况等价于 WITH WRAPPER；
- WITH UNCONDITIONAL WRAPPER 和 WITH WRAPPER 是等价的；
- ARRAY 关键字可以省略，省略和不省略意义一样。
| 路径表达式 | WITH WRAPPER | WITHOUT WRAPPER | WITH CONDITIONALWRAPPER |
|---|---|---|---|
| {"id": 38327} | [{"id": 38327}] | {"id": 38327} | {"id": 38327} |
| [42, "a", true] | [[42, "a", true]] | [42, "a", true] | [42, "a", true] |
| 42 | [42] | Error | [42] |
| 42, "a", true | [42, "a", true] | Error | [42, "a", true] |
| none | [] | Error | [] |
举例说明
使用 WITH WRAPPER 关键字，以数组的形式返回查询结果。
//j_purchaseorder表的创建语句以及数据插入语句请参考18.7一个简单的例子
SELECT json_query(po_document, '$.ShippingInstructions.Phone[*].type' WITH WRAPPER) FROM j_purchaseorder;
查询结果如下：
JSON_QUERY(PO_DOCUMENT,'$.ShippingInstructions.Phone[*].type'WITHWRAPPER)
----------------------------------------------------------------------
["Office","Mobile"]
18.3.4 ERROR 项
JSON 函数和条件表达式的错误处理。只有路径表达式语法正确时，ERROR 语句才有效。缺省为 NULL ON ERROR。
- ERROR ON ERROR：出错时则返回该错误；
- NULL ON ERROR：出错时返回 NULL；
- EMPTY ON ERROR：出错时返回[]，只有 json_query 可以用；
- DEFAULT '<value>' ON ERROR：出错时返回指定的值，且<value> 必须是字符串或者数值类型常量。布尔类型有两种表示方法：一是返回'true'或'false'（VARCHAR 类型），二是返回 1 或 0（NUMBER 类型）。
举例说明
展示 <ERROR 项 > 的使用效果。
例 1 返回错误的信息
SELECT JSON_VALUE('[1,2]','$[0,1]' ERROR ON ERROR) FROM DUAL;
查询结果报错：
[-3107]:JSON_VALUE 求值为多个值
例 2 返回错误的信息
SELECT JSON_VALUE('[[1]]','$[0]' ERROR ON ERROR) FROM DUAL;
查询结果报错：
[-3106]:JSON_VALUE 的计算结果为非标量值
例 3 用 DEFAULT '<value>' ON ERROR 返回错误时指定的值，此例返回值类型与值不对应
SELECT JSON_VALUE('[1]','$[1]' RETURNING VARCHAR DEFAULT 1 ON ERROR) FROM DUAL;
查询结果报错：
[-3109]:默认值不匹配在 RETURNING 子句中定义的类型
例 4 用 DEFAULT '<value>' ON ERROR 返回错误时指定的值
SELECT JSON_VALUE('[aa]','$[0]' RETURNING number default '1' on error) FROM DUAL;
查询结果如下：
JSON_VALUE('[aa]','$[0]'RETURNINGNUMBERDEFAULT'1'ONERROR)
---------------------------------------------------------
1
18.4 操作符
18.4.1 <JSON_exp1> :: JSON
<JSON_exp1>::JSON 检查 <JSON_exp1> 是否为合法的 JSON 字符串。
语法格式
<JSON_exp1> :: JSON
参数
<JSON_exp1>：表示 JSON 的字符串，数据类型为 VARCHAR 或 CLOB。
返回值
若 <JSON_exp1> 为合法的 JSON 字符串，则返回 JSON 字符串；否则报错。
使用说明
- 当参数 <JSON_exp1> 为 NULL 时，返回 NULL；
- 参数 <JSON_exp1> 中出现的字符串必须使用双引号括起来，否则报错。
举例说明
例 1 参数 <JSON_exp1> 为 NULL 时，返回 NULL。
select null::JSON;
查询结果如下：
行号 NULL::JSON
---------- -----------
1 NULL
例 2 参数 <JSON_exp1> 为合法的 JSON 字符串，返回 JSON 字符串。
//<JSON_exp1>参数内容对应的JSON类型为string
select '"str"'::JSON;
//查询结果如下：
行号 '"str"'::JSON
---------- -------------
1 "str"
//<JSON_exp1>参数内容对应的JSON类型为number
select '-1.2'::JSON;
//查询结果如下：
行号 '-1.2'::JSON
---------- ------------
1 -1.2
//<JSON_exp1>参数内容对应的JSON类型为boolean
select 'true'::JSON;
//查询结果如下：
行号 'true'::JSON
---------- ------------
1 true
//<JSON_exp1>参数内容对应的JSON类型为object
select '{"b":1, "a":1, "a":3, "a":2}'::JSON;
//查询结果如下：
行号 '{"b":1,"a":1,"a":3,"a":2}'::JSON
---------- ---------------------------------
1 {"b":1,"a":1,"a":3,"a":2}
//<JSON_exp1>参数内容对应的JSON类型为array
select '[1, true, "a", {"b":1}]'::JSON;
//查询结果如下：
行号 '[1,true,"a",{"b":1}]'::JSON
---------- ----------------------------
1 [1,true,"a",{"b":1}]
例 3 若参数 <JSON_exp1> 为非法的 JSON 字符串，则报错。
select '{"b":1'::JSON;
查询结果报错：
[-3105]:JSON值语法错误.
18.4.2 <JSON_exp1> :: JSONB
<JSON_exp1>::JSON 检查 <JSON_exp1> 是否为合法的 JSON 字符串，若是，则将其转换为对应的 JSONB 字符串，即对其中的 OBJECT 名称/值对进行排序去重，并返回 JSONB 字符串。
语法格式
<JSON_exp1> :: JSONB
参数
<JSON_exp1>：表示 JSON 的字符串，数据类型为 VARCHAR 或 CLOB。
返回值
返回值
若 <JSON_exp1> 为合法的 JSON 字符串，则返回其对应的 JSONB 字符串；否则报错。
使用说明
- 当参数 <JSON_exp1> 为 NULL 时，返回 NULL；
- 参数 <JSON_exp1> 中出现的字符串必须使用双引号括起来，否则报错；
- 自动对参数 <JSON_exp1> 中的 OBJECT 名称/值对中的“名称”进行排序去重，去重时仅保留输入的最后一个 OBJECT 名称/值对。
举例说明
例 1 参数 <JSON_exp1> 为 NULL 时，返回 NULL。
select null::JSONB;
查询结果如下：
行号 NULL::JSONB
---------- -----------
1 NULL
例 2 参数 <JSON_exp1> 为合法的 JSON 字符串，返回其对应的 JSONB 字符串。
--<JSON_exp1>参数内容对应的JSON类型为string
select '"str"'::JSONB;
--查询结果如下：
行号 '"str"'::JSONB
---------- --------------
1 "str"
--<JSON_exp1>参数内容对应的JSON类型为number
select '-1.2'::JSONB;
--查询结果如下：
行号 '-1.2'::JSONB
---------- -------------
1 -1.2
--<JSON_exp1>参数内容对应的JSON类型为boolean
select 'true'::JSONB;
--查询结果如下：
行号 'true'::JSONB
---------- -------------
1 true
--<JSON_exp1>参数内容对应的JSON类型为object
--自动对object中的名称/值对进行排序去重
select '{"b":1, "a":1, "a":3, "a":2}'::JSONB;
--查询结果如下：
行号 '{"b":1,"a":1,"a":3,"a":2}'::JSONB
---------- ----------------------------------
1 {"a":2,"b":1}
--<JSON_exp1>参数内容对应的JSON类型为array
--array中包含object，自动对object中的名称/值对进行排序去重
select '[1, true, "a", {"b":{"a":1, "a":3}}]'::JSONB;
--查询结果如下：
行号 '[1,true,"a",{"b":{"a":1,"a":3}}]'::JSONB
---------- -----------------------------------------
1 [1,true,"a",{"b":{"a":3}}]
例 3 <JSON_exp1>::JSONB 不会对不同的 OBJECT 对象进行排序、去重。
select '[{"b":1},{"a":1},{"a":2}]'::JSONB;
查询结果如下：
行号 '[{"b":1},{"a":1},{"a":2}]'::JSONB
---------- ----------------------------------
1 [{"b":1},{"a":1},{"a":2}]
18.4.3 <JSON_exp1> :: JSON -> <exp2>
<JSON_exp1>::JSON-><exp2> 获取 JSON 数组元素或者 JSON 对象指定名称的值。
语法格式
<JSON_exp1> :: JSON -> <exp2>
或
<JSON_exp1> :: JSONB -> <exp2>
参数
<JSON_exp1>：表示 JSON 的字符串，数据类型为 VARCHAR 或 CLOB。
<exp2>：指定数组元素或对象名称。该参数的书写规则与 INI 参数 JSON_MODE 的取值有关，具体规则如下：
当 JSON_MODE=1 时，<exp2> 为 JSON 数组的索引号或 JSON 对象的名称。JSON 数组元素索引从 0 开始，数据类型为数值类型（如：INT、BIGINT、NUMBER）；JSON 对象的“名称”为字符串，对应数据类型为 VARCHAR。
当 JSON_MODE=2 时，<exp2> 为路径表达式，请参考 18.3.1 路径表达式。
返回值
返回 JSON 数组元素或者 JSON 对象指定名称的值。返回值数据类型与参数 <JSON_exp1> 的数据类型一致。
使用说明
- 当参数 <JSON_exp1> 为 NULL 时，返回 NULL；
- 当前操作符仅支持 JSON_MODE 取值 1 或 2，当 JSON_MODE 取值为 0 时，将报错；
- <JSON_exp1>::JSON 或者 <JSON_exp1>::JSONB 也可替换为列名，列的数据类型必须为 JSON 类型，即必须包含 CHECK (< 列名 > IS JSON)约束。
举例说明
例 1 JSON_MODE=1，查询 JSON 数组元素。
SELECT '[1,true,"abc"]'::JSON->0;
--查询结果如下
行号 '[1,true,"abc"]'::JSON->0
---------- -------------------------
1 1
SELECT '[1,true,"abc"]'::JSON->1;
--查询结果如下
行号 '[1,true,"abc"]'::JSON->1
---------- -------------------------
1 true
SELECT '[1,true,"abc"]'::JSON->2;
--查询结果如下
行号 '[1,true,"abc"]'::JSON->2
---------- -------------------------
1 "abc"
例 2 JSON_MODE=1，查询 JSON 对象指定名称的值。
SELECT '{"a":"bcd","e":[1,2,3]}'::JSON->'a';
--查询结果如下
行号 '{"a":"bcd","e":[1,2,3]}'::JSON->'a'
---------- ------------------------------------
1 "bcd"
SELECT '{"a":"bcd","e":[1,2,3]}'::JSON->'e';
--查询结果如下
行号 '{"a":"bcd","e":[1,2,3]}'::JSON->'e'
---------- ------------------------------------
1 [1,2,3]
例 3 JSON_MODE=2，查询 JSON 数组元素。
SELECT '[1,true,"abc"]'::JSON->'$[0]';
--查询结果如下
行号 '[1,true,"abc"]'::JSON->'$[0]'
---------- -------------------------------
1 1
SELECT '[1,true,"abc"]'::JSON->'$[1]';
--查询结果如下
行号 '[1,true,"abc"]'::JSON->'$[1]'
---------- ------------------------------
1 true
SELECT '[1,true,"abc"]'::JSON->'$[2]';
--查询结果如下
行号 '[1,true,"abc"]'::JSON->'$[2]'
---------- ------------------------------
1 "abc"
例 4 JSON_MODE=2，查询 JSON 对象指定名称的值。
SELECT '{"a":"bcd","e":[1,2,3]}'::JSON->'$.a';
--查询结果如下
行号 '{"a":"bcd","e":[1,2,3]}'::JSON->'$.a'
---------- --------------------------------------
1 "bcd"
SELECT '{"a":"bcd","e":[1,2,3]}'::JSON->'$.e';
--查询结果如下
行号 '{"a":"bcd","e":[1,2,3]}'::JSON->'$.e'
---------- --------------------------------------
1 [1,2,3]
18.4.4 <JSON_exp1> :: JSON ->> <exp2>
<JSON_exp1>::JSON->><exp2> 获取 JSON 数组元素或者 JSON 对象指定名称的值，并取消数组元素或值外层的双引号。
语法格式
<JSON_exp1> :: JSON ->> <exp2>
或
<JSON_exp1> :: JSONB ->> <exp2>
参数
<JSON_exp1>：表示 JSON 的字符串，数据类型为 VARCHAR 或 CLOB。
<exp2>：指定数组元素或对象名称。该参数的书写规则与 INI 参数 JSON_MODE 的取值有关，具体规则请参考 18.4.3 <JSON_exp1> :: JSON -> <exp2>
返回值
返回取消双引号之后的 JSON 数组元素或者 JSON 对象指定名称的值。返回值数据类型与参数 <JSON_exp1> 的数据类型一致。
使用说明
请参考 18.4.3 <JSON_exp1> :: JSON -> <exp2> 。
举例说明
例 1 JSON_MODE=1，查询 JSON 数组元素，取消数组元素外层的双引号。
SELECT '[1,true,"abc"]'::JSON->>0;
--查询结果如下
行号 '[1,true,"abc"]'::JSON->>0
---------- --------------------------
1 1
SELECT '[1,true,"abc"]'::JSON->>1;
--查询结果如下
行号 '[1,true,"abc"]'::JSON->>1
---------- --------------------------
1 true
SELECT '[1,true,"abc"]'::JSON->>2;
--查询结果如下
行号 '[1,true,"abc"]'::JSON->>2
---------- --------------------------
1 abc
例 2 JSON_MODE=1，查询 JSON 对象指定名称的值，取消值外层的双引号。
SELECT '{"a":"bcd","e":[1,2,3]}'::JSON->>'a';
--查询结果如下
行号 '{"a":"bcd","e":[1,2,3]}'::JSON->>'a'
---------- -------------------------------------
1 bcd
SELECT '{"a":"bcd","e":[1,2,3]}'::JSON->>'e';
--查询结果如下
行号 '{"a":"bcd","e":[1,2,3]}'::JSON->>'e'
---------- -------------------------------------
1 [1,2,3]
例 3 JSON_MODE=2，查询 JSON 数组元素，取消数组元素外层的双引号。
SELECT '[1,true,"abc"]'::JSON->>'$[0]';
--查询结果如下
行号 '[1,true,"abc"]'::JSON->>'$[0]'
---------- -------------------------------
1 1
SELECT '[1,true,"abc"]'::JSON->>'$[1]';
--查询结果如下
行号 '[1,true,"abc"]'::JSON->>'$[1]'
---------- -------------------------------
1 true
SELECT '[1,true,"abc"]'::JSON->>'$[2]';
--查询结果如下
行号 '[1,true,"abc"]'::JSON->>'$[2]'
---------- -------------------------------
1 abc
例 4 JSON_MODE=2，查询 JSON 对象指定名称的值，取消值外层的双引号。
SELECT '{"a":"bcd","e":[1,2,3]}'::JSON->>'$.a';
--查询结果如下
行号 '{"a":"bcd","e":[1,2,3]}'::JSON->>'$.a'
---------- ---------------------------------------
1 bcd
SELECT '{"a":"bcd","e":[1,2,3]}'::JSON->>'$.e';
--查询结果如下
行号 '{"a":"bcd","e":[1,2,3]}'::JSON->>'$.e'
---------- ---------------------------------------
1 [1,2,3]
18.4.5 <JSON_exp1> :: JSONB - <exp2>
<JSONB_exp1>::JSONB-<exp2> 删除 JSONB 数据中指定“名称”的名称/值对，或指定下标的数组元素。
语法格式
<JSON_exp1> :: JSONB - <exp2>
参数
<JSON_exp1>：表示 JSON 的字符串，数据类型为 VARCHAR 或 CLOB。
<exp2>：当 <JSON_exp1> 为 OBJECT 类型数据时，<exp2> 必须为字符串，用于指定待删除名称/值对中的“名称”；当 <JSON_exp1> 为 ARRAY 类型数据时，<exp2> 必须为整数，用于指定待删除数组元素的下标。
返回值
删除指定名称/值对或数组元素后的 JSONB 数据。
使用说明
- 若 <JSON_exp1> 或<exp2> 为 NULL，则返回 NULL；
- 若 <JSON_exp1> 为 OBJECT 类型数据，且<exp2> 为整数，则报错；
- 若 <JSON_exp1> 为 ARRAY 类型数据，且<exp2> 为字符串，则返回 <JSON_exp1> :: JSONB。
举例说明
例 1 参数<exp2> 为 NULL 时，返回 NULL。
select '{"a":1, "b":2}'::jsonb-null;
查询结果如下：
行号 '{"a":1,"b":2}'::JSONB-NULL
---------- ---------------------------
1 NULL
例 2 删除指定“名称”的名称/值对。
select '{"a":1, "b":2}'::jsonb-'a';
查询结果如下：
行号 '{"a":1,"b":2}'::JSONB-'a'
---------- --------------------------
1 {"b":2}
例 3 当 <JSON_exp1> 为 OBJECT 类型数据，且<exp2> 为整数时，报错。
select '{"a":1, "b":2}'::jsonb-1;
查询结果报错：
[-3118]:只能从数组中提取元素.
例 4 删除指定下标的数组元素。
select '[1,{"a":1, "b":2},"c"]'::jsonb-1;
查询结果如下：
行号 '[1,{"a":1,"b":2},"c"]'::JSONB-1
---------- --------------------------------
1 [1,"c"]
例 5 当 <JSON_exp1> 为 ARRAY 类型数据，且<exp2> 为字符串时，返回 <JSON_exp1> :: JSONB。
select '[1,{"a":1, "b":2},"c"]'::jsonb-'a';
查询结果如下：
行号 '[1,{"a":1,"b":2},"c"]'::JSONB-'a'
---------- ----------------------------------
1 [1,{"a":1,"b":2},"c"]
18.4.6 <JSONB_exp1> @> <JSONB_exp2>
<JSONB_exp1> @> <JSONB_exp2> 判断左边的 JSONB 是否包含右边的 JSONB。
语法格式
<JSONB_exp1> @> <JSONB_exp2>
参数
<JSONB_exp1>：JSONB 或表示 JSONB 的字符串，字符串数据类型可为 VARCHAR 或 CLOB。
<JSONB_exp2>：JSONB 或表示 JSONB 的字符串，字符串数据类型可为 VARCHAR 或 CLOB。
返回值
返回值为数值类型，1 表示包含，0 表示不包含。
使用说明：
- 该操作符仅支持 JSONB 类型数据使用，不支持 JSON 类型数据。
- 两个参数中如果有一个是 JSONB 类型，另一个参数也可以是字符串类型，它会被转化为 JSONB 类型。但不允许两个参数均为字符串类型。
举例说明
例 1 两参数均为 JSONB 类型
SELECT '{"a":1, "b":2}'::JSONB @> '{"b":2}'::JSONB;
查询结果如下：
行号 '{"a":1,"b":2}'::JSONB@>'{"b":2}'::JSONB
---------- ----------------------------------------
1 1
例 2 一参数为 JSONB，一参数为字符串，字符串被转化为 JSONB
SELECT '{"a":1, "b":2}'::JSONB @> '{"b":2, "b":2}';
查询结果如下：
行号 '{"a":1,"b":2}'::JSONB@>'{"b":2,"b":2}'
---------- ---------------------------------------
1 1
例 3 不支持 JSON
SELECT '{"a":1, "b":2}'::JSONB @> '{"b":2}'::JSON;
查询结果报错：
SELECT '{"a":1, "b":2}'::JSONB @> '{"b":2}'::JSON;
第1 行附近出现错误[-5403]:参数不兼容.
例 4 不允许两个参数均为字符串
SELECT '{"a":1, "b":2}' @> '{"b":2}';
查询结果报错：
SELECT '{"a":1, "b":2}' @> '{"b":2}';
第1 行附近出现错误[-5403]:参数不兼容.
18.5 使用 IS JSON/IS NOT JSON 条件
IS JSON/IS NOT JSON 条件，用于判断 JSON 数据合法性。当判断语法正确时，IS JSON 返回 true，IS NOT JSON 返回 false。
语法格式
<IS_JSON_clause>::=
IS [NOT] JSON [(STRICT|LAX)] [<unique_clause>]
<unique_clause>::=
WITH UNIQUE KEYS |
WITHOUT UNIQUE KEYS
详细的参数介绍如下：
■ IS JSON/IS NOT JSON
通常，IS JSON/IS NOT JSON 条件被用于 CHECK 约束中。当对 JSON 数据使用 IS JSON/IS NOT JSON 的 CHECK 约束时，在插入过程中会相对慢一些。当能够保证 JSON 数据的合法性时，可以 DISABLE 这个约束，建议不要 DROP 该约束。
举例说明
例 1 在 CHECK 中使用 IS JSON，保证插入的数据，符合 JSON 标准。
drop table json_is_json cascade;
CREATE TABLE json_is_json
(id int NOT NULL,
po_document CLOB
CONSTRAINT is_json_con CHECK (po_document IS JSON));
//创建成功
INSERT INTO json_is_json VALUES (1,'{"PONumber" : 1600, "PONumber" : 1800}');
//插入成功
INSERT INTO json_is_json VALUES (2,'OK'); //不是JSON数据，报错违反CHECK约束
INSERT INTO json_is_json VALUES (3,NULL); //IS JSON可以成功插入NULL，IS NOT JSON时也可以成功插入NULL。
例 2 在插入语句中使用 IS JSON，保证从其他表中拷入的数据，是符合 JSON 标准的。
drop table j_purchaseorder_insert;
//第一步：创建表
CREATE TABLE j_purchaseorder_insert
(c1 int NOT NULL,
c2 TIMESTAMP (6) WITH TIME ZONE,
c3 VARCHAR);
//第二步：插入数据
//j_purchaseorder表的创建语句以及数据插入语句请参考18.7一个简单的例子
INSERT INTO j_purchaseorder_insert select id, date_loaded, po_document from j_purchaseorder where po_document IS JSON;
■ LAX/STRICT
LAX/STRICT 用来规范 JSON 数据格式。STRICT 数据格式比 LAX 要求更严格。默认是 LAX。详细规则如下：
1.STRICT 和 LAX 时，true/false/null 大小写要求不同，详情请参考 true/false、null 章节；
2.IS JSON(STRICT)时，正数不能以“+”或“.”开头，不能有前导 0，不能以“.”结尾；LAX 时则可以；
3.IS JSON(STRICT)时，object 的 string:value 链表或 array 的 value 链表后不能多追加“,”；IS JSON(LAX)时则可以；
4.JSON 函数不区分 STRICT 和 LAX。
举例说明
将同一组数据插入到分别使用了 LAX/STRICT 的表中，对比区别。
第一步，在表 t_json_s 中使用 IS JOSN (STRICT)，表 t_json_l 中使用 IS JOSN (LAX)。
drop table t_json_l;
drop table t_json_s;
create table t_json_l(c1 int, c2 varchar2(100) constraint l_c2_json CHECK (c2 IS JSON (LAX)));
create table t_json_s(c1 int, c2 varchar2(100) constraint c2_json CHECK (c2 IS JSON (STRICT)));
第二步，分别插入下列数据。
//向t_json_l表中插入数据
insert into t_json_l values(1,'{''dmdatabase'':29}'); //<名称>：没有使用双引号
insert into t_json_l values(2,'{"2dmdatabase":29}'); //正确
insert into t_json_l values(3,'{"dmdatabase":.29}'); //<值>：缺失整数部分
insert into t_json_l values(4,'{"dmdatabase":NULL}'); //<值>：NULL没有小写
insert into t_json_l values(5,'{"dmdatabase":False}'); //<值>：False没有小写
insert into t_json_l values(6,'{"dmdatabase":29,"dmdatabase":30}'); //正确
insert into t_json_l values(7,'{"dm data base":29}'); //正确
insert into t_json_l values(8,'{"dmdatabase":dmdatabase}'); //<值>：字符串缺双引号
insert into t_json_l values(9,'{dmdatabase: "dmdatabase"}');//<值>：字符串前有空格
insert into t_json_l values(10,'{"dmdatabase":2s}'); //<值>：字符串没有加双引号
insert into t_json_l values(11,'{"dmdatabase":''2s''}'); //<值>：字符串缺双引号
insert into t_json_l values(12,'{dmdatabase:29 }'); //<值>：数字后有空格
//向t_json_s表中插入数据
insert into t_json_s values(1,'{''dmdatabase'':29}'); //<名称>：没有使用双引号
insert into t_json_s values(2,'{"2dmdatabase":29}'); //正确
insert into t_json_s values(3,'{"dmdatabase":.29}'); //<值>：缺失整数部分
insert into t_json_s values(4,'{"dmdatabase":NULL}'); //<值>：NULL没有小写
insert into t_json_s values(5,'{"dmdatabase":False}'); //<值>：False没有小写
insert into t_json_s values(6,'{"dmdatabase":29,"dmdatabase":30}'); //正确
insert into t_json_s values(7,'{"dm data base":29}'); //正确
insert into t_json_s values(8,'{"dmdatabase":dmdatabase}');//<值>：字符串缺双引号
insert into t_json_s values(9,'{dmdatabase: "dmdatabase"}'); //<值>：字符串前有空格
insert into t_json_s values(10,'{"dmdatabase":2s}'); //<值>：字符串没有加双引号
insert into t_json_s values(11,'{"dmdatabase":''2s''}'); //<值>：字符串缺双引号
insert into t_json_s values(12,'{dmdatabase:29 }'); //<值>：数字后有空格
第三步，查询两个表中数据，对比插入结果。
select * from t_json_l order by c1;
t_json_l 表的查询结果如下：
行号 C1 C2
---------- ----------- ---------------------------------
1 1 {'dmdatabase':29}
2 2 {"2dmdatabase":29}
3 3 {"dmdatabase":.29}
4 4 {"dmdatabase":NULL}
5 5 {"dmdatabase":False}
6 6 {"dmdatabase":29,"dmdatabase":30}
7 7 {"dm data base":29}
8 9 {dmdatabase: "dmdatabase"}
9 11 {"dmdatabase":'2s'}
10 12 {dmdatabase:29 }
10 rows got
由结果可以看出，t_json_l 表只有第 8 行和第 10 行插入失败。
select * from t_json_s order by c1;
t_json_s 表的查询结果如下：
行号 C1 C2
---------- ----------- ---------------------------------
1 2 {"2dmdatabase":29}
2 6 {"dmdatabase":29,"dmdatabase":30}
3 7 {"dm data base":29}
由结果可以看出，t_json_s 表只有第 2、6、7 行插入成功。其他没有插入成功的，插入时均报错：[-6604]:违反 CHECK 约束。
■ with unique keys/ without unique keys
与 IS JOSN 一起使用；
使用 WITH UNIQUE 时，对象中不可以有同名的名称，即名称必须唯一；
使用 WITHOUT UNIQUE 时，对象中可以有同名的名称，但是查询时只会随机选择其中一个，DM 默认选择第一个。
缺省为使用 WITHOUT UNIQUE。
举例说明
在 CHECK 约束中使用 IS JOSN WITH UNIQUE KEYS，保证插入的数据没有重复。
DROP TABLE json_unique CASCADE;
CREATE TABLE json_unique
(id int NOT NULL,
date_loaded TIMESTAMP (6) WITH TIME ZONE,
po_document CLOB
CONSTRAINT ensure_json_unique CHECK (po_document IS JSON with unique keys));
//创建成功
INSERT INTO json_unique VALUES (
111,SYSTIMESTAMP,'{"PONumber" : 1600, "PONumber" : 1800}');
执行结果报错：
[-6604]:违反CHECK约束条件 (ENSURE_JSON_UNIQUE)
18.6 视图
JSON 数据信息都存储在 DBA_JSON_COLUMNS、USER_JSON_COLUMNS 和 ALL_JSON_COLUMNS 视图中，下面进行详细介绍。
18.6.1 视图使用说明
- 当 JSON 列的 IS JSON 约束被失效后，该列仍然在视图中显示；
- 当 IS JSON 涉及多列时，则所有涉及的列均在视图中显示。例如：<u>c1||c2 is json</u>，则 c1 和 c2 列均在视图中显示；
- 如果 IS JSON 与其他约束进行”与”(AND)运算时，则所有涉及的列均在视图中显示。例如：<u>c1 = '1' and c2 is json</u>；
- 如果 IS JSON 与其他约束进行”或”(OR)运算时，则所有列均不在视图中显示。例如：<u>c1 is json OR c2 < 1000</u>，即使是<u>c1 is json OR c2 is json</u> 也不行；
- 如果 IS NOT JSON 作为 CHECK 约束时，则该列不在视图中显示。例如：<u>C1 is not json</u>。同理:<u>c1||c2 is not json</u>，则 c1 和 c2 均不能在下列视图中显示；
- 如果 NOT IS JSON 作为 CHECK 约束时，则该列也不能在下列视图中显示。例如：<u>not( c2 is json )</u>；
- 当虚拟列相关的实际列使用 IS JSON 作为 CHECK 约束时，该虚拟列不在视图中显示；当虚拟列使用 IS JSON 作为 CHECK 约束时，仅该虚拟列在视图中显示，实际列则不在视图中显示。
18.6.2 DBA_JSON_COLUMNS
显示数据库中所有的 JSON 数据信息。
| 列名 | 数据类型 | 说明 |
|---|---|---|
| OWNER | VARCHAR(128) | 模式名 |
| TABLE_NAME | VARCHAR(128) | 表名 |
| COLUMN_NAME | VARCHAR(128) | 列名 |
| FORMAT | VARCHAR(4) | 格式化。统一为 TEXT |
| DATA_TYPE | VARCHAR(11) | 列的数据类型。可能的取值：VARCHAR2、CLOB、LONGVARCHAR、TEXT、UNDEFINED(对于 CHAR 类型、VARCHAR 类型) |
18.6.3 USER_JSON_COLUMNS
显示当前用户所拥有的 JSON 数据信息。该视图比 DBA_JSON_COLUMNS 视图少了一列 OWNER。
18.6.4 ALL_JSON_COLUMNS
显示当前用户有权访问的 JSON 数据信息。该视图列与 DBA_JSON_COLUMNS 完全相同。
18.7 一个简单的例子
在数据库表中插入 JSON 数据，并查询它。
第一步，在 CHECK 中使用 IS JSON，保证插入的数据符合 JSON 标准格式。
drop table j_purchaseorder;
//创建表
CREATE TABLE j_purchaseorder
(id int NOT NULL,
date_loaded TIMESTAMP (6) WITH TIME ZONE,
po_document VARCHAR
CONSTRAINT ensure_json CHECK (po_document IS JSON));
//插入数据。插入po_document列的就是JSON数据，也可以使用dmfldr工具导入数据。
INSERT INTO j_purchaseorder VALUES (1,SYSTIMESTAMP,'{
"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" : true,
"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}]}');
SELECT json_query(po_document,'$.*' RETURNING VARCHAR PRETTY WITH WRAPPER ERROR ON ERROR) from j_purchaseorder;
查询结果如下：
行号 json_query(PO_DOCUMENT,'$.*'PRETTYWITHWRAPPERERRORONERROR)
------------------------------------------------------------------------
1 [
1600,
"ABULL-20140421",
"Alexis Bull",
"ABULL",
"A50",
{
"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"
}
]
},
null,
true,
[
{
"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
}
]
]
