JSON 数据类型

MatrixOne 支持由 RFC 7159 定义的原生 JSON 数据类型，可以有效访问 JSON（JavaScript 对象表示法）文档中的数据。与将 JSON 格式的字符串存储在字符串列中相比，JSON 数据类型具有以下优势：

  • 自动验证存储在 JSON 列中的 JSON 文档。无效的文档会产生报错。

  • 自动优化存储格式。存储在 JSON 列中的 JSON 文档转换为允许对文档元素进行快速读取访问的内部格式。当服务器必须读取以这种二进制格式存储的 JSON 值时，不需要从文本中解析该值。二进制格式的结构使服务器能够直接通过键或数组索引查找子对象或嵌套值。

存储 JSON 文档所需的存储空间与 BLOB 或 TEXT 大致相同。

JSON 的类型

JSON 类型有 JSON 数组和 JSON 对象。

  • JSON 数组即包含由逗号分隔并包含在 [] 字符内的值列表，如：
  1. ["abc", 10, null, true, false]
  • JSON 对象包含一组键值对，由逗号分隔并包含在 {} 字符内，如：
  1. {"k1": "value", "k2": 10}

JSON 数组和对象可以包含标量值，即字符串或数字、JSON 空字面量或 JSON 布尔真或假字面量。JSON 对象中的键值必须是字符串。JSON 也允许时间（日期，日期时间）标量值。如：

  1. ["12:18:29.000000", "2015-07-29", "2015-07-29 12:18:29.000000"]

在 JSON 数组元素和 JSON 对象键值中可嵌套，如：

  1. [99, {"id": "HK500", "cost": 75.99}, ["hot", "cold"]]
  2. {"k1": "value", "k2": [10, 20]}

JSON 值的规范化

当一个字符串被解析为有效的 JSON 文档时，它也会被规范化。这表示意味着具有与稍后在文档中找到的键重复的键的成员，从左到右读取，将被丢弃。这意味着从左往右读取时，后面出现的重复的的键值将被忽略。

将值插入 JSON 列时执行规范化，如下所示：

  1. CREATE TABLE t1 (c1 JSON);
  2. INSERT INTO t1 VALUES
  3.      ('{"x": 17, "x": "red"}'),
  4.      ('{"x": 17, "x": "red", "x": [3, 5, 7]}');
  6. mysql> SELECT c1 FROM t1;
  7. +------------------+
  8. | c1               |
  9. +------------------+
  10. | {"x": "red"}     |
  11. | {"x": [3, 5, 7]} |
  12. +------------------+
  13. 2 rows in set (0.01 sec)

JSON EXTREACT

JSON EXTREACT 是一个 JSON 查询函数，可用于查询 JSON 文档。

语法结构：select json_extract(jsonDoc, pathExpression);

pathExpression 是 JSON 路径表达式，即在 JSON 文档中选择一个值。

路径表达式对于提取部分 JSON 文档或修改 JSON 文档的函数很有用，指定在该文档中的哪个位置进行操作。例如，以下查询从 JSON 文档中提取具有 name 键的成员的值：

  1. mysql> SELECT JSON_EXTRACT('{"id": 14, "name": "Aztalan"}', '$.name');
  2. +-----------------------------------------------------+
  3. | json_extract({"id": 14, "name": "Aztalan"}, $.name) |
  4. +-----------------------------------------------------+
  5. | "Aztalan"                                           |
  6. +-----------------------------------------------------+
  7. 1 row in set (0.00 sec)

路径表达式必须以 $ 字符开头：

  • , 后跟键名，使用给定键命名对象中的成员。键名需要使用双引号包含。

  • [N]：选择数组的 path 后，将数组中位置 N 处的值命名。数组位置是从零开始的整数。如果数组是负数，则产生报错。

  • 路径可以包含 * 或 ** 通配符：

  • .[*] 计算 JSON 对象中所有成员的值。

  • [*] 计算 JSON 数组中所有元素的值。

  • prefix**suffix：计算以命名前缀开头并以命名后缀结尾的所有路径。

  • 文档中不存在的路径（或不存在的数据）评估为 NULL。

示例：

如下一组 JSON 数组：

  1. [3, {"a": [5, 6], "b": 10}, [99, 100]]

  • $[0] 表示 3。

  • $[1] 表示 {“a”: [5, 6], “b”: 10}。

  • $[2] 表示 [99, 100]。

  • $[3] 为 NULL (数组路径从 $[0] 开始，而 $[3] 表示第四组数据，这组数据不存在)。

由于 $[1] 与 $[2] 计算为非标量值，那么表达式可以嵌套。例如：

  • $[1].a 表示 [5, 6]。

  • $[1].a[1] 表示 6。

  • $[1].b 表示 10。

  • $[2][0] 表示 99。

键名在路径表达式中需要使用双引号。$ 引用这个键值，也需要加双引号：

  1. {"a fish": "shark", "a bird": "sparrow"}

这两个键都包含一个空格，必须用引号引起来：

  • $."a fish" 表示 shark。

  • $."a bird" 表示 sparrow。

使用通配符 $ 的路径可以为包含多个值的数组：

  1. mysql> select JSON_EXTRACT('{"a": 1, "b": 2, "c": [3, 4, 5]}', '$.*');
  2. +---------------------------------------------------------+
  3. | JSON_EXTRACT('{"a": 1, "b": 2, "c": [3, 4, 5]}', '$.*') |
  4. +---------------------------------------------------------+
  5. | [1, 2, [3, 4, 5]]                                       |
  6. +---------------------------------------------------------+
  8. mysql> SELECT JSON_EXTRACT('{"a": 1, "b": 2, "c": [3, 4, 5]}', '$.c[*]');
  9. +------------------------------------------------------------+
  10. | JSON_EXTRACT('{"a": 1, "b": 2, "c": [3, 4, 5]}', '$.c[*]') |
  11. +------------------------------------------------------------+
  12. | [3, 4, 5]                                                  |
  13. +------------------------------------------------------------+

在下述示例中，路径 $**.b 计算为多个路径 ($.a.b 和 $.c.b) 并生成匹配路径值的数组：

  1. mysql> select JSON_EXTRACT('{"a": {"b": 1}, "c": {"b": 2}}', '$**.b');
  2. +---------------------------------------------------------+
  3. | JSON_EXTRACT('{"a": {"b": 1}, "c": {"b": 2}}', '$**.b') |
  4. +---------------------------------------------------------+
  5. | [null, 1, 2]                                                  |
  6. +---------------------------------------------------------+

在下述示例中，将展示从列中查询 JSON 值：

  1. create table t1 (a json,b int);
  2. insert into t1(a,b) values ('{"a":1,"b":2,"c":3}',1);
  4. mysql> select json_extract(t1.a,'$.a') from t1 where t1.b=1;
  5. +-------------------------+
  6. | json_extract(t1.a, $.a) |
  7. +-------------------------+
  8. | 1                       |
  9. +-------------------------+
  10. 1 row in set (0.00 sec)
  12. insert into t1(a,b) values ('{"a":4,"b":5,"c":6}',2);
  14. mysql> select json_extract(t1.a,'$.b') from t1 where t1.b=2;
  15. +-------------------------+
  16. | json_extract(t1.a, $.b) |
  17. +-------------------------+
  18. | 5                       |
  19. +-------------------------+
  20. 1 row in set (0.00 sec)
  22. mysql> select json_extract(t1.a,'$.a') from t1;
  23. +-------------------------+
  24. | json_extract(t1.a, $.a) |
  25. +-------------------------+
  26. | 1                       |
  27. | 4                       |
  28. +-------------------------+
  29. 2 rows in set (0.00 sec)
  31. insert into t1(a,b) values ('{"a":{"q":[1,2,3]}}',3);
  33. mysql> select json_extract(t1.a,'$.a.q[1]') from t1 where t1.b=3;
  34. +------------------------------+
  35. | json_extract(t1.a, $.a.q[1]) |
  36. +------------------------------+
  37. | 2                            |
  38. +------------------------------+
  39. 1 row in set (0.01 sec)
  41. insert into t1(a,b) values ('[{"a":1,"b":2,"c":3},{"a":4,"b":5,"c":6}]',4);
  43. mysql> select json_extract(t1.a,'$[1].a') from t1 where t1.b=4;
  44. +----------------------------+
  45. | json_extract(t1.a, $[1].a) |
  46. +----------------------------+
  47. | 4                          |
  48. +----------------------------+
  49. 1 row in set (0.00 sec)