mysql json介绍

最新推荐文章于 2024-06-07 07:18:07 发布
最新推荐文章于 2024-06-07 07:18:07 发布
阅读量1w 收藏
点赞数
分类专栏： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/lanwangxia/article/details/115319927
版权

一、JSON的路径表达式
MySQL用路径表达式对JSON格式的数据进行查询。

路径表达式中用$代表JSON值。
用key值代表该key对应的元素。
用[N]代表JSON数组中的第N个元素。序号从0开始。
用[M to N]代表JSON数组中第M个至第N个元素。序号从0开始。
用.[*]通配符代表JSON对象的所有子元素。
用[*]通配符代表JSON数组的所有元素。
用**通配符代表用某字符开头或结尾的元素。

*通配符的查询：

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

**通配符的查询：

mysql> SELECT JSON_EXTRACT('{"a": {"b": 1}, "c": {"b": 2}}', '$**.b');
+---------------------------------------------------------+
| JSON_EXTRACT('{"a": {"b": 1}, "c": {"b": 2}}', '$**.b') |
+---------------------------------------------------------+
| [1, 2]                                                  |
+---------------------------------------------------------+

正序范围查询

范围查询使用[M to N]这样的表达式，注意序号是从0开始的，比如：

mysql> SELECT JSON_EXTRACT('[1, 2, 3, 4, 5]', '$[1 to 3]');
+----------------------------------------------+
| JSON_EXTRACT('[1, 2, 3, 4, 5]', '$[1 to 3]') |
+----------------------------------------------+
| [2, 3, 4]                                    |
+----------------------------------------------+
1 row in set (0.00 sec)

倒序范围查询

倒序查询使用[last-M to last-N]这样的表达式，注意逆向的序号也是从0开始的，比如：

mysql> SELECT JSON_EXTRACT('[1, 2, 3, 4, 5]', '$[last-3 to last-1]');
+--------------------------------------------------------+
| JSON_EXTRACT('[1, 2, 3, 4, 5]', '$[last-3 to last-1]') |
+--------------------------------------------------------+
| [2, 3, 4]                                              |
+--------------------------------------------------------+
1 row in set (0.01 sec)

 

二、json函数

1、创建JSON格式数据

1.1、JSON_ARRAY([val[, val] ...])

根据参数值创建JSON数组，每个参数都是数组中一个元素。

1.2、JSON_OBJECT([key, val[, key, val] ...])

根据参数列表创建JSON对象。

按照参数的排列，第一个是key，第二个是value，第三个是key，以此类推。参数个数必须是双数。

参数列表可以为空，得到空JSON：{}。

key不能是null。否则报错。value可以是null。

数据类型错误时会报错。

1.3、JSON_QUOTE(string)

用双引号把字符串括起来，把结果转为utf8mb4并返回。其中会经过JSON的转义。目的是得到JSON的字符串形式。

2、json查询

2.1、JSON_CONTAINS(target, candidate[, path])
检查候选JSON是否包含在目标JSON中，1表示包含，0表示不包含。

target是目标元素，candidate是候选元素，path是路径表达式，如果path有值，则目标元素需要先经过路径表达式的处理再参与判断。

此函数可以使用多值索引。

另有如下规则：

1）参数可比时才能使用此函数。json_type()相同时才能使用此函数，另外integer和decimal类型可以直接比。

2）数组包含在数组中。只有目标数组的每个元素都包含在候选数组中，才算包含。

3）非数组包含在数组中。只有候选JSON对象包含在目标数组的某个元素中，才算包含。

4）非数组包含在非数组中。当且仅当候选JSON对象的每个key，在目标JSON对象中都有同名的key和同值的value，才算包含。

SELECT JSON_CONTAINS('{"a": 1, "b": 2, "c": {"d": 4}}', '{"d":4}', '$.c');

2.2、JSON_CONTAINS_PATH(json_doc, one_or_all, path[, path] ...)
判断目标JSON中是否存在指定的路径或路径列表。

第一个参数是目标JSON。

第二个参数可以选择one或all。如果选择one，那么只要其中一条路径是存在的就返回1，否则返回0。如果选择all，那么必须所有路径都存在才返回1，否则返回0。

SELECT JSON_CONTAINS_PATH('{"a": 1, "b": 2, "c": {"d": 4}}', 'one', '$.a', '$.e');

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

从目标JSON中返回对应路径下的元素。如果匹配到多个元素则封装成数组。

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

2.4、->运算符

此运算符是JSON_EXTRACT()函数的简写，单个path的场景。

这个运算符几乎可以出现在sql的所有位置，而且在select，update等语句中都能用，比如：

mysql> SELECT c, JSON_EXTRACT(c, "$.id"), g
     > FROM jemp
     > WHERE JSON_EXTRACT(c, "$.id") > 1
     > ORDER BY JSON_EXTRACT(c, "$.name");

这个语句可以替换为

mysql> SELECT c, c->"$.id", g
     > FROM jemp
     > WHERE c->"$.id" > 1
     > ORDER BY c->"$.name";

2.5、->>运算符

->>运算符是加强版的->运算符，他把->运算符得到的结果去掉了引号，就像JSON_UNQUOTE ()函数一样，所以，以下三个表达式所代表的含义是一样的：

JSON_UNQUOTE( JSON_EXTRACT(column, path) )
JSON_UNQUOTE(column -> path)
column->>path
和->>运算符一样，->>运算符可以被用到sql中的很多位置。

2.6、JSON_KEYS(json_doc[, path])

返回JSON中一级键值对中key的列表。如果写了path字段，则先进行路径表达式计算，然后的返回第一级键值对中key的列表。

也就是说，不会返回子元素的key。

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

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

2.7、JSON_OVERLAPS(json_doc1, json_doc2)
比较两个JSON是否有相同元素。也就是是否有交集。有则返回1，没有返回0。

部分匹配的情况不能算有相同元素。

两个JSON对象比较时，两者至少有一个相同name的key和相同对应value，则返回1。

两个标量比较时，则比较值是否相等。

标量和数组比较时，则判断标量是否和数组某元素相等。数据类型不同时不算相等。

SELECT JSON_OVERLAPS('[[1,2],[3,4],5]', '[1,[2,3],[4,5]]');

SELECT JSON_OVERLAPS('[4,5,"6",7]', '6');

2.8、JSON_SEARCH(json_doc, one_or_all, search_str[, escape_char[, path] ...])
根据某字符串，返回在目标JSON中匹配的value的路径表达式，search_str和JSON中的value相等时算匹配。

此函数能查询子元素信息。

第一个参数json_doc是目标JSON。

第二个参数one_or_all可以选择one或者all。one表示返回一个匹配的值的路径。all表示返回所有路径。

参数search_str是要搜索的字符串。其中可以用%(百分号)代表任意多个字符，_(下划线)代表任意一个字符。

参数escape_char是转义字符。默认是\。写成空字符串或NULL时，也默认为\。

参数path是路径表达式，如果写了path，匹配结果需在路径表达式下进行。

SELECT JSON_SEARCH('["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]', 'all', '%b%', NULL, '$[3]');

2.9、value MEMBER OF(json_array)
判断value是否被包含在某JSON数组中。包含则返回1，否则返回0。

数据格式不同时不算包含。

value可以用其他表达式替代。

JSON格式的字符串不能直接和数组中的JSON对象比较，会返回0，此时需要把value转成JSON类型才能返回1。

SELECT '{"a":1}' MEMBER OF(JSON_ARRAY(17, CAST('{"a":1}' AS JSON), "abc", 23));

SELECT CAST('{"a":1}' AS JSON) MEMBER OF(JSON_ARRAY(17, CAST('{"a":1}' AS JSON), "abc", 23));

 

三、json值的修改

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

向JSON数组中追加元素，如果对应位置是单个元素，则和新元素一起封装成数组。

另外，在MySQL8.0中，原来的JSON_APPEND()函数不再使用。

SELECT JSON_ARRAY_APPEND('["a", ["b", "c"], "d"]', '$[1]', 1);

SELECT JSON_ARRAY_APPEND('["a", ["b", "c"], "d"]', '$[1][0]', 3);

SELECT JSON_ARRAY_APPEND('{"a": 1}', '$', 'z');

3.2、JSON_ARRAY_INSERT(json_doc, path, val[, path, val] ...)
在数组的指定位置追加元素，原位置的元素和后面的元素依次向后移一位。

指定位置超过数组上限，则添加在数组最后位置。

注意：在多个位置添加多个元素时，添加是有顺序的，后面添加的元素需要等前面的元素添加成功后重新确认具体位置。
 
mysql> SELECT JSON_ARRAY_INSERT('["a", {"b": [1, 2]}, [3, 4]]', '$[0]', 'x', '$[2][1]', 'y');  
+--------------------------------------------------------------------------------+
| JSON_ARRAY_INSERT('["a", {"b": [1, 2]}, [3, 4]]', '$[0]', 'x', '$[2][1]', 'y') |
+--------------------------------------------------------------------------------+
| ["x", "a", {"b": [1, 2]}, [3, 4]]                                              |
+--------------------------------------------------------------------------------+
1 row in set (0.00 sec)

注意这个语句，在$[0]处添加x，在$[2][1]处添加y，但是最后y没有添加成功，本来$[2]代表的应该是[3, 4]，但是前面添加了x后，JSON变成了：

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

 

于是此时的$[2]变成了{"b": [1, 2]}，是个JSON对象，不是数组，所以无法使用此函数。

以下sql可以验证这个场景：

mysql> SELECT JSON_ARRAY_INSERT('["a", {"b": [1, 2]}, [3, 4]]', '$[0]', 'x', '$[2].b[1]', 'y');
+----------------------------------------------------------------------------------+
| JSON_ARRAY_INSERT('["a", {"b": [1, 2]}, [3, 4]]', '$[0]', 'x', '$[2].b[1]', 'y') |
+----------------------------------------------------------------------------------+
| ["x", "a", {"b": [1, "y", 2]}, [3, 4]]                                           |
+----------------------------------------------------------------------------------+
1 row in set (0.00 sec)
可见添加y的时候$[2]已经是{"b": [1, 2]}了。
 

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

向JSON添加键值对。

如果添加的key已经存在，则忽略此键值对，不再添加。

mysql> SELECT JSON_INSERT('{ "a": 1, "b": [2, 3]}', '$.a', 10, '$.c', '[true, false]');      
+--------------------------------------------------------------------------+
| JSON_INSERT('{ "a": 1, "b": [2, 3]}', '$.a', 10, '$.c', '[true, false]') |
+--------------------------------------------------------------------------+
| {"a": 1, "b": [2, 3], "c": "[true, false]"}                              |
+--------------------------------------------------------------------------+
1 row in set (0.00 sec)

从此结果可以看到，此函数不支持数据类型转换，'[true, false]'参数被当做字符串添加了，如果需要作为JSON数组添加，需要手动转换：

mysql> SELECT JSON_INSERT('{ "a": 1, "b": [2, 3]}', '$.a', 10, '$.c', CAST('[true, false]' AS JSON));
+----------------------------------------------------------------------------------------+
| JSON_INSERT('{ "a": 1, "b": [2, 3]}', '$.a', 10, '$.c', CAST('[true, false]' AS JSON)) |
+----------------------------------------------------------------------------------------+
| {"a": 1, "b": [2, 3], "c": [true, false]}                                              |
+----------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

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

合并多个JSON，MySQL8.0.3版本已废弃，建议使用JSON_MERGE_PRESERVE()函数。

3.5、JSON_MERGE_PATCH(json_doc, json_doc[, json_doc] ...)

合并多个JSON对象。会去重的合并。

合并规则：

1，如果两JSON合并，第一个JSON不是对象（比如标量或数组），则合并的结果是第二个JSON，即使第二个JSON也不是对象。

2，如果两JSON合并，第二个JSON不是对象（比如标量或数组），则合并的结果还是第二个JSON。

3，如果两个JSON中有相同的key，且value中有非对象，则value保留后面JSON的value。

4，如果两个JSON中有相同的key，且对应的value中都是JSON对象，则两个value递归合并。

5，合并完成后，value是null的键值对会被删掉。可以用这个特性来删除空值键值对，比如用一个标量和想删null值的JSON合并，记得标量放第一位，JSON放第二位。

mysql> SELECT JSON_MERGE_PATCH('[1, 2]', '[true, false]');
+---------------------------------------------+
| JSON_MERGE_PATCH('[1, 2]', '[true, false]') |
+---------------------------------------------+
| [true, false]                               |
+---------------------------------------------+
1 row in set (0.00 sec)
 
mysql> SELECT JSON_MERGE_PATCH('{"a":1, "b":2, "c":null}', '[]');
+----------------------------------------------------+
| JSON_MERGE_PATCH('{"a":1, "b":2, "c":null}', '[]') |
+----------------------------------------------------+
| []                                                 |
+----------------------------------------------------+
1 row in set (0.00 sec)
 
mysql> SELECT JSON_MERGE_PATCH('{ "a": 1, "b":2 }','{ "a": 3, "c":4 }','{ "a": 5, "d":6 }');
+-------------------------------------------------------------------------------+
| JSON_MERGE_PATCH('{ "a": 1, "b":2 }','{ "a": 3, "c":4 }','{ "a": 5, "d":6 }') |
+-------------------------------------------------------------------------------+
| {"a": 5, "b": 2, "c": 4, "d": 6}                                              |
+-------------------------------------------------------------------------------+
1 row in set (0.00 sec)
 
mysql> SELECT JSON_MERGE_PATCH('{"a":{"x":1}}', '{"a":{"y":2}}');
+----------------------------------------------------+
| JSON_MERGE_PATCH('{"a":{"x":1}}', '{"a":{"y":2}}') |
+----------------------------------------------------+
| {"a": {"x": 1, "y": 2}}                            |
+----------------------------------------------------+
1 row in set (0.00 sec)
 
mysql> SELECT JSON_MERGE_PATCH('1','{"a":1, "b":2, "c":null}');
+--------------------------------------------------+
| JSON_MERGE_PATCH('1','{"a":1, "b":2, "c":null}') |
+--------------------------------------------------+
| {"a": 1, "b": 2}                                 |
+--------------------------------------------------+
1 row in set (0.00 sec)

3.6、JSON_MERGE_PRESERVE(json_doc, json_doc[, json_doc] ...)

合并多个JSON。不去重，保留所有值的合并。

合并规则：

1，两数组或两标量合并，合成一个新数组。

2，两对象合并，递归合并成一个新对象。如有同key键值对，则几个value合并成最终的value。

3，对象和数组合并，对象转成单个元素的数组然后合并成一个新数组。

3.7、JSON_REMOVE(json_doc, path[, path] ...)

从JSON中删除对应路径下的元素。

指定路径不存在也不会报错。

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

替换JSON中的值。

如果路径不存在，则忽略。

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

替换JSON中的值。

如果路径不存在，则添加该值。

如果修改的是数组，路径超过了数组上限，则把元素添加到数组末尾。

所以，JSON_SET()，JSON_INSERT()，和JSON_REPLACE()三者的区别在于：

  • JSON_SET()：路径存在则替换值，路径不存在则新增。
  • JSON_INSERT()：只负责新增。
  • JSON_REPLACE()：只负责替换已存在的值。

3.10、JSON_UNQUOTE(json_val)

去掉JSON值的双引号，并返回utf8mb4格式的字符串。

此函数可以识别转义字符。

 

四、json值的属性

4.1、JSON_DEPTH(json_doc)

返回JSON值的最大深度。

空数组，空对象，标量的深度为1。

仅包含深度为1的元素的数组或对象的深度为2。

其他情况以此类推。

4.2、JSON_LENGTH(json_doc[, path])

返回JSON值的长度。如果写了path，返回对应路径下的JSON长度。

标量的长度是1。

数组的长度是元素的数量。

对象的长度是成员数量。

嵌套的数组不会被计算长度。

4.3、JSON_TYPE(json_val)

返回JSON类型。返回值是一个utf8mb4字符串。

可返回的JSON类型有：

OBJECT。JSON对象。
ARRAY。JSON数组。
BOOLEAN。JSON的true或false。
NULL。JSON的null值。这个返回值是大写的。
INTEGER。TINYINT，SMALLINT，MEDIUMINT，INT，BIGINT类型标量。
DOUBLE。DOUBLE和FLOAT类型标量。
DECIMAL。DECIMAL和NUMERIC类型标量。
DATETIME。DATETIME和TIMESTAMP类型标量。
DATE。DATE类型标量。
TIME。TIME类型标量。
STRING。MySQL中utf8格式标量，比如：CHAR，VARCHAR，TEXT，ENUM，SET。
BLOB。MySQL中二进制格式标量，比如：BINARY，VARBINARY，BLOB，BIT。
OPAQUE。raw bits格式。

4.4、JSON_VALID(val)

判断JSON值是否符合JSON规范。符合返回1，不符合返回0。

4.5、JSON临时表函数

JSON临时表函数可以把一个JSON值变成一个临时表，并返回表格的一些信息。

建立临时表的函数如下：

JSON_TABLE(

    expr,

    path COLUMNS (column_list)

)   [AS] alias

解析一下其中的各个表达式：

1，expr是JSON值。可以是个常量，比如'{"a":1}'。可以是一列值，比如t1.json_data，t1是之前定义的JSON临时表。可以是返回JSON值的函数，比如：JSON_EXTRACT(t1,jsn_data,'$.post.comments')。

2，path是路径表达式，JSON值要先经过路径表达式的筛选。

3，alias是临时表的表名。AS可以不写。

4，COLUMNS是临时表的列。当列中用到path路径时，$从父级路径表达式继承，属于相对路径。

 

COLUMNS的单个元素都可以有以下选择：

1），name FOR ORDINALITY

此列代表行号。name是自定义列名。此列内容是一个自增的计数器，类型是unsigned int，初始为1。类似MySQL的自增id。如果列中包含NESTED PATH语句，会把一行拆成多行，此时这些行的行号都是一样的，也就是第一级行的行号。

2），name type PATH string_path [on_empty] [on_error]

此列表示按照路径表达式查询JSON中的值。name是列名。type是要求返回的数据格式。string_path是路径表达式。

[on_empty]用于路径表达式值不存在的时候的默认值。

[on_empty]可选的写法有：

NULL ON EMPTY。显示为NULL，此为默认方式。

ERROR ON EMPTY。显示为ERROR。

DEFAULT json_string ON EMPTY。显示一个默认的JSON值。

 

[on_error]用于路径表达式下报错的时候的默认值。报错的场景比如保存时的精度错误，格式错误等。

[on_error]可选的写法有：

NULL ON ERROR。显示为NULL，此为默认方式。

ERROR ON ERROR。显示为ERROR。

DEFAULT json_string ON ERROR。显示一个默认的JSON值。

mysql>  SELECT *
    ->  FROM
    ->    JSON_TABLE(
    ->      '[{"a":"3"},{"a":2},{"b":1},{"a":0},{"a":[1,2]}]',
    ->      "$[*]"
    ->      COLUMNS(
    ->        rowid FOR ORDINALITY,
    ->        ac VARCHAR(100) PATH "$.a" DEFAULT '111' ON EMPTY DEFAULT '999' ON ERROR
    ->      )
    ->    ) AS tt;
+-------+------+
| rowid | ac   |
+-------+------+
|     1 | 3    |
|     2 | 2    |
|     3 | 111  |
|     4 | 0    |
|     5 | 999  |
+-------+------+
5 rows in set (0.01 sec)

第5行从{"a":[1,2]}获取"$.a"时得到了数组，而不是列中定义的VARCHAR(100)，类型错误，显示的是ON ERROR默认的999。

3），name type EXISTS PATH path

指定路径下的值是否存在。存在返回1，不存在返回0。

4），NESTED [PATH] path COLUMNS (column_list)

将JSON中嵌套结构的JSON对象或数组元素拆成单独的行。

[PATH]关键字可以不写。

column_list可以写多个，可写的内容和临时表函数可用的column_list一样，name FOR ORDINALITY之类的都可以写。

对于同一元素中的PATH和NESTED PATH，得到的结果类似一个内连接。

如果存在多个NESTED PATH，则会对每个NESTED PATH单独生成一组记录，不会用连接的形式展示。
mysql> SELECT *
    -> FROM
    ->   JSON_TABLE(
    ->     '[ {"a": 1, "b": [11,111]}, {"a": 2, "b": [22,222]}, {"a":3}]',
    ->     '$[*]' COLUMNS(
    ->             id FOR ORDINALITY,
    ->             a INT PATH '$.a',
    ->             NESTED PATH '$.b[*]' COLUMNS (b INT PATH '$')
    ->            )
    ->    ) AS jt
    -> WHERE b IS NOT NULL;
+------+------+------+
| id   | a    | b    |
+------+------+------+
|    1 |    1 |   11 |
|    1 |    1 |  111 |
|    2 |    2 |   22 |
|    2 |    2 |  222 |
+------+------+------+
4 rows in set (0.00 sec)

可以看到，前两行id都是1，他们都是从数组的第一个元素{"a": 1, "b": [11,111]}得来的，这两行的a列和b列就类似'$.a'和'$.b[*]'的一个内连接。

再比如：

mysql> SELECT *
    -> FROM
    ->   JSON_TABLE(
    ->     '[{"a": 1, "b": [11,111]}, {"a": 2, "b": [22,222,2222]}]',
    ->     '$[*]' COLUMNS(
    ->         a INT PATH '$.a',
    ->         NESTED PATH '$.b[*]' COLUMNS (b1 INT PATH '$'),
    ->         NESTED PATH '$.b[*]' COLUMNS (b2 INT PATH '$')
    ->     )
    -> ) AS jt;
+------+------+------+
| a    | b1   | b2   |
+------+------+------+
|    1 |   11 | NULL |
|    1 |  111 | NULL |
|    1 | NULL |   11 |
|    1 | NULL |  111 |
|    2 |   22 | NULL |
|    2 |  222 | NULL |
|    2 | 2222 | NULL |
|    2 | NULL |   22 |
|    2 | NULL |  222 |
|    2 | NULL | 2222 |
+------+------+------+
10 rows in set (0.00 sec)

可以看到，前4条数据是由{"a": 1, "b": [11,111]}得来的，b1和b2列理论上能获得11和111两个值，但是查询结果给b1和b2分别生成了2条记录。后6条数据是由{"a": 2, "b": [22,222,2222]}得来的，b1和b2列能获得22，222，2222三个值，但是查询结果给b1和b2分别生成了3条记录。
mysql> SELECT *
    -> FROM
    ->   JSON_TABLE(
    ->     '[{"a": "a_val",
    '>       "b": [{"c": "c_val", "l": [1,2]}]},
    '>     {"a": "a_val",
    '>       "b": [{"c": "c_val","l": [11]}, {"c": "c_val", "l": [22]}]}]',
    ->     '$[*]' COLUMNS(
    ->       top_ord FOR ORDINALITY,
    ->       apath VARCHAR(10) PATH '$.a',
    ->       NESTED PATH '$.b[*]' COLUMNS (
    ->         bpath VARCHAR(10) PATH '$.c',
    ->         ord FOR ORDINALITY,
    ->         NESTED PATH '$.l[*]' COLUMNS (lpath varchar(10) PATH '$')
    ->         )
    ->     )
    -> ) as jt;
+---------+-------+-------+------+-------+
| top_ord | apath | bpath | ord  | lpath |
+---------+-------+-------+------+-------+
|       1 | a_val | c_val |    1 | 1     |
|       1 | a_val | c_val |    1 | 2     |
|       2 | a_val | c_val |    1 | 11    |
|       2 | a_val | c_val |    2 | 22    |
+---------+-------+-------+------+-------+
4 rows in set (0.00 sec)

 

五、JSON格式校验

5.1、JSON_SCHEMA_VALID(schema,document)
校验document文档是否符合schema定义的JSON规则。

document和schema必须都是标准的JSON格式。

schema中的require参数表示必须要有的key。

假如定义一个schema：
mysql> SET @schema = '{
    '>  "id": "http://json-schema.org/geo",
    '>  "$schema": "http://json-schema.org/draft-04/schema#",
    '>  "description": "A geographical coordinate",
    '>  "type": "object",
    '>  "properties": {
    '>   "latitude": {
    '>     "type": "number",
    '>     "minimum": -90,
    '>     "maximum": 90
    '>   },
    '>   "longitude": {
    '>     "type": "number",
    '>     "minimum": -180,
    '>     "maximum": 180
    '>   }
    '>  },
    '>  "required": ["latitude", "longitude"]
    '> }';
Query OK, 0 rows affected (0.00 sec)

然后定义document：

mysql> SET @document = '{
    '>  "latitude": 63.444697,
    '>  "longitude": 10.445118
    '> }';
Query OK, 0 rows affected (0.00 sec)

最后用JSON_SCHEMA_VALID(schema,document)函数来校验：

mysql> SELECT JSON_SCHEMA_VALID(@schema, @document);
+---------------------------------------+
| JSON_SCHEMA_VALID(@schema, @document) |
+---------------------------------------+
|                                     1 |
+---------------------------------------+
1 row in set (0.00 sec)

返回1即为符合schema定义的规范。

下面把document改成空的：

mysql> SET @document = '{}';
Query OK, 0 rows affected (0.00 sec)

再校验一下：

mysql> SELECT JSON_SCHEMA_VALID(@schema, @document);
+---------------------------------------+
| JSON_SCHEMA_VALID(@schema, @document) |
+---------------------------------------+
|                                     0 |
+---------------------------------------+
1 row in set (0.00 sec)

返回0表示校验失败。

5.2、JSON_SCHEMA_VALIDATION_REPORT(schema,document)
校验document文档是否符合schema定义的JSON规则，并返回JSON格式的校验报告。

如果校验成功，则返回：{"valid": true}。

如果校验失败，则返回的校验报告中会包含以下内容：
valid。校验失败则为false。
reason。失败原因。
schema-location。校验失败的节点在schema中的位置。
document-location。校验失败的节点在document中的位置。
schema-failed-keyword。包含校验失败节点的一段schema的关键字。
一个校验失败的场景：

mysql> SET @schema = '{
    '>  "id": "http://json-schema.org/geo",
    '> "$schema": "http://json-schema.org/draft-04/schema#",
    '> "description": "A geographical coordinate",
    '> "type": "object",
    '> "properties": {
    '>   "latitude": {
    '>     "type": "number",
    '>     "minimum": -90,
    '>     "maximum": 90
    '>   },
    '>   "longitude": {
    '>     "type": "number",
    '>     "minimum": -180,
    '>     "maximum": 180
    '>   }
    '> },
    '> "required": ["latitude", "longitude"]
    '> }';
Query OK, 0 rows affected (0.00 sec)
 
mysql> SET @document = '{
    '> "latitude": 63.444697,
    '> "longitude": 310.445118
    '> }';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT JSON_PRETTY(JSON_SCHEMA_VALIDATION_REPORT(@schema, @document));
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| JSON_PRETTY(JSON_SCHEMA_VALIDATION_REPORT(@schema, @document))                                                                                                                                                                                                                        |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {
  "valid": false,
  "reason": "The JSON document location '#/longitude' failed requirement 'maximum' at JSON Schema location '#/properties/longitude'",
  "schema-location": "#/properties/longitude",
  "document-location": "#/longitude",
  "schema-failed-keyword": "maximum"
} |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

 

六、JSON辅助函数

6.1、JSON_PRETTY(json_val)

格式化JSON值，让输出更好看一些。

参数必须是JSON值或者是符合JSON格式的字符串。

6.2、JSON_STORAGE_FREE(json_val)
此函数的功能是，在使用JSON_SET()，JSON_REPLACE(),JSON_REMOVE()函数修改了列值之后，JSON释放了多少空间。

多次使用这三个函数修改列值，此函数返回值会累加。

不使用这三个函数修改列值时，比如直接用set关键字修改列值，则此函数返回0。

只对修改表中存储JSON值的场景有效，如果用三个函数修改用户变量，此函数返回值依然是0。

参数是JSON字符串，返回值会是0。
 

6.3、JSON_STORAGE_SIZE(json_val)
此函数返回的是一个JSON值转换成二进制后占用的磁盘空间。

如果参数是一个JSON格式的列，则表示此列的JSON值转换成二进制后占用的磁盘空间。

如果参数是一个JSON格式的字符串，则表示此字符串代表的JSON值转换成二进制后会占用的磁盘空间。

JSON_SET()，JSON_REPLACE(),JSON_REMOVE()三个函数会部分更新JSON值，不会导致此函数的返回值发生变化。直接用set关键字修改列值会导致此函数返回值发生变化。

因为用户变量不能部分修改，所以当修改用户变量时，此函数的返回值会立刻变化。
 

 

 

 

 

 

 

 

 

 

wiscourper_
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
Json路径表达式
A-MING的博客
08-26 471
CONENT里面的内容。
Mysql Json
wilsonLiujin的博客
05-19 2472
Mysql Json应用场景分析
lookup:使用简单的 JSON 路径表达式在 JSON 文档中查找字符串
05-30
抬头 这个包提供了一种使用简单的 JSON 路径表达式搜索和操作 JSON 文件的方法。 公用事业 包含一个名为loookup的实用程序，当给定一个简单的 JSON 路径表达式时，该实用程序可用于在 JSON 文档中查找值。 你可以做类似x.store.book[0].title 。 x是根节点。 这是 JSON 路径的简单实现。 只有. 和[ ]到目前为止受支持。
mysql json_quote和json_unquote的用法
最新发布
codedadi的博客
06-07 629
总之，JSON_QUOTE() 和 JSON_UNQUOTE() 函数在处理 MySQL 中的 JSON 字符串时非常有用，可以帮助你确保字符串在 JSON 上下文中的正确性，并轻松地提取和清理 JSON 字段中的值。在 MySQL 中，JSON_QUOTE() 和 JSON_UNQUOTE() 函数与 JSON 数据类型的处理有关。注意，在上面的例子中，我使用了两个反斜杠来表示 JSON 字符串中的一个反斜杠，因为反斜杠在 SQL 字符串中也是一个转义字符。注意输出中的双引号和反斜杠都被转义了。
JSON路径表达式
程序猿的实验室
06-29 3530
JSON
JsonPath - 根据表达式路径解析Json
唱、跳、Rap、篮球
07-11 3729
JsonPath 在xml的使用过程中，对于xml的解析我们知道可以使用xpath的方式，随意的获取到我们想要的属性值。那么在使用json时，我们能不能实现同样的操作呢？ 答案就是 json-path 基础介绍 跟 XPath 类似，JsonPath 通过路径来检索JSON，对语法格式如下 语法 符号 描述 $ 表示json的根节点，表示根节点下的所有数据 . 表示子节点，如 $.store 表示根节点下的store节点下的所有数据 … 可实现递归搜索，如 $…title 表示搜索j
MySQL JSON 路径表达式语法
常备不懈
03-04 986
MySQL中支持的许多JSON函数，需要路径表达式以便识别JSON文档中的特定元素。路径由路径的作用域和一个或多个路径段组成。在MySQL JSON函数中使用的路径中，作用域始终是正在搜索或以其他方式操作的文档，由前导的$字符表示。路径段由句点字符（.）分隔。数组中的单元格由[N]表示，其中N是非负整数。键的名称必须是双引号字符串或有效的ECMAScript标识符。正如前面所指出的，在MySQL中，路径的作用域始终是表示为的正在操作的文档。您可以在JSON路径表达式中使用”作为文档的同义词。
如何Jmeter中书写正确的jsonpath表达式
m0_66501929的博客
12-20 1010
也就是:{key1:value1,key2:value2,key3:value3,key4:value4,key5:value5}这种情况如果要提取key2对应的所有值value1、value2、value3，那么表达式就是:$.key1[*].key3。这种情况如果要提取value3，那么表达式就是：$.key1.key2.key3。这种情况如果要提取value1，那么表达式就是：$.key1。
MySQL中的JSON
Valineliu
08-25 8588
从5.7.8开始，MySQL开始支持JSON类型，用于存储JSON数据。JSON类型的加入模糊了关系型数据库与NoSQL之间的界限，给日常开发也带来了很大的便利。这篇文章主要介绍一下MySQL中JSON类型的使用，主要参考MySQL手册：https://dev.mysql.com/doc/refman/8.0/en/......
mysql5.7JSON字段
02-07
mysql5.7以上具有json数据类型了，文档是学习使用JSON文档。
简单谈谈MySQL5.7 JSON格式检索
09-09
MySQL 5.7.7 labs版本开始InnoDB存储引擎已经原生支持JSON格式，该格式不是简单的BLOB类似的替换。下面我们来详细探讨下吧
MySQL数据生成JSON数据
08-03
MySQL是一种广泛使用的开源关系型数据库管理系统，而JSON（JavaScript Object Notation）则是一种轻量级的数据交换格式，常用于Web服务和前后端数据传输。在MySQL中，将数据转换为JSON格式是常见的需求，尤其在处理...
MySQL插入json问题
09-09
MySQL在5.7.8版本之后引入了对JSON数据类型的原生支持，这极大地扩展了数据库处理复杂数据结构的能力。JSON（JavaScript Object Notation）是一种轻量级的数据交换格式，常用于存储和传输数据，特别是在Web服务和...
Mysql当中Json相关的函数详解
热门推荐
怪咖@的博客
04-30 1万+
从5.7开始，MySQL开始支持json类型，用于存储JSON数据。提供数据类型的同时也提供了很多关于json的函数供我们使用，本篇文章基本上都来源于官网;
详解Mysql中的JSON系列操作函数
我想我是海 冬天的大海 心情随风轻摆
02-28 1867
原文：http://www.jb51.net/article/89219.htm、http://blog.csdn.net/joker_honey/article/details/53081716 一、方法罗列： 分类 函数 描述 创建json json_array 创建json数组 json_object 创建json对象 json_quote 将json转成json字符串类型 查询jso...
JSON 表达式
好记性不如烂笔头
04-17 3761
JSON语法规则：     数据在名称/值对中；     数据由逗号分隔；     大括号保存对象；     中括号保存数组 1、访问对象值： var myObj,x; myObj = {"name":"runoob","alexa":10000,"site":null}; x = myObj.name; //使用点号（.）来访问对象的值 x = myObj["name"]; //使用中括号（...
MYSQL JSON 函数
程小明的博客
07-01 8432
MYSQL JSON函数 json API
mysql：列类型之json
火焰
02-22 4299
环境： 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
07-27
MySQL 提供了对 JSON 数据类型的支持。你可以在 MySQL 中存储和操作 JSON 数据。以下是一些关于 MySQL JSON 的常见问题和答案： 1. 如何在 MySQL 中创建存储 JSON 数据的列？ 在创建表时，可以使用以下语法来定义一个存储 JSON 数据的列： ``` CREATE TABLE table_name (column_name JSON); ``` 2. 如何插入 JSON 数据到 MySQL 表中？ 可以使用 INSERT INTO 语句将 JSON 数据插入到表中的 JSON 列中。例如： ``` INSERT INTO table_name (column_name) VALUES ('{"key": "value"}'); ``` 3. 如何从 MySQL 表中检索 JSON 数据？ 可以使用 SELECT 语句来检索 JSON 数据。例如： ``` SELECT column_name->"$.key" FROM table_name; ``` 4. 如何更新 MySQL 表中的 JSON 数据？ 可以使用 UPDATE 语句来更新 JSON 数据。例如： ``` UPDATE table_name SET column_name = JSON_SET(column_name, "$.key", "new_value") WHERE condition; ``` 5. 如何使用 MySQL 中的内置函数处理 JSON 数据？ MySQL 提供了一系列内置函数来处理 JSON 数据，例如：JSON_EXTRACT、JSON_ARRAY、JSON_OBJECT、JSON_ARRAY_APPEND 等。你可以根据需要使用这些函数来操作和处理 JSON 数据。 请注意，MySQL 中的 JSON 支持从版本 5.7 开始引入，并在之后的版本中得到了改进和增强。在使用之前，请确保你使用的 MySQL 版本支持 JSON 数据类型和相关的函数。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值