MySQL常用json函数

最新推荐文章于 2024-06-14 16:48:38 发布
最新推荐文章于 2024-06-14 16:48:38 发布
阅读量156 收藏
点赞数
分类专栏： mysql
原文链接：https://www.cnblogs.com/waterystone/p/5626098.html
版权

作者：水岩

出处：http://www.cnblogs.com/waterystone

原文链接：https://www.cnblogs.com/waterystone/p/5626098.html

1. 概述

MySQL里的json分为json array和json object。 $表示整个json对象，在索引数据时用下标(对于json array，从0开始)或键值(对于json object，含有特殊字符的key要用"括起来，比如$."my name")。

例如：[3, {"a": [5, 6], "b": 10}, [99, 100]]，那么：

$[0]：3

$[1]： {"a": [5, 6], "b": 10}

$[2] ：[99, 100]

$[3] ： NULL

$[1].a：[5, 6]

$[1].a[1]：6

$[1].b：10

$[2][0]：99

 

2. 比较规则

json中的数据可以用 =, <, <=, >, >=, <>, !=, and <=> 进行比较。但json里的数据类型可以是多样的，那么在不同类型之间进行比较时，就有优先级了，高优先级的要大于低优先级的（可以用JSON_TYPE()函数查看类型）。优先级从高到低如下：

1

2

3

4

5

6

7

8

9

10

11

12

BLOB

BIT

OPAQUE

DATETIME

TIME

DATE

BOOLEAN

ARRAY

OBJECT

STRING

INTEGER, DOUBLE

NULL

　　

3. 常用函数

3.1 创建函数

3.1.1 JSON_ARRAY

JSON_ARRAY(val1,val2,val3...)

生成一个包含指定元素的json数组。

1

2

3

4

5

6

mysql> SELECT JSON_ARRAY(1, "abc", NULL, TRUE, CURTIME());

+---------------------------------------------+

| JSON_ARRAY(1, "abc", NULL, TRUE, CURTIME()) |

+---------------------------------------------+

| [1, "abc", null, true, "11:30:24.000000"]   |

+---------------------------------------------+

　　

3.1.2 JSON_OBJECT

JSON_OBJECT(key1,val1,key2,val2...)

生成一个包含指定K-V对的json object。如果有key为NULL或参数个数为奇数，则抛错。

1

2

3

4

5

6

mysql> SELECT JSON_OBJECT('id', 87, 'name', 'carrot');

+-----------------------------------------+

| JSON_OBJECT('id', 87, 'name', 'carrot') |

+-----------------------------------------+

| {"id": 87, "name": "carrot"}            |

+-----------------------------------------+

 

3.1.3 JSON_QUOTE

JSON_QUOTE(json_val)

将json_val用"号括起来。

1

2

3

4

5

6

7

8

9

10

11

12

mysql> SELECT JSON_QUOTE('null'), JSON_QUOTE('"null"');

+--------------------+----------------------+

| JSON_QUOTE('null') | JSON_QUOTE('"null"') |

+--------------------+----------------------+

| "null"             | "\"null\""           |

+--------------------+----------------------+

mysql> SELECT JSON_QUOTE('[1, 2, 3]');

+-------------------------+

| JSON_QUOTE('[1, 2, 3]') |

+-------------------------+

| "[1, 2, 3]"             |

+-------------------------+

　　

3.1.4 CONVERT

CONVERT(json_string,JSON)

1

2

3

4

5

6

mysql> select CONVERT('{"mail": "amy@gmail.com", "name": "Amy"}',JSON);

+----------------------------------------------------------+

| CONVERT('{"mail": "amy@gmail.com", "name": "Amy"}',JSON) |

+----------------------------------------------------------+

| {"mail": "amy@gmail.com", "name": "Amy"}                 |

+----------------------------------------------------------+

　　

3.2 查询函数　　

 3.2.1 JSON_CONTAINS

JSON_CONTAINS(json_doc, val[, path])

查询json文档是否在指定path包含指定的数据，包含则返回1，否则返回0。如果有参数为NULL或path不存在，则返回NULL。

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

mysql> SET @j = '{"a": 1, "b": 2, "c": {"d": 4}}';

mysql> SET @j2 = '1';

mysql> SELECT JSON_CONTAINS(@j, @j2, '$.a');

+-------------------------------+

| JSON_CONTAINS(@j, @j2, '$.a') |

+-------------------------------+

|                             1 |

+-------------------------------+

mysql> SELECT JSON_CONTAINS(@j, @j2, '$.b');

+-------------------------------+

| JSON_CONTAINS(@j, @j2, '$.b') |

+-------------------------------+

|                             0 |

+-------------------------------+

 

mysql> SET @j2 = '{"d": 4}';

mysql> SELECT JSON_CONTAINS(@j, @j2, '$.a');

+-------------------------------+

| JSON_CONTAINS(@j, @j2, '$.a') |

+-------------------------------+

|                             0 |

+-------------------------------+

mysql> SELECT JSON_CONTAINS(@j, @j2, '$.c');

+-------------------------------+

| JSON_CONTAINS(@j, @j2, '$.c') |

+-------------------------------+

|                             1 |

+-------------------------------+

　　

 3.2.2 JSON_CONTAINS_PATH

JSON_CONTAINS_PATH(json_doc, one_or_all, path[, path] ...)

查询是否存在指定路径，存在则返回1，否则返回0。如果有参数为NULL，则返回NULL。

one_or_all只能取值"one"或"all"，one表示只要有一个存在即可；all表示所有的都存在才行。

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

mysql> SET @j = '{"a": 1, "b": 2, "c": {"d": 4}}';

mysql> SELECT JSON_CONTAINS_PATH(@j, 'one', '$.a', '$.e');

+---------------------------------------------+

| JSON_CONTAINS_PATH(@j, 'one', '$.a', '$.e') |

+---------------------------------------------+

|                                           1 |

+---------------------------------------------+

mysql> SELECT JSON_CONTAINS_PATH(@j, 'all', '$.a', '$.e');

+---------------------------------------------+

| JSON_CONTAINS_PATH(@j, 'all', '$.a', '$.e') |

+---------------------------------------------+

|                                           0 |

+---------------------------------------------+

mysql> SELECT JSON_CONTAINS_PATH(@j, 'one', '$.c.d');

+----------------------------------------+

| JSON_CONTAINS_PATH(@j, 'one', '$.c.d') |

+----------------------------------------+

|                                      1 |

+----------------------------------------+

mysql> SELECT JSON_CONTAINS_PATH(@j, 'one', '$.a.d');

+----------------------------------------+

| JSON_CONTAINS_PATH(@j, 'one', '$.a.d') |

+----------------------------------------+

|                                      0 |

+----------------------------------------+

　

3.2.3 JSON_EXTRACT

JSON_EXTRACT(json_doc, path[, path] ...)

从json文档里抽取数据。如果有参数有NULL或path不存在，则返回NULL。如果抽取出多个path，则返回的数据封闭在一个json array里。

 

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

mysql> SELECT JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]');

+--------------------------------------------+

| JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]') |

+--------------------------------------------+

| 20                                         |

+--------------------------------------------+

mysql> SELECT JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]', '$[0]');

+----------------------------------------------------+

| JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]', '$[0]') |

+----------------------------------------------------+

| [20, 10]                                           |

+----------------------------------------------------+

mysql> SELECT JSON_EXTRACT('[10, 20, [30, 40]]', '$[2][*]');

+-----------------------------------------------+

| JSON_EXTRACT('[10, 20, [30, 40]]', '$[2][*]') |

+-----------------------------------------------+

| [30, 40]                                      |

+-----------------------------------------------+

 

在MySQL 5.7.9+里可以用"->"替代。

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

mysql> SELECT c, JSON_EXTRACT(c, "$.id"), g

     > FROM jemp

     > WHERE JSON_EXTRACT(c, "$.id") > 1

     > ORDER BY JSON_EXTRACT(c, "$.name");

+-------------------------------+-----------+------+

| c                             | c->"$.id" | g    |

+-------------------------------+-----------+------+

| {"id": "3", "name": "Barney"} | "3"       |    3 |

| {"id": "4", "name": "Betty"}  | "4"       |    4 |

| {"id": "2", "name": "Wilma"}  | "2"       |    2 |

+-------------------------------+-----------+------+

3 rows in set (0.00 sec)

 

mysql> SELECT c, c->"$.id", g

     > FROM jemp

     > WHERE c->"$.id" > 1

     > ORDER BY c->"$.name";

+-------------------------------+-----------+------+

| c                             | c->"$.id" | g    |

+-------------------------------+-----------+------+

| {"id": "3", "name": "Barney"} | "3"       |    3 |

| {"id": "4", "name": "Betty"}  | "4"       |    4 |

| {"id": "2", "name": "Wilma"}  | "2"       |    2 |

+-------------------------------+-----------+------+

3 rows in set (0.00 sec)

　　

在MySQL 5.7.13+，还可以用"->>"表示去掉抽取结果的"号，下面三种效果是一样的：

  • JSON_UNQUOTE( JSON_EXTRACT(column, path) )
  • JSON_UNQUOTE(column -> path)
  • column->>path

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

mysql> SELECT * FROM jemp WHERE g > 2;

+-------------------------------+------+

| c                             | g    |

+-------------------------------+------+

| {"id": "3", "name": "Barney"} |    3 |

| {"id": "4", "name": "Betty"}  |    4 |

+-------------------------------+------+

2 rows in set (0.01 sec)

 

mysql> SELECT c->'$.name' AS name    

    ->     FROM jemp WHERE g > 2;

+----------+

| name     |

+----------+

| "Barney" |

| "Betty"  |

+----------+

2 rows in set (0.00 sec)

 

mysql> SELECT JSON_UNQUOTE(c->'$.name') AS name

    ->     FROM jemp WHERE g > 2;

+--------+

| name   |

+--------+

| Barney |

| Betty  |

+--------+

2 rows in set (0.00 sec)

 

mysql> SELECT c->>'$.name' AS name

    ->     FROM jemp WHERE g > 2;

+--------+

| name   |

+--------+

| Barney |

| Betty  |

+--------+

2 rows in set (0.00 sec)

　

3.2.4 JSON_KEYS

JSON_KEYS(json_doc[, path])

获取json文档在指定路径下的所有键值，返回一个json array。如果有参数为NULL或path不存在，则返回NULL。

1

2

3

4

5

6

7

8

9

10

11

12

mysql> SELECT JSON_KEYS('{"a": 1, "b": {"c": 30}}');

+---------------------------------------+

| JSON_KEYS('{"a": 1, "b": {"c": 30}}') |

+---------------------------------------+

| ["a", "b"]                            |

+---------------------------------------+

mysql> SELECT JSON_KEYS('{"a": 1, "b": {"c": 30}}', '$.b');

+----------------------------------------------+

| JSON_KEYS('{"a": 1, "b": {"c": 30}}', '$.b') |

+----------------------------------------------+

| ["c"]                                        |

+----------------------------------------------+

　　

3.2.5 JSON_SEARCH

JSON_SEARCH(json_doc, one_or_all, search_str[, escape_char[, path] ...])

查询包含指定字符串的paths，并作为一个json array返回。如果有参数为NUL或path不存在，则返回NULL。

one_or_all："one"表示查询到一个即返回；"all"表示查询所有。

search_str：要查询的字符串。 可以用LIKE里的'%'或‘_’匹配。

path：在指定path下查。

 

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

85

86

87

88

89

90

91

92

93

94

95

96

97

98

99

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

mysql> SET @j = '["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]';

 

mysql> SELECT JSON_SEARCH(@j, 'one', 'abc');

+-------------------------------+

| JSON_SEARCH(@j, 'one', 'abc') |

+-------------------------------+

| "$[0]"                        |

+-------------------------------+

 

mysql> SELECT JSON_SEARCH(@j, 'all', 'abc');

+-------------------------------+

| JSON_SEARCH(@j, 'all', 'abc') |

+-------------------------------+

| ["$[0]", "$[2].x"]            |

+-------------------------------+

 

mysql> SELECT JSON_SEARCH(@j, 'all', 'ghi');

+-------------------------------+

| JSON_SEARCH(@j, 'all', 'ghi') |

+-------------------------------+

| NULL                          |

+-------------------------------+

 

mysql> SELECT JSON_SEARCH(@j, 'all', '10');

+------------------------------+

| JSON_SEARCH(@j, 'all', '10') |

+------------------------------+

| "$[1][0].k"                  |

+------------------------------+

 

mysql> SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$');

+-----------------------------------------+

| JSON_SEARCH(@j, 'all', '10', NULL, '$') |

+-----------------------------------------+

| "$[1][0].k"                             |

+-----------------------------------------+

 

mysql> SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$[*]');

+--------------------------------------------+

| JSON_SEARCH(@j, 'all', '10', NULL, '$[*]') |

+--------------------------------------------+

| "$[1][0].k"                                |

+--------------------------------------------+

 

mysql> SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$**.k');

+---------------------------------------------+

| JSON_SEARCH(@j, 'all', '10', NULL, '$**.k') |

+---------------------------------------------+

| "$[1][0].k"                                 |

+---------------------------------------------+

 

mysql> SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$[*][0].k');

+-------------------------------------------------+

| JSON_SEARCH(@j, 'all', '10', NULL, '$[*][0].k') |

+-------------------------------------------------+

| "$[1][0].k"                                     |

+-------------------------------------------------+

 

mysql> SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$[1]');

+--------------------------------------------+

| JSON_SEARCH(@j, 'all', '10', NULL, '$[1]') |

+--------------------------------------------+

| "$[1][0].k"                                |

+--------------------------------------------+

 

mysql> SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$[1][0]');

+-----------------------------------------------+

| JSON_SEARCH(@j, 'all', '10', NULL, '$[1][0]') |

+-----------------------------------------------+

| "$[1][0].k"                                   |

+-----------------------------------------------+

 

mysql> SELECT JSON_SEARCH(@j, 'all', 'abc', NULL, '$[2]');

+---------------------------------------------+

| JSON_SEARCH(@j, 'all', 'abc', NULL, '$[2]') |

+---------------------------------------------+

| "$[2].x"                                    |

+---------------------------------------------+

 

mysql> SELECT JSON_SEARCH(@j, 'all', '%a%');

+-------------------------------+

| JSON_SEARCH(@j, 'all', '%a%') |

+-------------------------------+

| ["$[0]", "$[2].x"]            |

+-------------------------------+

 

mysql> SELECT JSON_SEARCH(@j, 'all', '%b%');

+-------------------------------+

| JSON_SEARCH(@j, 'all', '%b%') |

+-------------------------------+

| ["$[0]", "$[2].x", "$[3].y"]  |

+-------------------------------+

 

mysql> SELECT JSON_SEARCH(@j, 'all', '%b%', NULL, '$[0]');

+---------------------------------------------+

| JSON_SEARCH(@j, 'all', '%b%', NULL, '$[0]') |

+---------------------------------------------+

| "$[0]"                                      |

+---------------------------------------------+

 

mysql> SELECT JSON_SEARCH(@j, 'all', '%b%', NULL, '$[2]');

+---------------------------------------------+

| JSON_SEARCH(@j, 'all', '%b%', NULL, '$[2]') |

+---------------------------------------------+

| "$[2].x"                                    |

+---------------------------------------------+

 

mysql> SELECT JSON_SEARCH(@j, 'all', '%b%', NULL, '$[1]');

+---------------------------------------------+

| JSON_SEARCH(@j, 'all', '%b%', NULL, '$[1]') |

+---------------------------------------------+

| NULL                                        |

+---------------------------------------------+

 

mysql> SELECT JSON_SEARCH(@j, 'all', '%b%', '', '$[1]');

+-------------------------------------------+

| JSON_SEARCH(@j, 'all', '%b%', '', '$[1]') |

+-------------------------------------------+

| NULL                                      |

+-------------------------------------------+

 

mysql> SELECT JSON_SEARCH(@j, 'all', '%b%', '', '$[3]');

+-------------------------------------------+

| JSON_SEARCH(@j, 'all', '%b%', '', '$[3]') |

+-------------------------------------------+

| "$[3].y"                                  |

+-------------------------------------------+

　　

3.3 修改函数

3.3.1 JSON_APPEND/JSON_ARRAY_APPEND

JSON_ARRAY_APPEND(json_doc, path, val[, path, val] ...)

在指定path的json array尾部追加val。如果指定path是一个json object，则将其封装成一个json array再追加。如果有参数为NULL，则返回NULL。

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

mysql> SET @j = '["a", ["b", "c"], "d"]';

mysql> SELECT JSON_ARRAY_APPEND(@j, '$[1]', 1);

+----------------------------------+

| JSON_ARRAY_APPEND(@j, '$[1]', 1) |

+----------------------------------+

| ["a", ["b", "c", 1], "d"]        |

+----------------------------------+

mysql> SELECT JSON_ARRAY_APPEND(@j, '$[0]', 2);

+----------------------------------+

| JSON_ARRAY_APPEND(@j, '$[0]', 2) |

+----------------------------------+

| [["a", 2], ["b", "c"], "d"]      |

+----------------------------------+

mysql> SELECT JSON_ARRAY_APPEND(@j, '$[1][0]', 3);

+-------------------------------------+

| JSON_ARRAY_APPEND(@j, '$[1][0]', 3) |

+-------------------------------------+

| ["a", [["b", 3], "c"], "d"]         |

+-------------------------------------+

 

mysql> SET @j = '{"a": 1, "b": [2, 3], "c": 4}';

mysql> SELECT JSON_ARRAY_APPEND(@j, '$.b', 'x');

+------------------------------------+

| JSON_ARRAY_APPEND(@j, '$.b', 'x')  |

+------------------------------------+

| {"a": 1, "b": [2, 3, "x"], "c": 4} |

+------------------------------------+

mysql> SELECT JSON_ARRAY_APPEND(@j, '$.c', 'y');

+--------------------------------------+

| JSON_ARRAY_APPEND(@j, '$.c', 'y')    |

+--------------------------------------+

| {"a": 1, "b": [2, 3], "c": [4, "y"]} |

+--------------------------------------+

 

mysql> SET @j = '{"a": 1}';

mysql> SELECT JSON_ARRAY_APPEND(@j, '$', 'z');

+---------------------------------+

| JSON_ARRAY_APPEND(@j, '$', 'z') |

+---------------------------------+

| [{"a": 1}, "z"]                 |

+---------------------------------+

　　

3.3.2 JSON_ARRAY_INSERT

JSON_ARRAY_INSERT(json_doc, path, val[, path, val] ...)

 在path指定的json array元素插入val，原位置及以右的元素顺次右移。如果path指定的数据非json array元素，则略过此val；如果指定的元素下标超过json array的长度，则插入尾部。

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

mysql> SET @j = '["a", {"b": [1, 2]}, [3, 4]]';

mysql> SELECT JSON_ARRAY_INSERT(@j, '$[1]', 'x');

+------------------------------------+

| JSON_ARRAY_INSERT(@j, '$[1]', 'x') |

+------------------------------------+

| ["a", "x", {"b": [1, 2]}, [3, 4]]  |

+------------------------------------+

mysql> SELECT JSON_ARRAY_INSERT(@j, '$[100]', 'x');

+--------------------------------------+

| JSON_ARRAY_INSERT(@j, '$[100]', 'x') |

+--------------------------------------+

| ["a", {"b": [1, 2]}, [3, 4], "x"]    |

+--------------------------------------+

mysql> SELECT JSON_ARRAY_INSERT(@j, '$[1].b[0]', 'x');

+-----------------------------------------+

| JSON_ARRAY_INSERT(@j, '$[1].b[0]', 'x') |

+-----------------------------------------+

| ["a", {"b": ["x", 1, 2]}, [3, 4]]       |

+-----------------------------------------+

mysql> SELECT JSON_ARRAY_INSERT(@j, '$[2][1]', 'y');

+---------------------------------------+

| JSON_ARRAY_INSERT(@j, '$[2][1]', 'y') |

+---------------------------------------+

| ["a", {"b": [1, 2]}, [3, "y", 4]]     |

+---------------------------------------+

mysql> SELECT JSON_ARRAY_INSERT(@j, '$[0]', 'x', '$[2][1]', 'y');

+----------------------------------------------------+

| JSON_ARRAY_INSERT(@j, '$[0]', 'x', '$[2][1]', 'y') |

+----------------------------------------------------+

| ["x", "a", {"b": [1, 2]}, [3, 4]]                  |

+----------------------------------------------------+

　　

3.3.3 JSON_INSERT/JSON_REPLACE/JSON_SET

JSON_INSERT(json_doc, path, val[, path, val] ...)

在指定path下插入数据，如果path已存在，则忽略此val（不存在才插入）。

1

2

3

4

5

6

7

mysql> SET @j = '{ "a": 1, "b": [2, 3]}';

mysql> SELECT JSON_INSERT(@j, '$.a', 10, '$.c', '[true, false]');

+----------------------------------------------------+

| JSON_INSERT(@j, '$.a', 10, '$.c', '[true, false]') |

+----------------------------------------------------+

| {"a": 1, "b": [2, 3], "c": "[true, false]"}        |

+----------------------------------------------------+

 

JSON_REPLACE(json_doc, path, val[, path, val] ...)

替换指定路径的数据，如果某个路径不存在则略过（存在才替换）。如果有参数为NULL，则返回NULL。

1

2

3

4

5

6

7

mysql> SET @j = '{ "a": 1, "b": [2, 3]}';

mysql> SELECT JSON_REPLACE(@j, '$.a', 10, '$.c', '[true, false]');

+-----------------------------------------------------+

| JSON_REPLACE(@j, '$.a', 10, '$.c', '[true, false]') |

+-----------------------------------------------------+

| {"a": 10, "b": [2, 3]}                              |

+-----------------------------------------------------+

 

 

JSON_SET(json_doc, path, val[, path, val] ...)

设置指定路径的数据（不管是否存在）。如果有参数为NULL，则返回NULL。

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

mysql> SET @j = '{ "a": 1, "b": [2, 3]}';

mysql> SELECT JSON_SET(@j, '$.a', 10, '$.c', '[true, false]');

+-------------------------------------------------+

| JSON_SET(@j, '$.a', 10, '$.c', '[true, false]') |

+-------------------------------------------------+

| {"a": 10, "b": [2, 3], "c": "[true, false]"}    |

+-------------------------------------------------+

mysql> SELECT JSON_INSERT(@j, '$.a', 10, '$.c', '[true, false]');

+----------------------------------------------------+

| JSON_INSERT(@j, '$.a', 10, '$.c', '[true, false]') |

+----------------------------------------------------+

| {"a": 1, "b": [2, 3], "c": "[true, false]"}        |

+----------------------------------------------------+

mysql> SELECT JSON_REPLACE(@j, '$.a', 10, '$.c', '[true, false]');

+-----------------------------------------------------+

| JSON_REPLACE(@j, '$.a', 10, '$.c', '[true, false]') |

+-----------------------------------------------------+

| {"a": 10, "b": [2, 3]}                              |

+-----------------------------------------------------+

　　

3.3.4 JSON_MERGE

JSON_MERGE(json_doc, json_doc[, json_doc] ...)

merge多个json文档。规则如下：

  • 如果都是json array，则结果自动merge为一个json array；
  • 如果都是json object，则结果自动merge为一个json object；
  • 如果有多种类型，则将非json array的元素封装成json array再按照规则一进行mege。

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

mysql> SELECT JSON_MERGE('[1, 2]', '[true, false]');

+---------------------------------------+

| JSON_MERGE('[1, 2]', '[true, false]') |

+---------------------------------------+

| [1, 2, true, false]                   |

+---------------------------------------+

mysql> SELECT JSON_MERGE('{"name": "x"}', '{"id": 47}');

+-------------------------------------------+

| JSON_MERGE('{"name": "x"}', '{"id": 47}') |

+-------------------------------------------+

| {"id": 47, "name": "x"}                   |

+-------------------------------------------+

mysql> SELECT JSON_MERGE('1', 'true');

+-------------------------+

| JSON_MERGE('1', 'true') |

+-------------------------+

| [1, true]               |

+-------------------------+

mysql> SELECT JSON_MERGE('[1, 2]', '{"id": 47}');

+------------------------------------+

| JSON_MERGE('[1, 2]', '{"id": 47}') |

+------------------------------------+

| [1, 2, {"id": 47}]                 |

+------------------------------------+

　　

3.3.5 JSON_REMOVE

JSON_REMOVE(json_doc, path[, path] ...)
移除指定路径的数据，如果某个路径不存在则略过此路径。如果有参数为NULL，则返回NULL。

1

2

3

4

5

6

7

mysql> SET @j = '["a", ["b", "c"], "d"]';

mysql> SELECT JSON_REMOVE(@j, '$[1]');

+-------------------------+

| JSON_REMOVE(@j, '$[1]') |

+-------------------------+

| ["a", "d"]              |

+-------------------------+

　　

3.3.6 JSON_UNQUOTE

JSON_UNQUOTE(val)

去掉val的引号。如果val为NULL，则返回NULL。

1

2

3

4

5

6

7

8

9

10

11

12

13

14

mysql> SET @j = '"abc"';

mysql> SELECT @j, JSON_UNQUOTE(@j);

+-------+------------------+

| @j    | JSON_UNQUOTE(@j) |

+-------+------------------+

| "abc" | abc              |

+-------+------------------+

mysql> SET @j = '[1, 2, 3]';

mysql> SELECT @j, JSON_UNQUOTE(@j);

+-----------+------------------+

| @j        | JSON_UNQUOTE(@j) |

+-----------+------------------+

| [1, 2, 3] | [1, 2, 3]        |

+-----------+------------------+

 

3.4 JSON特性查询

3.4.1 JSON_DEEPTH

 JSON_DEPTH(json_doc)

获取json文档的深度。如果参数为NULL，则返回NULL。

空的json array、json object或标量的深度为1。

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

mysql> SELECT JSON_DEPTH('{}'), JSON_DEPTH('[]'), JSON_DEPTH('true');

+------------------+------------------+--------------------+

| JSON_DEPTH('{}') | JSON_DEPTH('[]') | JSON_DEPTH('true') |

+------------------+------------------+--------------------+

|                1 |                1 |                  1 |

+------------------+------------------+--------------------+

mysql> SELECT JSON_DEPTH('[10, 20]'), JSON_DEPTH('[[], {}]');

+------------------------+------------------------+

| JSON_DEPTH('[10, 20]') | JSON_DEPTH('[[], {}]') |

+------------------------+------------------------+

|                      2 |                      2 |

+------------------------+------------------------+

mysql> SELECT JSON_DEPTH('[10, {"a": 20}]');

+-------------------------------+

| JSON_DEPTH('[10, {"a": 20}]') |

+-------------------------------+

|                             3 |

+-------------------------------+

 

3.4.2 JSON_LENGTH

JSON_LENGTH(json_doc[, path])

获取指定路径下的长度。如果参数为NULL，则返回NULL。　

长度的计算规则：

  • 标量的长度为1；
  • json array的长度为元素的个数；
  • json object的长度为key的个数。

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

mysql> SELECT JSON_LENGTH('[1, 2, {"a": 3}]');

+---------------------------------+

| JSON_LENGTH('[1, 2, {"a": 3}]') |

+---------------------------------+

|                               3 |

+---------------------------------+

mysql> SELECT JSON_LENGTH('{"a": 1, "b": {"c": 30}}');

+-----------------------------------------+

| JSON_LENGTH('{"a": 1, "b": {"c": 30}}') |

+-----------------------------------------+

|                                       2 |

+-----------------------------------------+

mysql> SELECT JSON_LENGTH('{"a": 1, "b": {"c": 30}}', '$.b');

+------------------------------------------------+

| JSON_LENGTH('{"a": 1, "b": {"c": 30}}', '$.b') |

+------------------------------------------------+

|                                              1 |

+------------------------------------------------+

　　

3.4.3 JSON_TYPE

JSON_TYPE(json_val)

获取json文档的具体类型。如果参数为NULL，则返回NULL。

 

3.4.4 JSON_VALID

JSON_VALID(val)

判断val是否为有效的json格式，是为1，不是为0。如果参数为NUL，则返回NULL。

1

2

3

4

5

6

7

8

9

10

11

12

mysql> SELECT JSON_VALID('{"a": 1}');

+------------------------+

| JSON_VALID('{"a": 1}') |

+------------------------+

|                      1 |

+------------------------+

mysql> SELECT JSON_VALID('hello'), JSON_VALID('"hello"');

+---------------------+-----------------------+

| JSON_VALID('hello') | JSON_VALID('"hello"') |

+---------------------+-----------------------+

|                   0 |                     1 |

+---------------------+-----------------------+

天马行空波
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
MySQL数据库基础：JSON函数各类操作一文详解
Huangjiazhen711的博客
11-04 5999
很多日常业务场景都会用到json文件作为数据存储起来，而mysql5.7以上就提供了存储json的支撑。往常存储json一般都保留在pg库或者是hive库里面，现在mysql有了支持的话基本业务都可以用mysql来实现。现在mysql8.x版本对json字符出处理已经做的非常完善了。现在就让我们来详细了解一下关于json数据数据类型mysql都有哪些函数能够对其进行操作。
MYSQL JSON 函数
程小明的博客
07-01 8427
MYSQL JSON函数 json API
Mysql当中Json相关的函数详解
怪咖@的博客
04-30 1万+
从5.7开始，MySQL开始支持json类型，用于存储JSON数据。提供数据类型的同时也提供了很多关于json的函数供我们使用，本篇文章基本上都来源于官网;
MySQL 之 JSON 支持（三）—— JSON 函数
最新发布
wzy0623的专栏
06-14 937
JSON 函数官方文档翻译
Mysql Json 数据类型&函数
王文萱的博客
06-28 7661
Mysql Json 数据类型&函数
MySQL用于操作JSON的一些函数
weixin_44797884的博客
08-18 217
MySQL用于操作JSON的一些函数
Mysql Json 检索函数 原代码
07-31
自宝义函数json_searchvalue，从给定的JSON值中，根据给定的Key，检索出需要Value,
Mysql 查询JSON结果的相关函数汇总
12-14
JSON 格式字段是 Mysql 5.7 新加的属性，不够它本质上以字符串性质保存在库中的，刚接触时我只了解 $.xx 查询字段的方法，因为大部分时间，有这个就够了，其他交给程序就行了，但是最近一些操作需要更复杂的查询操作...
Mysql5.7中JSON操作函数使用说明
09-09
以下是一些关于 MySQL 5.7 中 JSON 操作函数的详细说明： 1. **JSON 数据类型** JSON 数据类型允许你在表中存储 JSON 文档。例如，在创建 `article` 表时，`tags` 字段被定义为 JSON 类型，可以存储如 `["Mysql", ...
MySQL数据生成JSON数据
08-03
3. MySQL中的JSON函数，如`JSON_ARRAY()`、`JSON_OBJECT()`和`JSON_ARRAYAGG()`，用于创建和操作JSON数据。 4. 将查询结果转换为JSON格式，并可能涉及将数据输出到文件或屏幕。 在实际应用中，这个过程可能需要结合...
MySQL——JSON类型
吴声子夜歌的博客
04-08 2376
如果json_doc参数不是一个有效的JSON文档，任何路径参数不是一个有效的路径表达式，one_or_all不是’one’或’all’，或者escape_char不是一个常量表达式，就会发生错误。如果插入的值中存在重复 KEY，在 MySQL 8.0.3 之前，遵循 first duplicate key wins 原则，会保留第一个 KEY，后面的将被丢弃掉。之前，这类数据不是单独的数据类型，会被存储为字符串。从指定文档中给定的路径处的JSON文档中提取值，并返回提取的值，可以将其转换为所需的类型。
mysql：列类型之json
火焰
02-22 4297
环境： window10 vs2022 .net 6 mysql 8.0.25 DBeaver 1. 认识json格式 json全称是: JavaScript Object Notation。它的格式有以下几点： 两个对象类型（[] or {}）； 四种标量 （string、number、true/false、null）； 根节点是对象或者是数组； 标量：true/false和null均全为小写形式； 标量：string可以有转移字符（"、\、/、\b、\f、\n、\r、\t、\u0000）； js
MySQL JSON_VALID() 函数
LiZhen314的博客
02-18 1072
MySQL函数返回0 和 1 来指示给定的参数是否是一个有效的 JSON文档。
MySQL-常用JSON操作函数
cyy_j的博客
03-18 466
JSON_SET()、JSON_REPLACE()、JSON_REMOVE()、JSON_EXTRACT()
mysql json格式数据操作
qq_41482600的博客
05-05 2785
mysql5.7以上提供了一种新的字段格式-json，大概是mysql想把非关系型和关系型数据库一口通吃，所以推出了这种非常好用的格式，这样，我们的很多基于mongoDb或者clickHouse的业务都可以用mysql去实现了。5.7的版本只是最基础的版本，对于海量数据的效率是远远不够的，不过这些都在mysql8.0解决了。 1、创建json格式字段 CREATE TABLE `dept` ( `id` int(11) NOT NULL, `dept` varchar(255) DEFAULT .
mysql中添加字段
热门推荐
m0_54850467的博客
09-11 4万+
深知大多数初中级Java工程师，想要提升技能，往往是自己摸索成长或者是报班学习，但对于培训机构动则近万的学费，着实压力不小。自己不成体系的自学效果低效又漫长，而且极易碰到天花板技术停滞不前！因此收集整理了一份《Java开发全套学习资料》送给大家，初衷也很简单，就是希望能够帮助到想自学提升又不知道该从何学起的朋友，同时减轻大家的负担。
Mysql中的JSON系列操作函数
liunx___的博客
12-07 3630
前言 JSON是一种轻量级的数据交换格式，采用了独立于语言的文本格式，类似XML，但是比XML简单，易读并且易编写。对机器来说易于解析和生成，并且会减少网络带宽的传输。 JSON的格式非常简单：名称/键值。之前MySQL版本里面要实现这样的存储，要么用VARCHAR要么用TEXT大文本。 MySQL5.7发布后，专门设计了JSON数据类型以及关于这种类型的检索以及其他函数解析。 下面一起来实...
MySQL 的各个json 函数说明
愿我如星君如月 ... 夜夜流光相皎洁 ...
12-27 9307
见官网：https://dev.mysql.com/doc/refman/5.7/en/json.html 只是为了方便 自己用的时候找： 前言： 个人windows下用 5.7.20试过，部分函数能运行，部分不能，不知道是不是版本不到 5.7.8 的原因，如果你知道是为什么请一定告诉我！ 如果你和我同版本，运行函数都OK也请告诉我...
MYSQL JSON的操作函数 -- JSON_SET (), JSON_INSERT(), and JSON_REPLACE()
Mr.hu
07-02 2万+
InMySQL, 有一些函数可以帮我们处理JSON类型包括：JSON_SET(),JSON_INSERT(), andJSON_REPLACE()函数. 语法 三种函数的语法： JSON_SET(json_doc, path, val[, path, val] ...) JSON_INSERT(json_doc, path, val[, path, val] ...) JSO...
mysql5.7 json函数
09-09
MySQL 5.7引入了一系列的JSON函数，用于处理和操作JSON格式的数据。这些函数可以分为几个不同的分类，包括函数创建JSON值、查询JSON值和修改JSON值。 在函数创建JSON值的分类中，有一些常用的函数可以用来创建JSON...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值