JSON类型¶
简介¶
JSON(JavaScript Object Notation)是一种轻量级的数据交换格式，它采用完全独立于编程语言的 文本格式来存储和表示数据，简洁和清晰的层次结构使得 JSON 成为理想的数据交换语言。 易于人阅读和编写，同时也易于机器解析和生成，并有效地提升网络传输效率。
JSON数据类型¶
JSON中的数据类型包括：对象，字符串，数字，布尔值，NULL，数组。
对象：对象在 JSON 中是使用花括号 {} 包裹起来的内容，数据结构为 {key1：value1, key2：value2, ...} 的属性-值对结构。 key 为对象的属性，value 为对应的值。属性名只能用字符串来表示，值的类型可以是对象，字符串，数字，布尔值，NULL，数组。
数组：数组在 JSON 中是方括号 [] 包裹起来的内容，数据结构为 ["java", "javascript", "vb", ...] 的索引结构。 在 JSON 中，数组的值的类型可以是对象，字符串，数字，布尔值，NULL，数组。
JSON路径¶
简介：json path值得是数据在json中所处于的位置。用户可以对指定路径或路径下的值进行操作。
json path由$ * . [] key number 构成
$ 表示根路径，所有的路径必须以$开始
[number] 表示数组的第几个元素，从0开始
.key 表示对象中的key值，获取key指向的元素
[*] 表示当前数组中任意元素
.* 表示当前对象中所有key值指向的元素
.** 表示当前及子节点中所有的元素 还有些其它的形式都在结构体JsonPathItemType中
JSON数据的存储方式¶
方式一：创建表时，直接创建json类型存储json数据。
例：create table test(j json);
方式二：创建表时，创建其它类型(目前支持clob,text和varchar)存储json数据,并使用check约束检查其有效性。
例：(1)create table test(j clob constraint ck1 check(j is json));
(2)create table test(j text constraint ck1 check(j is json));
(3)create table test(j varchar(100) constraint ck1 check(j is json));
示例：
drop table test;
drop table test2;
-- 普通表插入数据
create table test(j json);
--插入符合json格式的数据
insert into test values('{"a":"b"}'),('[1,2,3]'),('"abc"'),('1'),('true'),('null');
--插入不符合json格式的数据
insert into test values('{"a":"b":"c"}');
ERROR, invalid input syntax for type json Expected "," or "}", but found ":".JSON data, line 1: {"a":"b":...
-- 含有check约束的表插入数据
create table test2(j clob constraint ck1 check(j is json));
--插入符合json格式的数据
insert into test2 values('{"a":"b"}'),('[1,2,3]'),('"abc"'),('1'),('true'),('null');
--插入不符合json格式的数据
insert into test2 values('{"a":"b":"c"}');
ERROR, INSERT的数据违反了关系TEST2上的约束CK1
drop table test;
drop table test2;
向JSON中写入数据¶
-- 表中JSON插入数字，方法一
create table test(j json);
insert into test values(1);
select json_type(j) from test;
JSON_TYPE(text) |
---------------------
NUMBER |
总数目：1
-- 表中JSON插入数字，方法二
delete from test;
insert into test values('1');
select json_type(j) from test;
JSON_TYPE(text) |
---------------------
NUMBER |
总数目：1
-- 表中JSON插入布尔值，方法一
delete from test;
insert into test values(true);
select json_type(j) from test;
JSON_TYPE(text) |
---------------------
BOOLEAN |
总数目：1
-- 表中JSON插入布尔值，方法二
delete from test;
insert into test values('true');
select json_type(j) from test;
JSON_TYPE(text) |
---------------------
BOOLEAN |
总数目：1
-- 表中JSON插入NULL值
delete from test;
insert into test values('null');
select json_type(j) from test;
JSON_TYPE(text) |
---------------------
NULL |
总数目：1
-- 表中JSON插入字符串
delete from test;
insert into test values('"abcd"');
select json_type(j) from test;
JSON_TYPE(text) |
---------------------
STRING |
总数目：1
-- 表中JSON插入数组，方法一，直接写入
delete from test;
insert into test values('[1,2,3]');
select json_type(j) from test;
JSON_TYPE(text) |
---------------------
ARRAY |
总数目：1
-- 表中JSON插入数组，方法二，通过JSON_ARRAY()函数构造
delete from test;
insert into test values((select json_array(1,2,3) from dual));
select json_type(j) from test;
JSON_TYPE(text) |
---------------------
ARRAY |
总数目：1
-- 表中JSON插入对象，方法一，直接写入
delete from test;
insert into test values('{"a":"b","c":"d"}');
select json_type(j) from test;
JSON_TYPE(text) |
---------------------
OBJECT |
总数目：1
-- 表中JSON插入对象，方法二，通过JSON_OBJECT()函数构造
delete from test;
insert into test values((select json_object('a','b','c','d') from dual));
select json_type(j) from test;
JSON_TYPE(text) |
---------------------
OBJECT |
总数目：1
从JSON中读取数据¶
create table test(id int,j json);
insert into test values(1,'2');
insert into test values(2,'true');
insert into test values(3,'null');
insert into test values(4,'"abcdef"');
insert into test values(5,'[1,2,3]');
insert into test values(6,'{"a":"b","c":"d"}');
-- 方法一，通过select直接读取表中数据
select * from test;
ID(int) |J(json) |
--------------------------
1 |2 |
--------------------------
2 |true |
--------------------------
3 |null |
--------------------------
4 |"abcdef" |
--------------------------
5 |[1, 2, 3] |
--------------------------
6 |{"a": "b", "c|
|": "d"} |
总数目：6
-- 方法二，通过json_value读取json数据
select id,json_value(j,'$') from test;
ID(int) |JSON_VALUE(text) |
-----------------------------------
1 |2 |
-----------------------------------
2 |true |
-----------------------------------
3 |null |
-----------------------------------
4 |abcdef |
-----------------------------------
5 |[1, 2, 3] |
-----------------------------------
6 |{"a": "b", "c": "d"} |
总数目：6
-- 方法三，通过json_extract读取json数据
select id,json_extract(j,'$') from test;
ID(int) |JSON_EXTRACT(json) |
-------------------------------------
1 |2 |
-------------------------------------
2 |true |
-------------------------------------
3 |null |
-------------------------------------
4 |"abcdef" |
-------------------------------------
5 |[1, 2, 3] |
-------------------------------------
6 |{"a": "b", "c": "d"} |
总数目：6