mySQL中的JSON操作

关于JSON值

• json数组可以包含数字、字符、布尔值等，以[]组合起来

[10,null,"a",true]

• json对象的健值表现形式以键值对表示，且值可以是字符型或数字、json空文本或json布尔值，但键必须是字符型，以{}组合起来

{"key1":"value1","key2":8}

• json还允许使用时间（date``time``datetime``timestamp）等

["15:51:29.000000", "2019-03-20", "2019-043-20 15:51:29.000000"]

• 在json数组和对象中允许嵌套

["ac",{"key1":"value1"}]
{"key2":10,[null,true]}

mySQL中的创建JSON值的函数

JSON_ARRAY([value[,value]…)

• 创建json数组，并返回该数组

• 可以为null

SELECT JSON_ARRAY(9,null,true,"I am Dolores",now());
//output
[9, null, true, "I am Dolores", "2019-03-20 16:05:42.000000"]

JSON_ARRAYAGG(value)

创建单个json数组

 SELECT JSON_ARRAYAGG(1);//[1]
 SELECT JSON_ARRAYAGG(null);//[null]
 SELECT JSON_ARRAYAGG(true);//[true]
 SELECT JSON_ARRAYAGG(now());//["2019-03-20 16:31:20.000000"]

JSON_OBJECT([key,value[,key,value]…)

• 创建json对象，并返回该键值对

• 如果任何键名称为null或参数个数为奇数，则发生错误

SELECT json_object('name','Dolores','id','2015214328');
//output
{"id": "2015214328", "name": "Dolores"}

JSON_OBJECTAGG(key,value)

创建单个对象，并返回该对象

SELECT json_objectagg('ID',22);
//{"ID": 22}

JSON_QUOTE(string)

• 将字符串作为json值引用，方法是用双引号字符包装字符串，并转义内部引号和其他字符，然后将结果以utf8bm4字符串返回

• 如果参数为null，则返回null

SELECT JSON_QUOTE('null');
//output
"null"

SELECT JSON_QUOTE('"null"');
//output
"\"null\""

SELECT JSON_QUOTE('I \'m Dolores');
//output
"I 'm Dolores"

SELECT JSON_QUOTE('[1,2,3]');
//output
"[1,2,3]"

CAST(value AS JSON)

将其他类型的值转换成JSON类型来获取json值

SELECT CAST('null' AS JSON)
//"null"

判断JSON

JSON_TYPE()

将json值作为参数传入，如果值有效，则返回其json类型，否则报错

SELECT JSON_TYPE('[1,"Dolores",true]');
//ARRAY

SELECT JSON_TYPE('"hello"');
//STRING

SELECT JSON_TYPE('{"key":"value"}');
//OBJECT

SELECT JSON_TYPE('hello');
//Error Code: 3141. Invalid JSON text in argument 1 to function json_type: "Invalid value." at position 0.  0.000 sec

合并JSON

JSON_MERAGE_PRESERVE()

将两个或多个json值合并为一个json并返回最终值

SELECT JSON_MERGE_PRESERVE('["ABC","HELLO"]','{"name":"Dolores"}');
//["ABC", "HELLO", {"name": "Dolores"}]

JSON_MERGE_PATCH()

合并两个或多个json值，但不合并重复键的值，如果出现重复键，仅保留最后一个的值

SELECT JSON_MERGE_PATCH('[1, 2]', '[true, false]');
//[true, false]     

SELECT JSON_MERGE_PATCH('{"name": "x"}', '{"id": 47}');
//{"id": 47, "name": "x"}

SELECT JSON_MERGE_PATCH('{"a":{"x":1}}', '{"a":{"y":2}}');
// {"a": {"x": 1, "y": 2}}

两者的比较

• 在遇到重复键时，json_merge_patch仅保留最后一个值，

• json_merge_perserve会在原有值上添加值

SET @x = '{ "a": 1, "b": 2 }',
@y = '{ "a": 3, "c": 4 }',
@z = '{ "a": 5, "d": 6 }';

SELECT  JSON_MERGE_PATCH(@x, @y, @z)  AS `Patch`,
JSON_MERGE_PRESERVE(@x, @y, @z) AS `Preserve`
//Patch: {"a": 5, "b": 2, "c": 4, "d": 6}
//Preserve: {"a": [1, 3, 5], "b": 2, "c": 4, "d": 6}

mysql中的json是否区分大小写

经过函数转换得到的json是区分大小写的，原因在于转换后的字符集格式为utf8mb4和utf8mb4_bin,因为utf8mb4_bin是二进制排序规则，所以区分大小写

SET @j = JSON_OBJECT('key', 'value');
SELECT CHARSET(@j), COLLATION(@j);
//utf8mb4     utf8mb4_bin   

SELECT JSON_ARRAY('a') = JSON_ARRAY('A');
//0
(0为false的意思)

因为区分大小写，所以json中的null、 true和false都必须用小写字母编写

SELECT JSON_VALID('null'), JSON_VALID('Null'), JSON_VALID('NULL');
//1 0 0 

SELECT JSON_VALID('true'), JSON_VALID('True'), JSON_VALID('TRUE');
/1 0 0 

需要存储转义字符时

直接插入键值对语句和用JSON_OBJECT转换成json值存入的差别在于，前者需要双反斜杠转义字符，而后者只需要单反斜杠转义字符

CREATE TABLE facts (sentence JSON);

当需要存储的内容如下

mascot: The MySQL mascot is a dolphin named "Sakila".

使用直接插入的方法时：

INSERT INTO facts VALUES
('{"mascot": "Our mascot is a dolphin named \\"Sakila\\"."}');

SELECT sentence FROM facts;
// {"mascot": "Our mascot is a dolphin named \"Sakila\"."}

使用JSON_OBJECT时

 INSERT INTO facts VALUES
(JSON_OBJECT("mascot", "Our mascot is a dolphin named \"Sakila\"."));

SELECT sentence FROM facts;
//{"mascot": "Our mascot is a dolphin named \"Sakila\"."}

搜索和修改json---json路径表达式

json路径表达式用于精确查找json中的值

• 路径语法使用前导$来表示正在考虑的 json 文档，后跟选择器

• 选择器的语法为：

  • .后跟一个具体的键名，如'$.name',当有空格是必须加上双引号如'$."my name"'

   SELECT JSON_EXTRACT('{"id": 14, "name": "Aztalan"}', '$.name');
   //"Aztalan" ​    
  

  • 使用[N], 该路径命名数组中位置 N 的值。数组位置是以零开头的整数。如果path未选择数组值, path 的计算结果与path[0]相同:

  //JSON_SET是一个具备修改功能的函数
  SELECT JSON_SET('"x"', '$[0]', 'a');
   //"a"
  SELECT JSON_SET('"x"', '$[1]', 'a','$[2]','B');
  //["x", "a", "B"]
  

  • [M to N]指定数组值的子集或范围, 从位置 M的值开始, 并以位置 N 的值结束.

    SELECT JSON_EXTRACT('[1, 2, 3, 4, 5]', '$[1 to 3]');
    //[2, 3, 4]    
    

    • 可以配合last使用

       SELECT JSON_EXTRACT('[1, 2, 3, 4, 5]', '$[last-3 to last-1]');
      //[2, 3, 4]  
      

       SELECT JSON_REPLACE('"Sakila"', '$[last]', 10);
       //10
      

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

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

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

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

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

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

    如路径$**.b计算为多个路径 ($.a.b . b 和$.c.b), 并生成匹配路径值的数组

     SELECT JSON_EXTRACT('{"a": {"b": 1}, "c": {"b": 2}}', '$**.b');
     // [1, 2] 
    

• 文档中不存在的路径 (计算结果为不存在的数据) 的计算结果为NULL.

案例

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

• $[0]的计算结果3.
• $[1]的计算结果为{"a": [5, 6], "b": 10}.
• $[2]的计算结果为[99, 100].
• $[3]的计算结果为NULL (它指的是第四个数组元素, 它不存在)。

因为$[1]和$[2]计算为非标量值, 所以它们可以用作选择嵌套值的更具体的路径表达式的基础。例子：

• $[1].a的计算结果为[5, 6].
• $[1].a[1]的计算结果6.
• $[1].b计算结果10.
• $[2][0]结果为99.

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

• $."a fish"的结果是shark.
• $."a bird"的结果是sparrow.

结合JSON_SET``JSON_INSERT``JSON_REPLACE``JSON_REMOVE的使用

SET @j = '["a", {"b": [true, false]}, [10, 20]]';

JSON_SET替换存在的路径的值, 并为不存在的路径添加值

SELECT JSON_SET(@j, '$[1].b[0]', 1, '$[2][2]', 2);
// ["a", {"b": [1, false]}, [10, 20, 2]]

JSON_INSERT添加新值, 但不替换现有值:

SELECT JSON_INSERT(@j, '$[1].b[0]', 1, '$[2][2]', 2);
// ["a", {"b": [true, false]}, [10, 20, 2]] 

JSON_REPLACE替换现有值并忽略新值:

 SELECT JSON_REPLACE(@j, '$[1].b[0]', 1, '$[2][2]', 2);
 // ["a", {"b": [1, false]}, [10, 20]]  

JSON_REMOVE使用一个或多个路径, 这些路径指定要从文档中删除的值。返回值是原始文档减去由文档中存在的路径选择的值:

SELECT JSON_REMOVE(@j, '$[2]', '$[1].b[1]', '$[1].b[1]');
//["a", {"b": [true]}]