JSON数据类型

📄字数 2.5K

👁️阅读量 加载中...

JSON存储类型

数据采用大对象存储，支持最大2GB文本。

JSON数据格式

JSON数据支持存储值的基础类型为string、bool、number、null。

注意

后续JSON数据都是以JSON字符串方式展示，所以数据外部应和原有字符类型一样由单引号'包裹。

• json string是由双引号包裹的字符串：

'"中文"'

• json bool是小写 true 或 false：

'true' 'false'

• json number：

'1' '-1' '10.2'

• json null 是小写的 null：

'null'

• json array是包含在 [] 中括号之间以逗号分割的值列表：

'["abc", true, false, 1, 1.1, null]'

• json object是包含在 {} 大括号之间的多组键值对，键值必须为string：

'{"key1": "value", "key2": true, "key3": false, "key4": 1, "key5": 1.1, "key6": null}'

JSON路径表达式（JSONPath）

XuguDB支持路径表达式来检索JSON数据中特定的元素。

• 由$字符打头，代表JSON文本本身。

SQL> SELECT '{"test": 1}'->'$';

+-------------+
|    EXPR1    |
+-------------+
| {"test": 1} |
+-------------+

• . 字符用于寻找对象中键对应的值。

SQL> SELECT '{"test": 1}'->'$.test';

+-------+
| EXPR1 |
+-------+
| 1     |
+-------+

-- 或者

SQL> SELECT '{"test": 1}'->'$."test"';

+-------+
| EXPR1 |
+-------+
| 1     |
+-------+

• [N]用于寻找数组中下标为N的元素。

SQL> SELECT '[1,3,5,7]'->'$[1]';

+-------+
| EXPR1 |
+-------+
| 3     |
+-------+

• [M to N]用于寻找数组中下标 M 到 N 的元素集合。

SQL> SELECT '[1,3,5,7]'->'$[1 to 3]';

+-----------+
|   EXPR1   |
+-----------+
| [3, 5, 7] |
+-----------+

• last关键字作为数组最后一个元素或非数组元素的同义词。

SQL> SELECT '[1,3,5,7]'->'$[last]';

+-------+
| EXPR1 |
+-------+
| 7     |
+-------+

SQL> SELECT '[1,3,5,7]'->'$[last - 1 to last]';

+--------+
| EXPR1  |
+--------+
| [5, 7] |
+--------+

-- 非数组元素与直接使用 $ 相同
SQL> SELECT '"123"'->'$[last]';

+-------+
| EXPR1 |
+-------+
| "123" |
+-------+

SQL> SELECT '{"a": 1}'->'$[last]';

+----------+
|  EXPR1   |
+----------+
| {"a": 1} |
+----------+

• *通配符，代表全量元素。

SQL> SELECT '[1,2,3,4]'->'$[*]';

+--------------+
|    EXPR1     |
+--------------+
| [1, 2, 3, 4] |
+--------------+

-- 或者

SQL> SELECT '{"a": 1,"b": 2}'->'$.*';

+--------+
| EXPR1  |
+--------+
| [1, 2] |
+--------+

• **深度查找。

SQL> SELECT '[1,2,[3,3,3],4]'->'$**[1]';

+--------+
| EXPR1  |
+--------+
| [2, 3] |
+--------+

-- 或者

SQL> SELECT '{"a": 1,"b": {"a": 2}}'->'$**.a';

+--------+
| EXPR1  |
+--------+
| [1, 2] |
+--------+

JSON比较与排序

JSON类型支持=、<> 、> 、>= 、< 、 <=比较运算符。

以下列表展示了JSON类型排序规则，优先级从最高到最低：

1. BOOL
2. ARRAY
3. OBJECT
4. STRING
5. NUMBER
6. NULL

相同类型按照以下排序规则：

• BOOL
  false小于true。

• ARRAY
  由第一个有差异的元素决定。该位置较小的数组首先排序。如果较短的数组的所有值都等于长数组中对应的值，则较短的数组首先排序。

[] < ["a"] < ["ab"] < ["ab", "cd", "ef"] < ["ab", "ef"]

• OBJECT
  由第一个有差异的键值对决定。键小优先排序，键一样，值小优先排序。如果较短的对象的所有键值对都包含在长对象中，则较短的对象优先排序。存在相同的键值，则相等。

{} < {"a": 1} < {"a": 2} < {"ab": 1} < {"b": 1} < {"b": 1, "c": 1}

{"a": 1, "b": 2} = {"b": 2, "a": 1}

• STRING
  按照字典序排序。

• NUMBER
  按照大小排序。

JSON类型的增删查改

JSON类型必须是符合JSON格式的文本。

示例：

SQL> CREATE TABLE t_json(c_id INT PRIMARY KEY, c_json JSON);

SQL> INSERT INTO t_json VALUES(1, '[1,2,3]')(2, '{"中文key": "中文value"}');

-- 查看新插入的值
SQL> SELECT * FROM t_json;

+------+--------------------------+
| C_ID |          C_JSON          |
+------+--------------------------+
| 1    | [1, 2, 3]                |
| 2    | {"中文key": "中文value"} |
+------+--------------------------+

-- 更新c_json列
SQL> UPDATE t_json SET c_json='{"key": "value"}' WHERE c_id = 1;

-- 查看更新的后的值
SQL> SELECT * FROM t_json;

+------+--------------------------+
| C_ID |          C_JSON          |
+------+--------------------------+
| 1    | {"key": "value"}         |
| 2    | {"中文key": "中文value"} |
+------+--------------------------+

-- 删除c_id为1的行
SQL> DELETE FROM t_json WHERE c_id = 1;

-- 查看删除后的值
SQL> SELECT * FROM t_json;

+------+--------------------------+
| C_ID |          C_JSON          |
+------+--------------------------+
| 2    | {"中文key": "中文value"} |
+------+--------------------------+

数据类型特性表

数据类型	长度	Java数据类型	长度	封装器类	说明
JSON	2GB	Java.sql.String	变长	String	存储JSON数据格式字符串

JSON运算符与函数预览

名称	功能
- >	获取指定路径值
- >>	获取指定路径值并取消对JSON类型的引用
JSON_ARRAY	依据参数列表值创建 JSON 数组
JSON_ARRAY_APPEND	将值追加到 JSON 数组指定路径，并返回追加后的 JSON 数组
JSON_ARRAY_INSERT	将值插入到 JSON 数组指定路径，并返回插入后的 JSON 数组
JSON_CONTAINS	判断目标 JSON 文本是否包含候选 JSON 文本
JSON_CONTAINS_PATH	判断目标 JSON 文本指定的路径是否至少存在一个或是否全部存在
JSON_DEPTH	返回 JSON 文本的最大深度
JSON_EXTRACT	返回 JSON 文本中所有指定路径包含的值
JSON_INSERT	将值插入到 JSON 文本指定路径，并返回插入后的 JSON 文本
JSON_KEYS	返回 JSON 文本顶层对象的所有键
JSON_LENGTH	返回 JSON 文本长度
JSON_MERGE	合并两个或更多的 JSON 文本，与 JSON_MERGE_PRESERVE() 同义
JSON_MERGE_PATCH	按照 RFC 7396 规范合并两个或更多的 JSON 文本，不保留重复键的成员
JSON_MERGE_PRESERVE	合并两个或更多的 JSON 文本，将保留重复键的成员
JSON_OBJECT	构建JSON对象
JSON_OVERLAPS	对比两个 JSON 文本，判断其是否具有相同的键值对或具有相同数组元素
JSON_PRTTY	格式化JSON串
JSON_QUOTE	将参数包装为 JSON 值，用双括号包裹，并将引号等特殊序列替换为转义字符。
JSON_REMOVE	将 JSON 文本指定路径值移除，并返回移除后的 JSON 文本
JSON_REPLACE	将 JSON 文本指定路径值替换，并返回替换后的 JSON 文本
JSON_SCHEMA_VALID	根据指定模式验证指定 JSON 文本
JSON_SCHEMA_VALIDATION_REPORT	根据指定模式验证指定 JSON 文本，并返回报告 JSON 文本
JSON_SEARCH	根据指定模式验证指定 JSON 文本
JSON_SET	插入或更新 JSON 文本数据，并返回更新后的 JSON 文本
JSON_TYPE	返回 JSON 文本类型
JSON_UNQUOTE	移除 JSON 文本外层引号，并解析转义字符
JSON_VALID	验证给定值是否是合法 JSON 文本
JSON_VALUE	根据路径表达式从 JSON 文本获取值并返回，允许可选的指定返回类型
MEMBER OF	验证给定值是给定 JSON 数组的元素
JSON_ARRAYAGG	将结果集聚合为一个元素由行组成的 JSON 数组
JSON_OBJECTAGG	将两个列名或表达式作为参数，其中第一个用作键，第二个用作值，并返回一个包含键值对的 JSON 对象