JSON 函数

MatrixOne 支持以下 JSON 函数：

JSON_EXTRACT() 函数

JSON EXTRACT 是一个 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)

JSON_QUOTE() 函数

JSON_QUOTE 函数用于将一个字符串值转换为 JSON 格式中的字符串。通过使用双引号包装字符串并转义内引号和其他字符，将字符串作为 JSON 值引用，然后将结果作为 utf8mb4 字符串返回。如果参数为 NULL，则返回 NULL。

JSON_QUOTE 函数通常用于生成有效的 JSON 字符串，以包含在 JSON 文档中。

语法结构：select JSON_QUOTE(string_value);

string_value 是要转换为 JSON 字符串的字符串。该函数返回一个 JSON 格式的字符串，其中原始字符串已被引号包围并进行了适当的转义。

示例如下：

1. mysql> SELECT JSON_QUOTE('null'), JSON_QUOTE('"null"');
2. +------------------+--------------------+
3. | json_quote(null) | json_quote("null") |
4. +------------------+--------------------+
5. | "null"           | "\"null\""         |
6. +------------------+--------------------+
7. 1 row in set (0.00 sec)
8. mysql> SELECT JSON_QUOTE('[1, 2, 3]');
9. +-----------------------+
10. | json_quote([1, 2, 3]) |
11. +-----------------------+
12. | "[1, 2, 3]"           |
13. +-----------------------+
14. 1 row in set (0.00 sec)

16. mysql> SELECT JSON_QUOTE('hello world');
17. +-------------------------+
18. | json_quote(hello world) |
19. +-------------------------+
20. | "hello world"           |
21. +-------------------------+
22. 1 row in set (0.00 sec)

可以看到，原始字符串被引号包围并且字符串中的双引号也被转义了。这样，可以将其用作 JSON 格式的值，例如，将其作为 JSON 对象的属性值。

JSON_UNQUOTE() 函数

JSON_UNQUOTE() 函数用于从一个 JSON 字符串中提取一个没有引号的 JSON 值，并将其作为字符串返回。

具体来说，JSON_UNQUOTE() 函数需要一个 JSON 字符串作为输入，然后从中提取一个 JSON 值，并将其作为字符串返回。如果输入的 JSON 字符串不包含有效的 JSON 值或参数为 NULL，则函数返回 NULL。如果参数为 NULL，则返回 NULL。如果值以双引号开始和结束，但不是有效的 JSON 字符串文字，则会发生错误。

语法结构：select JSON_UNQUOTE(string_value);

在字符串中，某些序列具有特殊含义，这些序列都以反斜杠 () 开始，称为转义字符，规则如下表。对于所有其他转义序列，反斜杠将被忽略。也就是说，转义字符被解释为没有转义。例如，\x 就是 x。这些序列区分大小写。例如，\b 被解释为退格，而 \B 被解释为 B。

示例如下：

1. mysql> SET @j = '"abc"';
2. Query OK, 0 rows affected (0.00 sec)

4. mysql> SELECT @j, JSON_UNQUOTE(@j);
5. +-------+------------------+
6. | @j    | json_unquote(@j) |
7. +-------+------------------+
8. | "abc" | abc              |
9. +-------+------------------+
10. 1 row in set (0.00 sec)

12. mysql> SET @j = '[1, 2, 3]';
13. Query OK, 0 rows affected (0.00 sec)

15. mysql> SELECT @j, JSON_UNQUOTE(@j);
16. +-----------+------------------+
17. | @j        | json_unquote(@j) |
18. +-----------+------------------+
19. | [1, 2, 3] | [1, 2, 3]        |
20. +-----------+------------------+
21. 1 row in set (0.00 sec)

23. mysql> SELECT JSON_UNQUOTE('"\\t\\u0032"');
24. +----------------------------+
25. | json_unquote("\\t\\u0032") |
26. +----------------------------+
27. |       2                         |
28. +----------------------------+
29. 1 row in set (0.00 sec)