首页 > 数据库 >mysql 8.0数据类型 json

mysql 8.0数据类型 json

时间:2024-09-10 23:36:50浏览次数:5  
标签:info 8.0 数据类型 JSON json user NULL select

mysql 8.0 新增数据类型 json。

5.7通过blob等类型来保存json格式的数据,为什么还要专门增加这一数据格式的支持呢?

    1.保证了JSON数据类型的强校验:JSON数据列会自动校验存入此列的内容是否符合JSON格式,
        非正常格式则报错,而varchar类型和text等类型本身是不存在这种机制的。
    2.存储在JSON列中的JSON数据会被转成内部特定的存储格式,允许快速读取。
    3.可以基于JSON支持修改特定的键值。(即不需要把整条内容拿出来放到程序中遍历,替换再整个保存,MySQL内置的函数允许修改json中单个健值)


    MySQL对JSON的支持提供一系的操作方法:

    JSON_APPEND()                 将数据追加到JSON文档
    JSON_ARRAY()                    创建JSON数组
    JSON_ARRAY_APPEND()         将数据追加到JSON文档
    JSON_ARRAY_INSERT()           插入JSON数组
    JSON_SET()                      将数据插入JSON文档

    ->                               从JSON列返回值;等效于JSON_EXTRACT()。
    JSON_EXTRACT()                 从JSON文档返回数据
    JSON_CONTAINS()              JSON文档是否在路径中包含特定对象
    JSON_CONTAINS_PATH()         JSON文档是否在路径中包含任何数据
    JSON_DEPTH()                  JSON文档的最大深度

    ->>                             从JSON列返回值;等效于JSON_UNQUOTE(JSON_EXTRACT())。
    JSON_INSERT()                 将数据插入JSON文档
    JSON_KEYS()                   JSON文档中的键的数组
    JSON_LENGTH()                JSON文档中的元素数
    JSON_MERGE()                 合并JSON文档
    JSON_OBJECT()                创建JSON对象
    JSON_QUOTE()                   引用JSON文档
    JSON_REMOVE()               从JSON文档中删除数据
    JSON_REPLACE()                 替换JSON文档中的值
    JSON_SEARCH()               JSON文档中值的路径
    JSON_TYPE()                  JSON值的类型
    JSON_UNQUOTE()             取消引用JSON值
    JSON_VALID()                  JSON值是否有效

JSON列存储的数据要么是NULL,要么必须是JSON格式数据,否则会报错。
JSON数据类型是没有默认值的(声明时"DEFAULT NULL")。

create table json_test(
id int not null primary key auto_increment,
user_info json,
status tinyint(4)
);

insert into json_test(user_info,status) values
('{"user_name":"wukkk","age":25,"sex":0,"amount":8}',1),
('{"user_name":"wuod","age":22,"sex":0,"amount":1129}',1),
('{"user_name":"jacn","age":19,"sex":1}',1),
('[3, {"a": [5, 6], "b": "jack"}, [99, 100]]',1)

检查数据:
1   {"age": 25, "sex": 0, "amount": 8, "user_name": "wukkk"}    1
2   {"age": 22, "sex": 0, "amount": 1129, "user_name": "wuod"}  1
3   {"age": 19, "sex": 1, "user_name": "jacn"}  1
4   [3, {"a": [5, 6], "b": "jack"}, [99, 100]]  1


一、mysql的json

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

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

    select
    json_extract(user_info,'$[0]'),
    json_extract(user_info,'$[1]'),
    json_extract(user_info,'$[2]')
    from json_test where id=4;

    3   {"a": [5, 6], "b": "jack"}  [99, 100]

    select
    json_extract(user_info,'$[1].a'),
    json_extract(user_info,'$[1].a[0]'),
    json_extract(user_info,'$[2][0]')
    from json_test where id=4;

    [5, 6]  5   99

    跟python切片操作类似。


二、创建、构建JSON 对象

(1)创建json对象 json_object(跟python字典一样)

    select json_object("user_name", "enjoy", "age", 35,"amount",110);

    {"age": 35, "amount": 110, "user_name": "enjoy"}

(2)创建json数组 JSON_ARRAY (跟python列表一样)

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

    [1, "abc", null, true, "15:45:42.000000"]


    注意特殊情况(直接在数组中加入json字符串):
    SELECT JSON_ARRAY(1, "abc", NULL, TRUE, CURTIME(),'{"user_name":"wuod","age":22}');

    [1, "abc", null, true, "16:20:58.000000", "{\"user_name\":\"wuod\",\"age\":22}"]
    被识别成了字符串。但是插入json字段的时候,可以直接字符串插入。

    正确方式:
    SELECT JSON_ARRAY(1, "abc", NULL, TRUE, CURTIME(),json_object("user_name","wuod","age",22));

    [1, "abc", null, true, "16:23:33.000000", {"age": 22, "user_name": "wuod"}]

(3)转换json字符串为json CONVERT(json_string,JSON)

    如上面的问题,json字符串:
    SELECT JSON_ARRAY(1, "abc", NULL, TRUE, CURTIME(),'{"user_name":"wuod","age":22}');

    改为:
    SELECT JSON_ARRAY(1, "abc", NULL, TRUE, CURTIME(),convert('{"user_name":"wuod","age":22}',json));

    [1, "abc", null, true, "16:40:48.000000", {"age": 22, "user_name": "wuod"}]


(4)JSON_QUOTE(json_val) 将json_val用"号括起来。

    select json_quote('null'),json_quote('"null"');

    "null"  "\"null\""

    select json_quote('[1,2,2]'),convert('{"user_name":"wuod","age":22}',json)

    "[1,2,2]"   {"age": 22, "user_name": "wuod"}

 

三、查询函数

(1)判断数据是否存在   JSON_CONTAINS(json_doc, val[, path])
    查询json文档是否在指定path包含指定的数据,包含则返回1,否则返回0。如果有参数为NULL或path不存在,则返回NULL。

    select json_contains(user_info,'1129','$.amount') from json_test;

    0
    1
    NULL
    NULL

(2)判断路径是否存在   JSON_CONTAINS_PATH(json_doc, one_or_all, path[, path] ...)
    查询是否存在指定路径,存在则返回1,否则返回0。如果有参数为NULL,则返回NULL。
    one_or_all只能取值"one"或"all",one表示只要有一个存在即可;all表示所有的都存在才行。

    select json_contains_path(user_info,'one','$.amount','$.age') from json_test

    1
    1
    1
    0

    select json_contains_path(user_info,'all','$.amount','$.age') from json_test;

    1
    1
    0
    0

(3)查询包含指定字符串(value值,不是key)的路径  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下查。

    #搜索user_info下value匹配字符串 的路径
    select id,json_search(user_info,'one','wu%') from json_test where id BETWEEN 2 and 3;

    2   "$.user_name"
    3   NULL

    #在指定路径下,搜索指定字符串路径。
    select id,json_search(user_info,'all','ja%',NULL,'$[1]') from json_test where id = 4;
    等同:
    select id,json_search(user_info,'all','jac_',NULL,'$[1]') from json_test where id = 4;

    4   "$[1].b"


(4)读取JSON里数据  JSON_EXTRACT(json_doc, path[, path] ...)
    从json文档里抽取数据。如果有参数有NULL或path不存在,则返回NULL。如果抽取出多个path,则返回的数据封闭在一个json array里。

    select id,json_extract(user_info,'$.user_name') as name,json_extract(user_info,'$.amount') from json_test;

    1   "wukkk" 8
    2   "wuod"  1129
    3   "jacn"  NULL
    4    NULL   NULL

    select id,json_extract(user_info,'$.user_name','$.amount') as amount from json_test;

    1   ["wukkk", 8]
    2   ["wuod", 1129]
    3   ["jacn"]
    4   NULL

    #JSON数据过滤( -> 等同 json_extract)
    select id,user_info -> '$.user_name' as name from json_test where user_info -> '$.age' >20;
    1   "wukkk"
    2   "wuod"

    # like rlike等比较。可以使用
    select id,user_info -> '$.user_name' as name from json_test where user_info -> '$.user_name' like 'wu%';
    NULL  NULL
    select id,user_info -> '$.user_name' as name from json_test where user_info -> '$.user_name' like '%wu%';
    1   "wukkk"
    2   "wuod"


    # age过滤,group by 分组查找。 order by也可以使用JSON里数据来排序。
    select id,user_info -> '$.user_name' as name from json_test where user_info -> '$.age' > 10 GROUP BY user_info -> '$.sex';

    1   "wukkk"
    3   "jacn"

    原始数据:
    1   {"age": 25, "sex": 0, "amount": 8, "user_name": "wukkk"}    1
    2   {"age": 22, "sex": 0, "amount": 1129, "user_name": "wuod"}  1
    3   {"age": 19, "sex": 1, "user_name": "jacn"}  1
    4   [3, {"a": [5, 6], "b": "jack"}, [99, 100]]  1


(5)去掉抽取结果的"号,与上相反。
    以下三种方式效果一样:
    JSON_UNQUOTE( JSON_EXTRACT(column, path) )
    JSON_UNQUOTE(column -> path)
    column->>path

    select
    json_unquote(json_extract(user_info,'$.user_name')),
    json_unquote(user_info -> '$.user_name'),
    user_info ->> '$.user_name'
    from json_test where id =1;


(6)JSON_KEYS(json_doc[, path])
    获取json文档在指定路径下的所有键值,返回一个json array。如果有参数为NULL或path不存在,则返回NULL。

    select id,json_keys(user_info) from json_test where id=1;

    1   ["age", "sex", "amount", "user_name"]

    select id,json_keys(user_info,'$[1]') from json_test where id=4;

    4   ["a", "b"]

8.0中:

JSON值可以使用 =, <, <=, >, >=, <>, != 和 <=> 运算符进行比较。

<=> : NULL安全相等。此运算符执行与 = 号运算符类似的相等比较,但如果两个操作数均为NULL,则返回1而不是NULL;如果一个操作数为NULL,则返回0而不是NULL。
对于行比较,(a, b) <=> (x, y) 等效于 (a <=> x) AND (b <=> y)

JSON值尚不支持以下比较运算符和函数:

    BETWEEN
    IN()
    GREATEST()
    LEAST()

为了使用上述列出的比较运算符和函数,可以将JSON值转换为本机MySQL数值或字符串数据类型,以便它们具有一致的非JSON标量类型。

JSON值的比较发生在两个级别。
第一级比较基于比较值的JSON类型。如果类型不同,则比较结果仅由哪种类型具有更高优先级来确定。
如果这两个值具有相同的JSON类型,则使用特定于类型的规则进行第二级比较。

从最高优先级到最低优先级。(类型名称是JSON_TYPE()函数返回的类型名称。)同一行显示的类型优先级相同。列表中前面列出的任何具有JSON类型的值都比列表中稍后列出的具有JSON类型的任何值都要大。

    BLOB
    BIT
    OPAQUE
    DATETIME
    TIME
    DATE
    BOOLEAN
    ARRAY
    OBJECT
    STRING
    INTEGER, DOUBLE
    NULL

对于具有相同优先级的JSON值,比较规则是特定于类型的:

  BLOB:比较两个值的前N个字节,其中N是较短值中的字节数。如果两个值的前N个字节相同,则将较短值排在前面。
  BIT:与 BLOB规则相同
  OPAQUE:与 BLOB 规则相同。OPAQUE 值是未归类为其他类型之一的值。

  DATETIME:表示较早时间点的值排在表示较晚时间点的值之前。如果两个值最初分别来自MySQL DATETIME 和 TIMESTAMP类型,且它们表示相同的时间点,则它们相等。
  TIME:两个时间值中较小的一个排在较大的前面。
  DATE:较早的日期排在最近的日期之前。
  ARRAY:如果两个JSON数组具有相同的长度并且数组中相应位置的值相等,则它们是相等的。
  如果数组不相等,则它们的顺序由第一个不同的元素来确定。首先排序在该位置具有较小值的数组。
  如果较短数组中的所有值都等于较长数组中的相应值,则首先排序较短的数组。

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

  BOOLEAN:为 false的JSON值小于 为true的JSON值。
  OBJECT:如果它们有相同的键值对,它们就是相等的。键的顺序不需要完全相同。
    {"a": 1, "b": 2} = {"b": 2, "a": 1}
    两个不相等的对象的顺序是未指定的但是确定性的。

  JSON_OBJECT() 在处理JSON文档时会将key排序,所以键值的顺序对于对象的比较没有影响。关于排序的结果,可以看到,就是按照key1,value1,key2,value2...这样的顺序来比较,比较规则与 ARRAY 相同。

    mysql>     select JSON_OBJECT('a',3, 'b', 2) = JSON_OBJECT('b',2, 'a', 3);
    >    1

    mysql>    select JSON_OBJECT('b',2, 'a', 2);
    >    {"a": 2, "b": 2}

    mysql>     select JSON_OBJECT('a',3, 'b', 2) < JSON_OBJECT('b',2, 'a', 2);
    >    0

    mysql>     select JSON_OBJECT('a',3, 'b', 2) < JSON_OBJECT('b',2, 'a', 3, 'c',3);
    >    1

  STRING:字符串比较时会按照utf8mb4来比较。规则是前N个字节上按字母顺序排序,其中N是较短字符串的长度。如果两个字符串的前N个字节相同,则较短的字符串小于较长的字符串。
    "a" < "ab" < "b" < "bc"
    这种排序等同于使用排序规则utf8mb4_bin 的SQL字符串的排序。因为utf8mb4_bin是二进制排序规则,所以JSON值的比较区分大小写
    "A" < "a"

  INTEGER,DOUBLE :JSON值可以包含精确值数字和近似值数字。需要注意的是,比较JSON值中的数字规则与比较MySQL数值类型的规则有所不同。在使用本机MySQL INT和DOUBLE数字类型进行比较时,已知所有比较都涉及int和double,因此对于所有行,int 将转化为 double。也就是说精确值数字被转化为近似值数字。

  另一方面,如果查询比较包含数字的两个JSON列,则无法事先知道数字是 int 还是 double。为了在所有行中提供最一致的行为,MySQL将近似值数字转化为精确值数字。生成的顺序是一致的,并且不会丢失精确值数字的精度。

    9223372036854775805 < 9223372036854775806 < 9223372036854775807
    < 9.223372036854776e18 = 9223372036854776000 < 9223372036854776001

如果JSON比较使用非JSON数字比较规则,则可能发生不一致的排序。通常数字按MySQL排序规则后是下列结果:

Integer 比较

  9223372036854775805 < 9223372036854775806 < 9223372036854775807
Double 比较

  9223372036854775805 = 9223372036854775806 = 9223372036854775807 = 9.223372036854776e18

任何JSON值与 SQL的NULL值比较,结果都是UNKNOWN。

为了比较JSON值和非JSON值,非JSON值会按照下列规则转化为JSON值,然后按照之前提到的规则做比较。

 

JSON值的ORDER BY 和 GROUP BY 按照以下原则工作:

标量JSON值的排序使用上文所述规则

对于升序排序,SQL NULL排在所有JSON值之前,包括JSON空文字;对于降序排序,SQL NULL排在所有JSON值之后。

JSON的键排序时,需要 max_sort_length 系统变量来确定参与比较的字符数,比如 ‘abc’ 和 ‘abcdefg’ 比较时,如果 max_sort_length = 3,那么参与比较的字符其实是 ‘abc’ 和 ‘abc’,即 ‘abc’ = ‘abcdefg’;

如果max_sort_length = 4, 那么参与比较的字符其实是 'abc' 和 'abcd',即 ‘abc’ < 'abcdefg';

目前不支持对非标量值进行排序,会发出警告。

对于排序,将JSON标量转换为其它一些本地MySQL类型可能是有益的。例如,如果名为jdoc的列包含具有由id键和非负值组成的成员的JSON对象,请使用此表达式按id值排序:

  ORDER BY CAST(JSON_EXTRACT(jdoc, '$.id') AS UNSIGNED)
如果确定生成的列定义为使用与ORDER BY中相同的表达式,则MySQL优化器会识别并考虑使用索引执行查询计划。


四、修改函数

(1)追加value  JSON_ARRAY_APPEND(json_doc, path, val[, path, val] ...)    JSON_APPEND已经丢弃
在指定path的json array尾部追加val。如果指定path是一个json object,则将其封装成一个json array再追加。如果有参数为NULL,则返回NULL。

    select json_array_append(user_info,'$[1]',12) from json_test where id=4;

    [3, [{"a": [5, 6], "b": "jack"}, 12], [99, 100]]

    select json_array_append(user_info,'$[1].a',12) from json_test where id=4;

    [3, {"a": [5, 6, 12], "b": "jack"}, [99, 100]]

    select json_array_append(user_info,'$[1].a',12,'$[1]',1) from json_test where id=4;

    [3, [{"a": [5, 6, 12], "b": "jack"}, 1], [99, 100]]


(2)插入数据value  JSON_ARRAY_INSERT(json_doc, path, val[, path, val] ...)
 在path指定的json array元素插入val,原位置及以右的元素顺次右移。如果path指定的数据非json array元素,则略过此val;如果指定的元素下标超过json array的长度,则插入尾部。

    #在指定位置,插入value
    select json_array_insert(user_info,'$[1]','x') from json_test where id=4;

    [3, "x", {"a": [5, 6], "b": "jack"}, [99, 100]]

    #指定位置超长,在末尾插入
    select json_array_insert(user_info,'$[100]','x') from json_test where id=4;

    [3, {"a": [5, 6], "b": "jack"}, [99, 100], "x"]


(3)插入更新数据value  JSON_INSERT/JSON_REPLACE/JSON_SET

JSON_INSERT(json_doc, path, val[, path, val] ...)
在指定path下插入数据,如果path已存在,则忽略此val(不存在才插入)。

    select json_insert(user_info,'$[1]',10) from json_test where id>2;

    [{"age": 19, "sex": 1, "user_name": "jacn"}, 10]
    [3, {"a": [5, 6], "b": "jack"}, [99, 100]]

    第三条,为一个json_object,视为json_array中的$[0],没有$[1]所以插入。
    第四条,为一个json_array,已经存在$[1],因此忽略。

JSON_REPLACE(json_doc, path, val[, path, val] ...)
替换指定路径的数据,如果某个路径不存在则略过(存在才替换)。如果有参数为NULL,则返回NULL。

    select json_replace(user_info,'$[1]',10) from json_test where id>2;

    {"age": 19, "sex": 1, "user_name": "jacn"}
    [3, 10, [99, 100]]

    第三条,为一个json_object,视为json_array中的$[0],没有$[1]所以忽略。
    第四条,为一个json_array,已经存在$[1],因此将整个$[1]:{"a": [5, 6], "b": "jack"} 替换成10。


JSON_SET(json_doc, path, val[, path, val] ...)
设置指定路径的数据(不管是否存在)。如果有参数为NULL,则返回NULL。

    select json_set(user_info,'$[1]',10) from json_test where id>2;

    [{"age": 19, "sex": 1, "user_name": "jacn"}, 10]
    [3, 10, [99, 100]]

    第三条,为一个json_object,视为json_array中的$[0],没有$[1]所以转为json_array之后,添加$[1]:10。
    第四条,为一个json_array,已经存在$[1],因此将整个$[1]:{"a": [5, 6], "b": "jack"} 替换成10。


(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再按照规则一进行merge。

    初始化2个json:一个json_object,一个json_array。
    set @doc_1 = json_object("age",19);
    set @doc_2 = json_array("age",19,json_object("sex",1));

    select json_merge(@doc_1,@doc_1);
    {"age": [19, 19]}
    相同key的json_object,合并之后还是一个json_object,但是他的value是一个集合json_array。

    select json_merge(@doc_1,@doc_2);
    [{"age": 19}, "age", 19, {"sex": 1}]
    在多种类型下,合并成一个json_array,里面包含json_object和json_array的值。

    select json_merge(@doc_1,user_info -> '$.age') from json_test where id=3;

    [{"age": 19}, 19]
    自定义的变量doc_1的json_object,与字段user_info中的age值合并成一个json_array。

    select json_merge(user_info -> '$[0]',user_info -> '$[2]') from json_test where id=4;

    [3, 99, 100]
    将user_info中两个array值进行合并。

8.0中:JSON_MERGE丢弃。
JSON_MERGE_PRESERVE():将多个对象的键值对组合成一个新对象,会将重复键的值组成一个数组(不会去除重复值和null)赋给这个键。等同json_merge
JSON_MERGE_PATCH():将多个对象的键值对组合成一个新对象,重复键的值会用 last duplicate key wins(最后重复秘钥获胜)规则取最后一个。

    mysql> SELECT
        ->   JSON_MERGE_PRESERVE('[1, 2]', '["a", "b", "c"]', '[true, false]') AS Preserve,
        ->   JSON_MERGE_PATCH('[1, 2]', '["a", "b", "c"]', '[true, false]') AS Patch\G
    *************************** 1. row ***************************
    Preserve: [1, 2, "a", "b", "c", true, false]
       Patch: [true, false]
    注意:方法中都允许有null ,但是结果需要注意。JSON_MERGE_PRESERVE() 方法中,若作为参数的数组中有个null值,那最终结果中也会有个null,方法不会去除null值和重复值。

    mysql> SELECT JSON_MERGE_PRESERVE('[1, 2]', '["a", "b", "c"]','[true, false]','["a", null, "c"]') AS Preserve;

    [1, 2, "a", "b", "c", true, false, "a", null, "c"]

    但如果作为参数的数组本身就是null,那合并结果也是null

    SELECT JSON_MERGE_PRESERVE('[1, 2]', '["a", "b", "c"]',null,'[true, false]') AS Preserve;
    Null

    JSON_MERGE_PATCH() 方法由于只保留最后一个数组,所以null对它并没有什么影响,只有当最后一个参数为null时结果才为null

    SELECT JSON_MERGE_PATCH('[1, 2]', '["a", "b", "c"]', '[true, false]',null,'["a", "b", "c"]',null) AS Patch;
    Null


合并对象:将多个对象合并成单个对象

JSON_MERGE_PRESERVE():将多个对象的键值对组合成一个新对象,会将重复键的值组成一个数组(不会去除重复值和null)赋给这个键。
JSON_MERGE_PATCH():将多个对象的键值对组合成一个新对象,重复键的值会用 last duplicate key wins(最后重复秘钥获胜)规则取最后一个。

    SELECT JSON_MERGE_PRESERVE('{"a": 1, "b": 2}', '{"c": 3, "a": 1}', '{"c": 5, "d": 3}','{"d":null}') AS Preserve,
    JSON_MERGE_PATCH('{"a": 3, "b": 2}', '{"c": 3, "a": 4}', '{"c": 5, "d": 3}') AS Patch\G
    *************************** 1. row ***************************
    Preserve: {"a": [1, 1], "b": 2, "c": [3, 5], "d": [3, null]}
       Patch: {"a": 4, "b": 2, "c": 5, "d": 3}


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

    select json_remove(user_info,'$.amount') from json_test where id between 2 and 3;

    {"age": 22, "sex": 0, "user_name": "wuod"}
    {"age": 19, "sex": 1, "user_name": "jacn"}
    删除user_info下的amount数据(包括key和value),第四条没有忽略。

    select json_remove(user_info,'$[1]') from json_test where id=4;

    [3, [99, 100]]

    select json_remove(user_info,'$[10]') from json_test where id=4;

    [3, {"a": [5, 6], "b": "jack"}, [99, 100]]
    对于json_array也一样。注意删除之后,不会合并,保持原类型结构。

注意:删除的路径不能包含 *,** 以及一个范围array.也就是说,只能是确定的路径。

删除的问题:

(1)无法删除一个json_array中的json_object:
    原值:[3, {"a": [5, 6], "b": "jack"}, [99, 100]]

    select json_remove(user_info,'$[0]','$[1]','$[2]') from json_test where id=4;

    [{"a": [5, 6], "b": "jack"}]
    这里$[1]删除是失败的,这是一个json_object,并没有被删除。

    select json_remove(user_info,'$[0]','$[1].a','$[2]') from json_test where id=4;

    [{"a": [5, 6], "b": "jack"}, [99, 100]]

    尝试删除$[1]中的key为a的,失败,而且导致$[2]也删除失败。

    select json_remove(user_info,'$[1].a') from json_test where id=4;

    [3, {"b": "jack"}, [99, 100]]
    单个删除,则是可以的。

(2) 常用:不知道路径的情况下,删除指定匹配字符串的数据。
    select json_remove(user_info,
    JSON_UNQUOTE(json_search(user_info,'one','jac_',NULL,'$[1]'))
    ) from json_test where id=4;


五、JSON特性查询

原始数据:
1   {"age": 25, "sex": 0, "amount": 8, "user_name": "wukkk"}    1
2   {"age": 22, "sex": 0, "amount": 1129, "user_name": "wuod"}  1
3   {"age": 19, "sex": 1, "user_name": "jacn"}  1
4   [3, {"a": [5, 6], "b": "jack"}, [99, 100]]  1


(1)json文档的深度   JSON_DEPTH(json_doc)
    获取json文档的深度。如果参数为NULL,则返回NULL。
    空的json array、json object或标量的深度为1。

    SELECT JSON_DEPTH('{}'), JSON_DEPTH('[]'), JSON_DEPTH('true'),json_depth(NULL);

    1   1   1   NULL

    select json_depth(user_info) from json_test where id>2;

    2
    4


(2)json文档的长度  JSON_LENGTH(json_doc[, path])
    获取指定路径下的长度。如果参数为NULL,则返回NULL。 
    长度的计算规则:

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

    select json_length(user_info) from json_test where id>2
    3
    3

    #指定路径下统计
    select json_length(user_info,'$[1]') from json_test where id=4
    2

(3)JSON_TYPE(json_val)
    获取json文档的具体类型。如果参数为NULL,则返回NULL。

    select json_type(user_info) from json_test where id>2;
    OBJECT
    ARRAY

(4)检查json是否正确   JSON_VALID(val)
判断val是否为有效的json格式,是为1,不是为0。如果参数为NUL,则返回NULL。

    select json_valid('{}'),json_valid('[]'),json_valid('true'),json_valid('1'),json_valid('"a"');

    1   1   1   1   1

    以下无效:
    select json_valid(''),json_valid('"'),json_valid(null),json_valid('a')

    0   0  NULL  0
    注意:json_valid('1') 有效,json_valid('a')无效。改成其他数字均有效。英语字母均无效。

    select json_valid('{"a":2}'),json_valid('{"a":false}'),json_valid('{"a":}'),json_valid('{"a":NULL}')

    1   1   0   0

    json格式错误。有key,无value或者NULL。反过来无key,有value也是。

    select json_valid('{:2}'),json_valid('{NULL:1}')

    0   0

 

标签:info,8.0,数据类型,JSON,json,user,NULL,select
From: https://www.cnblogs.com/rcsy/p/18407467

相关文章

  • MySQL数据类型
    MySQL支持SQL数据类型的几个类别:数字类型、日期和时间类型、字符串(字符和字节)类型、空间类型和JSON数据类型。==========================================数据类型 ==========================================MySQL支持所有标准的SQL数值数据类型。这些类型包括精确的数值......
  • Redis的五大数据类型
    Redis的五大数据类型目录一、String数据类型1.SET/GET/APPEND/STRLEN:2.INCR/DECR/INCRBY/DECRBY:3.GETSET:4.SETEX:5.SETNX:6.MSET/MGET/MSETNX:二、List数据类型1.LPUSH/LPUSHX/LRANGE:2.LPOP/LLEN:3.LREM/LSET/LINDEX/LTRIM:4.LINSERT:5.RPUSH/RPUSHX/RPOP/RPOPLPU......
  • MySQL表的操作与数据类型
    目录前言一、表的操作1.创建一个表2.查看表的结构3.修改表 4.删除一个表二、 MySQL的数据类型0.数据类型一览:1.整数类型2.位类型3.小数类型4.字符类型前言在MySQL库的操作一文中介绍了有关MySQL库的操作,本节要讲解的是由库管理的结构——表一、表的操......
  • RapidJSON 的坑--允许Object对象存在相同的key,且key为数字时序列化报异常
    RapidJSON的坑--允许Object对象存在相同的key,且key为数字时序列化报异常测试代码如下:1voidshow(rapidjson::Document&doc)2{3printf("-----------------foriterator\nMemberCount:%d\n",doc.MemberCount());4for(autoit=doc.MemberBegin();it!=doc......
  • 推荐一款好用的golang修复无效json的库:JSONRepair
    简单来说,就是可以将错误格式(非标准)的json修正,如下图所示: 我的json明显不是标准的json格式,是不能被解析的: 但是JSONRepair库也能修复。 github地址:https://github.com/kaptinlin/jsonrepair使用也非常简单:1、安装:gogetgithub.com/kaptinlin/jsonrepair2......
  • 【昌哥IT课堂】MySQL8.0新特性之不可见主键
     一、概述作为MySQLDBA,相信大家都经历过在复制模式下,如果没有主键,遇到loaddata,大事务,ddl等有大量表数据行扫描的行为时,会带来严重的主从延迟,给数据库稳定性和数据一致性带来隐患。MySQL8.0.30新版本为我们提供了一个新特性-(GeneratedInvisiblePrimaryKeys)简称GI......
  • C++的数据类型----标准库类型(std::vector容器/std::list容器/std::map容器)的实例讲解
    目录1.字符串(std::string):用于处理文本字符串。2.容器:如std::vector、std::list、std::map等,用于存储和管理数据集合2.1std::vector容器2.2std::list容器2.3std::map容器1.字符串(std::string):用于处理文本字符串。下面是一个C++中字符串的示例程序......
  • MySQL 8.0修改密码
    最近系统升级牵涉到MySQL升级,需要升级到MySQL8.0,涉及MySQL用户的密码修改,特地记录一下!MySQL8.0前修改密码在MySQL8.0前,执行:SETPASSWORD=PASSWORD('[新密码]')进行密码修改,在MySQL8.0后,以上的方法使用root用户修改别的用户密码是报错的,因为MySQL8.0后修改了修改密码的方......
  • 镜像:PHP 8.0、Apache 和 phpMyAdmin 的集成​
    一、引言背景随着云计算和容器技术的快速发展,Docker已经成为了现代应用开发和部署的重要工具。在本文中,我们将介绍如何使用Docker镜像来集成PHP8.0、Apache和phpMyAdmin,以构建一个高效、可靠的Web应用开发环境。通过使用Docker,我们可以轻松地管理应用的依赖关系,提高开发......
  • shell请求api,获取json返回值,做判断
    1.shell如何请求api,获取到json返回值:https://www.cnblogs.com/pingguomang/p/184050112.shell如果解析json数据: https://www.cnblogs.com/pingguomang/p/184049963.shell的条件判断:#定义用户的iduser_id="199348"#1.调用api--获取用户数据详细信息response=$(curl......