MySQL5.7中对JSON的支持

概述

从MySQL5.7.8开始，Mysql提供了一个原生的Json类型，Json值将不再以字符串的形式存储，而是采用一种允许快速读取文本元素（document elements）的内部二进制（internal binary）格式。在Json列插入或者更新的时候将会自动验证Json文本，未通过验证的文本将产生一个错误信息。Json文本采用标准的创建方式，可以使用大多数的比较操作符进行比较操作，例如：=, <, <=, >, >=, <>, != 和 <=>。

MySQL5.7.8 还提供了一些基于Json的函数，包括：

• 创建JSON: JSON_ARRAY(), JSON_MERGE() 和 JSON_OBJECT()
• 查询JSON: JSON_CONTAINS(), JSON_CONTAINS_PATH(), JSON_EXTRACT(), JSON_KEYS() 和 JSON_SEARCH()
• 修改JSON: JSON_APPEND(), JSON_ARRAY_APPEND(), JSON_ARRAY_INSERT(), JSON_INSERT(), JSON_QUOTE(), JSON_REMOVE(), JSON_REPLACE(), JSON_SET() 和 JSON_UNQUOTE()
• META信息: JSON_DEPTH(), JSON_LENGTH(), JSON_TYPE() 和 JSON_VALID()

在5.7.9及之后的版本可以使用column->path作为JSON_EXTRACT(column, path)的快捷方式。这个函数可以作为列数据的别名出现在SQL语句中的任意位置，包括WHERE,ORDER BY,和GROUP BY语句。同样包含SELECT， UPDATE， DELETE，CREATE TABLE和其他SQL语句。->左边的参数为JSON数据的列名而不是一个表达式，其右边参数JSON数据中的某个路径表达式

MySQL中的JSON类型

在MySQL5.7.8之后开始支持一种可高效获取JSON文本中数据的原生JSON类型，该类型具有以下优点：

• 存储时自动验证，验证不通过将报错
• 更好的存储结构。采用一种针对告诉读取JSON文本元素的内部结构进行存储。新的服务器是以二进制的格式读取一个JSON文本，而不是以字符串格式读取然后进行转换。这种二进制格式允许服务器在文本中通过键或者数组索引获取其子对象（嵌套数组）而不需要读取全部值。

另外，系统对JSON格式做了一些限制：

• JSON文本的最大长度取决有系统常量：max_allowed_packet。该值仅在服务器进行存储的时候进行限制，在内存中进行计算的时候是允许超过该值的。
• JSON列不可有默认值
• JSON列与其他二进制类型列一样是无法创建索引。但是可以从JSON列中所存储的文本中某些表列值进行创建索引。MySQL最优控制器同样在通过JSON表达创建的索引中进行查询。

MySQL集群的NDB 7.5.2开始支持JSON列和JSON函数，包括为无法创建索引的JSON列通过JSON表达式生成伪列的方式创建索引。每个NDB表最多支持三个JSON列。

MySQL中的JSON函数

创建JSON

• JSON_ARRAY()
  
  • 语法：
    
JSON_ARRAY([val[, val] ...])

  • 示例：
    
mysql> SELECT JSON_ARRAY(1, "abc", NULL, TRUE, CURTIME());
+---------------------------------------------+
| JSON_ARRAY(1, "abc", NULL, TRUE, CURTIME()) |
+---------------------------------------------+
| [1, "abc", null, true, "11:30:24.000000"] |
+---------------------------------------------+

• JSON_MERGE()
  
  • 语法：
    
JSON_MERGE(json_doc, json_doc[, json_doc] ...)

  • 说明：
    
    • 合并两个或者多个Json文本然后返回一个合并集。如果某些参数为NULL,则返回NULL;如果参数中包含不合法的JSON文本将报错。
    • 合并操作遵循以下约定：
      
      • 多个数组相邻合并为单个数组
      • 多个对象相邻合并为单个对象
      • 标量自动包装为数组，并进行合并
      • 对象和数组相邻则将对象自动包装为数组，然后合并两个数组
  • 示例：
    
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}] |
+------------------------------------+

• JSON_OBJECT()
  
  • 语法：
    
JSON_OBJECT([key, val[, key, val] ...])

  • 示例：
    
mysql> SELECT JSON_OBJECT('id', 87, 'name', 'carrot');
+-----------------------------------------+
| JSON_OBJECT('id', 87, 'name', 'carrot') |
+-----------------------------------------+
| {"id": 87, "name": "carrot"} |
+-----------------------------------------+


查询JSON

• JSON_CONTAINS()
  
  • 语法：
    
JSON_CONTAINS(json_doc, val[, path])

  • 说明：
    
    • 返回0或者1来表示目标JSON文本中是否包含特定值，或者JSON文本的指定路径下是否包含特定值。
    • 以下情况将返回NULL:
      
      • 目标JSON文本或者特定值为NULl
      • 指定路径非目标JSON文本下的路径
    • 以下情况将报错：
      
      • 目标JSON文本不合法
      • 指定路径不合法
      • 包含* 或者 ** 匹配符
    • 若仅检查路径是否存在，使用JSON_CONTAINS_PATH()代替
    • 这个函数中做了以下约定：
      
      • 当且仅当两个标量可比较而且相等时，约定目标表标量中包含候选标量。两个标量的JSON_TYPE()值相同时约定他们是可比较的，另外类型分别为INTEGER和DECEMAL的两个标量也是可比较的
      • 当且仅当目标数组中包含所有的候选数组元素，约定目标数组包含候选数组
      • 当且仅当目标数组中某些元素包含空数组，约定目标数组包含空数组
      • 当且仅当候选对象中所有的键值都能在目标对象中找到相同名称的键而且候选键值被目标键值包含，约定目标对象包含候选对象
      • 其他的情况均为目标文本不包含候选文本
  • 示例：
    
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 |
+-------------------------------+

• JSON_CONTAINS_PATH()
  
  • 语法：
    
JSON_CONTAINS_PATH(json_doc, one_or_all, path[, path] ...)

  • 说明：
    
    • 返回0或者1表示JSON文本的指定的某个路径或者某些路径下是否包含特定值。
    • 当某些参数为NULL是否返回NULL
    • 以下情况将报错：
      
      • 参数json_doc为不合法JSON文本
      • path参数中包含不合法的路径
      • one_or_all参数为非’one’或者’all’的值
    • 检测某个路径中是否包含某个特定值，使用 JSON_CONTAINS()代替
    • 目标文本中如果没有指定的路径，则返回0。否则，返回值依赖于one_or_all值：
      
      • ’one’: 文本中存在至少一个指定路径则返回1，否则返回0
      • ‘all’: 文本中包含所有指定路径则返回1， 否则返回0
  • 示例：
    
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 |
+----------------------------------------+

• JSON_EXTRACT()
  
  • 语法：
    
JSON_EXTRACT(json_doc, path[, path] ...)

  • 说明：
    
    • 返回json_doc中与path参数相匹配的数据。当有参数为NULl或者文本中未找到指定path时将返回NULL。当参数不合法时将报错。
    • 返回结果包含所有与path匹配的值。如果返回多个值，则将自动包装为数组，其顺序为匹配顺序；相反则返回单个匹配值。
    • MySQL5.7.9及之后的版本将支持’->’操作符作为本函数两个参数时的便捷写法。->左边的参数为JSON数据的列名而不是一个表达式，其右边参数JSON数据中的某个路径表达式。详细使用方法将在文末详细阐述。
  • 示例：
    
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] |
+-----------------------------------------------+

• JSON_KEYS()
  
  • 语法：
    
JSON_KEYS(json_doc[, path])

  • 说明：
    
    • 返回JSON对象的顶层目录的所有key值或者path指定路径下的顶层目录的所有key所组成的JSON数组。
    • 以下情况返回NULL
      
      • 必填参数为NULL
      • json_doc非对象（为数组等）
      • 当给定path，但是在JSON中未找到
    • 以下情况报错
      
      • json_doc为不合法的JSON文本
      • path为不合法的路径表达
      • 包含 * 或者 ** 通配符
    • 当目标对象为空时，返回值为空。返回结果不包含顶层目录下的嵌套的目录中的key
  • 示例：
    
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"] |
+----------------------------------------------+

• JSON_SEARCH()
  
  • 语法：
    
JSON_SEARCH(json_doc, one_or_all, search_str[, escape_char[, path] ...])

  • 说明：
    
    • 返回JSON包含指定字符串的路径。
    • 以下情况将返回NULL
      
      • json_doc, search_str 或path 为NULL
      • 文本中不包含path
      • search_str未找到
    • 以下情况将报错
      
      • json_doc不合法
      • path不合法
      • one_or_all 不是one 或者all
      • escape_char 不是一个常量表达式
    • one_or_all的作用
      
      • ’one’:当查找操作找到第一个匹配对象，并将结果路径返回后就停止查找。
      • ‘all’:将返回所有的匹配结果路径，结果中不包含重复路径。如果返回结果集中包含多个字符串，将自动封装为一个数组，元素的排序顺序无意义。
    • 在search_str中，通配符’%’和’‘可以如同LIKE操作上一样运行。’%’可以匹配多个字符（包括0个字符），’‘则仅可匹配一个字符。
    • ‘%’或’_’作为特殊字符出现时，需要使用转义字符进行转义。当escape_char参数为NULL或者不存在的情况下，系统默认使用’\’作为转义字符。escape_char参数必须要常量（为空或者一个字符）
    • 对于通配符的处理上与LIKE操作不同之处在于，JSON_SEARCH()中的通配符在编译时的计算结果必须要是常量，而不像LIKE仅需在执行时为常量。例如：在prepared Statement中使用JSON_SEARCH(), escape_char参数使用’?’作为参数，那么这个参数在执行时可能是常量，而不是在编译的时候。（这句话自己也没怎么懂，想了很久没想到该怎么翻译）
  • 示例：
    
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" |
+-------------------------------------------+


修改JSON

• JSON_APPEND()
  
  • 在5.7.9中重命名为JSON_ARRAY_APPEND()

• JSON_ARRAY_APPEND()

  • 语法：

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

  • 说明：

    • 在指定的数组末尾以JSON文本形式追加指定的值并返回。当参数中包含NULL时，返回NULL。
    • 以下情况将报错
      
      • json_doc不合法
      • path 不合法
      • 包含* 或者 ** 通配符
    • 键值对采用自左到右的顺序进行追加。追加一对键值后的新值将成为下一对键值追加的目标。
    • 如果指定目录下为标量或者对象值，则会被封装为数组，然后将新的值加入到数组中。对于不包含任何值得键值对将直接忽略。
  • 示例：
    

    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"]                 |
    +---------------------------------+

• JSON_ARRAY_INSERT()
  
  • 语法：
    
JSON_ARRAY_INSERT(json_doc, path, val[, path, val] ...)

  • 说明：
    
    • 更新一个JSON文本，向文本中插入一个数组，然后返回修改后的文本。如果参数为NULL，则返回NULL。
    • 以下情况报错
      
      • json_doc参数不合法
      • path不合法
      • 包含 * 或者 ** 通配符
      • 不是以数组标示结尾
    • 键值对采用自左到右的顺序进行插入，插入一对后的新值将作为下一对插入的目标。
    • 对于不包含任何值得键值对将直接忽略。如果path指定的是一个数组元素，则其对应的值将插入到该元素右边的任意位置；如果path指定的是数组的末尾，则其值将插入到该数组末尾。
    • 执行插入操作后，其元素位置将发生变化，也将影响后续插入数据的位置定义。如最后的示例中，第二个插入数据并未出现数数据库中，是因为第一次插入操作后，原语句中定义的位置在新数据中未找到指定的元素，从而被忽略。
  • 示例：
    
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]] |
+----------------------------------------------------+

• JSON_INSERT()
  
  • 语法：
    
JSON_INSERT(json_doc, path, val[, path, val] ...)

  • 说明：
    
    • 将JSON文本中插入输入并返回。。如果参数为NULL，则返回NULL。
    • 以下情况报错
      
      • json_doc参数不合法
      • path不合法
      • 包含 * 或者 ** 通配符
      • 不是以数组标示结尾
    • 键值对采用自左到右的顺序进行插入，插入一对后的新值将作为下一对插入的目标。
    • 对于原文本中已存在的键值，采取跳过而不覆盖的策略。相反，原文本中不存在的path,如果其标志为以下两种类型，则会将val添加到document中。
      
      • 原对象中不存在的成员变量，将该path及其关联的值添加到document中
      • 原对象中已有的数组末尾，将使用新值对该数组进行扩展。如果当前要插入的数据非数组，则自动封装为数组再进行扩展
    • 其他情况文本中不存在的path将被忽略。
  • 示例：
    
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_REMOVE()
  
  • 语法：
    
JSON_REMOVE(json_doc, path[, path] ...)

  • 说明：
    
    • 从JSON文本中删除数据。如果任意参数为NULL，则返回NULL。
    • 以下情况报错
      
      • json_doc参数不合法
      • path不合法
      • 包含 * 或者 ** 通配符
    • path参数对采用自左到右的顺序进行删除，删除一个path后的新值将作为下个path删除的目标。
    • 删除本来就不存在的路径不会报错，只是对原文本无任何影响而已。
  • 示例：
    
mysql> SET @j = '["a", ["b", "c"], "d"]';
mysql> SELECT JSON_REMOVE(@j, '$[1]');
+-------------------------+
| JSON_REMOVE(@j, '$[1]') |
+-------------------------+
| ["a", "d"] |
+-------------------------+

• JSON_REPLACE()
  
  • 语法：
    
JSON_REPLACE(json_doc, path, val[, path, val] ...)

  • 说明：
    
    • 替换JSON文本中已经存在的值并返回。如果任意参数为NULL，则返回NULL。
    • 以下情况报错
      
      • json_doc参数不合法
      • path不合法
      • 包含 * 或者 ** 通配符
    • 键值对采用自左到右的顺序进行替换，替换一对后的新值将作为下一对替换的目标。
    • 对于原文本中已存在的path，使用新值进行覆盖；对于元文本中不存在的path，将忽略该次替换。
  • 示例：
    
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_SET(json_doc, path, val[, path, val] ...)

  • 说明：
    
    • 向JSON文本中插入或更新数据并返回。如果任意参数为NULL，则返回NULL。
    • 以下情况报错
      
      • json_doc参数不合法
      • path不合法
      • 包含 * 或者 ** 通配符
    • 键值对采用自左到右的顺序进行更新，更新一对后的新值将作为下一对更新的目标。
    • 对于原文本中已存在的键值，进行覆盖。另外，原文本中不存在的path,如果其标志为以下两种类型，则会将val添加到document中。
      
      • 原对象中不存在的成员变量，将该path及其关联的值添加到document中
      • 原对象中已有的数组末尾，将使用新值对该数组进行扩展。如果当前要插入的数据非数组，则自动封装为数组再进行扩展
    • 其他情况文本中不存在的path将被忽略。
    • JSON_INSERT(), JSON_REPLACE(), 和 JSON_SET()的比较
      
      • JSON_INSERT() 插入新值，不覆盖原有值
      • JSON_REPLACE() 不插入新值，覆盖原有值
      • JSON_SET() 插入新值，并覆盖原有值
    • 以下示例可以展示以上区别
  • 示例：
    
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]} |
+-----------------------------------------------------+

• JSON_QUOTE()
  
  • 语法：
    
JSON_QUOTE(json_val)

  • 说明：
    
    • 通过为字符串增加双引号和为引号增加转义字符将字符串包装为JSON值，然后返回一个uft8mb4字符串。如果参数为NULL，则返回NULL。
    • 这个函数通常用来为JSON文本生成合法的JSON字符串
  • 示例：
    
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]" |
+-------------------------+


• JSON_UNQUOTE()

  • 语法：

    JSON_UNQUOTE(val)

  • 说明：

    • 去除JSON值得引号并返回uft8mb4字符串。如果参数为NULL，则返回NULL。当参数值以双引号开始和结尾，但是却不是合法的JSON字符串时将报错。
    • 在字符串中存在一些具有特殊意义的字符序列将被特殊处理（开启NO_BACKSLASH_ESCAPES模式即可不进行特殊处理）。这些字符序列都是以’\’开始，叫做转移字符序列。MySQL中支持的序列有：

    转移字符序列	含义
    \”	双引号（”）
    \b	空格
    \f	换页
    \n	换行
    \r	光标回退
    \t	制表符
    \\	斜线字符（\）
    \uXXXX	UTF-8的Unicode码

    • 对于其他的转义字符序列，则自动忽略转义字符’\’，其值就是转义字符之后的字符串。
    • 同时转义字符处理大小写敏感，如：\b将转义为空格，而\B则转移为B

  • 示例：

    • 简单示例

      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]        |
      +-----------+------------------+

    • 转义字符处理

      mysql> SELECT @@sql_mode;
      +------------+
      | @@sql_mode |
      +------------+
      |            |
      +------------+
      mysql> SELECT JSON_UNQUOTE('"\\t\\u0032"');
      +------------------------------+
      | JSON_UNQUOTE('"\\t\\u0032"') |
      +------------------------------+
      | 2                            |
      +------------------------------+
      mysql> SET @@sql_mode = 'NO_BACKSLASH_ESCAPES';
      mysql> SELECT JSON_UNQUOTE('"\\t\\u0032"');
      +------------------------------+
      | JSON_UNQUOTE('"\\t\\u0032"') |
      +------------------------------+
      | \t\u0032                     |
      +------------------------------+
      mysql> SELECT JSON_UNQUOTE('"\t\u0032"');
      +----------------------------+
      | JSON_UNQUOTE('"\t\u0032"') |
      +----------------------------+
      | 2                          |
      +----------------------------+

META信息

• JSON_DEPTH()
  
  • 语法：
    
JSON_DEPTH(json_doc)

  • 说明：
    
    • 返回JSON文本的最大深度。参数为NULL时返回NULL。参数非合法JSON文本时报错。
    • 空数组，空对象，标量的长度为1。空数组包含一个深度为1的元素，或者空对象包含一个深度为1的成员，则其深度为2。其他情况认为文本深度大于2。
  • 示例：
    
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 |
+-------------------------------+

• JSON_LENGTH()
  
  • 语法：
    
JSON_LENGTH(json_doc[, path])

  • 说明：
    
    • 返回JSON文本或其指定路径下子文本长度。。如果任意参数为NULL，则返回NULL。
    • 以下情况报错
      
      • json_doc参数不合法
      • path不合法
      • 包含 * 或者 ** 通配符
    • 关于文本长度做了一下约定：
      
      • 标量长度为 1
      • 数组长度为数组元素个数
      • 对象长度为成员变量个数
      • 长度不包含子文本长度
  • 示例：
    
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 |
+------------------------------------------------+


• JSON_TYPE()

  • 语法：

    JSON_TYPE(json_val)

  • 说明：

    • 返回JSON值得类型字符串
    • 当参数为NULL时返回NULL
    • 当参数为不合法JSON时报错

    • JSON类型对照

      • 纯JSON类型

      JSON TYPE	REMARK
      OBJECT	JSON对象
      ARRAY	JSON数组
      BOOLEAN	JSON true和false字符串
      NULL	JSON NULL字符串

      • 数字类型

      JSON TYPE	REMARK
      INTEGER	MySQL中 TINYINT, SMALLINT, MEDIUMINT, INT 和 BIGINT 标量
      DOUBLE	MySQL中 DOUBLE FLOAT 标量
      DECIMAL	MySQL中 DECIMAL 和 NUMERIC 标量

      • 时间类型

      JSON TYPE	REMARK
      DATETIME	MySQL中 DATETIME 和 TIMESTAMP 标量
      DATE	MySQL中 DATE 标量
      TIME	MySQL中 TIME 标量

      • 字符串类型

      JSON TYPE	REMARK
      STRING	MySQL字符串标量: CHAR, VARCHAR, TEXT, ENUM, 和 SET

      • 二进制类型

      JSON TYPE	REMARK
      BLOB	MySQL 二进制标量: BINARY, VARBINARY, BLOB
      BIT	MySQL中 BIT 标量

      • 其他类型

      JSON TYPE	REMARK
      OPAQUE	(raw bits)

  • 示例：
    

    mysql> SET @j = '{"a": [10, true]}';
    mysql> SELECT JSON_TYPE(@j);
    +---------------+
    | JSON_TYPE(@j) |
    +---------------+
    | OBJECT        |
    +---------------+
    mysql> SELECT JSON_TYPE(JSON_EXTRACT(@j, '$.a'));
    +------------------------------------+
    | JSON_TYPE(JSON_EXTRACT(@j, '$.a')) |
    +------------------------------------+
    | ARRAY                              |
    +------------------------------------+
    mysql> SELECT JSON_TYPE(JSON_EXTRACT(@j, '$.a[0]'));
    +---------------------------------------+
    | JSON_TYPE(JSON_EXTRACT(@j, '$.a[0]')) |
    +---------------------------------------+
    | INTEGER                               |
    +---------------------------------------+
    mysql> SELECT JSON_TYPE(JSON_EXTRACT(@j, '$.a[1]'));
    +---------------------------------------+
    | JSON_TYPE(JSON_EXTRACT(@j, '$.a[1]')) |
    +---------------------------------------+
    | BOOLEAN                               |
    +---------------------------------------+
    mysql> SELECT JSON_TYPE(NULL);
    +-----------------+
    | JSON_TYPE(NULL) |
    +-----------------+
    | NULL            |
    +-----------------+
    mysql> SELECT JSON_TYPE(1);
    ERROR 3146 (22032): Invalid data type for JSON data in argument 1
    to function json_type; a JSON string or JSON type is required.

• JSON_VALID()
  
  • 语法：
    
JSON_VALID(val)

  • 说明：
    
    • 返回0或1表示参数中val是否为合法的JSON文本。当参数为NULL的时候返回NULL
  • 示例：
    
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 |
+---------------------+-----------------------+


-> 和->>操作符的使用

-> 操作符

在5.7.9之后用于快捷执行JSON_EXTRACT()只有两个参数时的功能。左边参数为JSON列名，右边的参数为该列中的路径。

SELECT中的使用

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)

WHERE中使用

mysql> ALTER TABLE jemp ADD COLUMN n INT;
Query OK, 0 rows affected (0.68 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> UPDATE jemp SET n=1 WHERE c->"$.id" = "4";
Query OK, 1 row affected (0.04 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> SELECT c, c->"$.id", g, n
> FROM jemp
> WHERE JSON_EXTRACT(c, "$.id") > 1
> ORDER BY c->"$.name";
+-------------------------------+-----------+------+------+
| c                             | c->"$.id" | g    | n    |
+-------------------------------+-----------+------+------+
| {"id": "3", "name": "Barney"} | "3"       | 3    | NULL |
| {"id": "4", "name": "Betty"}  | "4"       | 4    | 1    |
| {"id": "2", "name": "Wilma"}  | "2"       | 2    | NULL |
+-------------------------------+-----------+------+------+
3 rows in set (0.00 sec)

mysql> DELETE FROM jemp WHERE c->"$.id" = "4";
Query OK, 1 row affected (0.04 sec)

mysql> SELECT c, c->"$.id", g, n
> FROM jemp
> WHERE JSON_EXTRACT(c, "$.id") > 1
> ORDER BY c->"$.name";
+-------------------------------+-----------+------+------+
| c                             | c->"$.id" | g    | n    |
+-------------------------------+-----------+------+------+
| {"id": "3", "name": "Barney"} | "3"       | 3    | NULL |
| {"id": "2", "name": "Wilma"}  | "2"       | 2    | NULL |
+-------------------------------+-----------+------+------+
2 rows in set (0.00 sec)

此函数同样适用于JSON数组

mysql> CREATE TABLE tj10 (a JSON, b INT);
Query OK, 0 rows affected (0.26 sec)
mysql> INSERT INTO tj10
> VALUES ("[3,10,5,17,44]", 33), ("[3,10,5,17,[22,44,66]]", 0);
Query OK, 1 row affected (0.04 sec)
mysql> SELECT a->"$[4]" FROM tj10;
+--------------+
| a->"$[4]"    |
+--------------+
| 44           |
| [22, 44, 66] |
+--------------+
2 rows in set (0.00 sec)
mysql> SELECT * FROM tj10 WHERE a->"$[0]" = 3;
+------------------------------+------+
| a                            | b    |
+------------------------------+------+
| [3, 10, 5, 17, 44]           | 33   |
| [3, 10, 5, 17, [22, 44, 66]] | 0    |
+------------------------------+------+
2 rows in set (0.00 sec)

内嵌数组同样适用。如果在目标JSON文本中未找到匹配对象时返回NULL

mysql> SELECT * FROM tj10 WHERE a->"$[4][1]" IS NOT NULL;
+------------------------------+------+
| a                            | b    |
+------------------------------+------+
| [3, 10, 5, 17, [22, 44, 66]] | 0    |
+------------------------------+------+
mysql> SELECT a->"$[4][1]" FROM tj10;
+--------------+
| a->"$[4][1]" |
+--------------+
| NULL         |
| 44           |
+--------------+
2 rows in set (0.00 sec)
## 适用JSON_EXTRACT()实现
mysql> SELECT JSON_EXTRACT(a, "$[4][1]") FROM tj10;
+----------------------------+
| JSON_EXTRACT(a, "$[4][1]") |
+----------------------------+
| NULL |
| 44 |
+----------------------------+
2 rows in set (0.00 sec)

->> 操作符

这个操作符是对->的一个提升，从5.7.13开始支持。该操作符在->的基础上增加了JSON_UNQUOTE()操作。以下语句作用相同：
- JSON_UNQUOTE( JSON_EXTRACT(column, path) )
- JSON_UNQUOTE(column -> path)
- column->>path