mysql5.7以上提供了一种新的字段格式json，大概是mysql想把非关系型和关系型数据库一口通吃，所以推出了这种非常好用的格式，这样，我们的很多基于mongoDb或者clickHouse的业务都可以用mysql去实现了。当然了，5.7的版本只是最基础的版本，对于海量数据的效率是远远不够的，不过这些都在mysql8.0解决了。今天我们就针对mysql的json数据格式操作做一个简单的介绍

JSON 数组包含由逗号分隔并包含在[和] 字符中的值列表：
["abc", 10, null, true, false]
一个 JSON 对象包含一组由逗号分隔并包含在{和 }字符中的键值对：
{"k1": "value", "k2": 10}

一、创建 JSON 值的函数
JSON_ARRAY([val[, val] …])
评估（可能为空）值列表并返回包含这些值的 JSON 数组。

JSON_OBJECT([key, val[, key, val] …])
评估键值对（可能为空）列表并返回包含这些对的 JSON 对象。如果任何键名称是NULL或参数数量是奇数，则会发生错误。

JSON_QUOTE(string)
通过用双引号字符包裹字符串并转义内部引号和其他字符，将utf8mb4字符串引用为 JSON 值，然后将结果作为字符串返回 。NULL如果参数为 ，则 返回 NULL。

二、搜索 JSON 值的函数
JSON_CONTAINS(target, candidate[, path])
判断是否包含某个json值

SELECT * FROM json where JSON_CONTAINS(jsonData,'{"user_name":"tom"}');

JSON_CONTAINS_PATH(json_doc, one_or_all, path[, path] …)
判断某个路径下是否包json值

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

SELECT id,jsonData,JSON_CONTAINS_PATH(jsonData, 'one', '$.user_name') as json FROM json;

SELECT JSON_CONTAINS_PATH(jsonData, 'one', '$.bbs') as json FROM json;

json_extract（提取json值）的简洁写法
column->path

Id        jsonData
1	   [{"a": [5, 6], "b": 10}]
2	   {"age": "23", "sex": "1", "user_name": "tom"}
3	   [{"user_name": "bob"}, {"user_name": "jack"}]

SELECT JSON_EXTRACT(jsonData, '$.user_name') json_extract FROM json where id = 2;

在 MySQL 5.7.9 及更高版本中，当与两个参数一起使用时， -> 运算符用作JSON_EXTRACT()函数的别名， 左侧的列标识符和右侧的 JSON 路径（字符串文字）根据 JSON 文档（列值）。

SELECT jsonData -> '$.user_name' json_extract FROM json where id = 2;

SELECT jsonData -> '$[0].user_name' json_extract FROM json where id = 3;

json_unquote的简洁写法:
column->>path

去除json字符串的引号，将值转成string类型

SELECT jsonData ->> ‘$.user_name’ json_unquote FROM json;

JSON_KEYS(json_doc[, path])提取json中的键值为json数组

SELECT JSON_KEYS(jsonData) jsonKey FROM json;

SELECT JSON_KEYS(jsonData -> ‘$[0]’) jsonKey2 FROM json;

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下查。

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

SELECT JSON_SEARCH(@j, 'all', '10') jsonSearch;

SELECT JSON_SEARCH(jsonData, 'one', '23') as jsonSearch FROM json;

三、修改 JSON 值的函数
JSON_APPEND(json_doc, path, val[, path, val] …)将值附加到 JSON 文档中指定数组的末尾并返回结果
json_append 废弃，MySQL 5.7.9开始改名为json_array_append

JSON_ARRAY_APPEND(json_doc, path, val[, path, val] …)
末尾添加数组元素，如果原有值是数值或json对象，则转成数组后，再添加元素

SELECT JSON_ARRAY_APPEND(jsonData, '$[0]', '1') as newJson  FROM json  where id = 1;

SELECT JSON_ARRAY_APPEND(jsonData, '$[1]', '1') as newJson  FROM json  where id = 1;

JSON_ARRAY_INSERT(json_doc, path, val[, path, val] …)
在指定索引插入字符并返回对应的字符

SELECT JSON_ARRAY_INSERT(jsonData, ‘$[0]’, ‘2’) jsonArray FROM json where id = 1;

JSON_INSERT(json_doc, path, val[, path, val] …)
插入值（插入新值，但不替换已经存在的旧值）

SELECT JSON_INSERT(jsonData,'$[1].a','996','$[10]','mysql-json') jsonInsert FROM json where id = 1;

JSON_MERGE(json_doc, json_doc[, json_doc] …)
合并json数组或对象

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

JSON_REMOVE(json_doc, path[, path] …)
从 JSON 文档中删除数据并返回结果

SELECT JSON_REMOVE(jsonData,'$[0]') as jsonRemove FROM json where id = 1;

JSON_REPLACE(json_doc, path, val[, path, val] …)
替换 JSON 文档中的现有值并返回结果

SELECT JSON_REPLACE(jsonData, '$[1].a', '{1,2,3}') FROM json where id =1;

JSON_SET(json_doc, path, val[, path, val] …)
在 JSON 文档中插入或更新数据并返回结果

SELECT JSON_SET(jsonData,'$.sex','男','$[100]','json') jsonSet FROM json where id = 2;
JSON_SET()， JSON_INSERT()和 JSON_REPLACE()功能的关系：
JSON_SET() 替换现有值并添加不存在的值。
JSON_INSERT() 插入值而不替换现有值。
JSON_REPLACE()仅替换 现有值。

四、返回 JSON 值属性的函数
JSON_DEPTH(json_doc) 返回json文档的最大深度

SELECT JSON_DEPTH(jsonData) from json;

JSON_LENGTH(json_doc[, path]) 返回json文档的长度

SELECT JSON_LENGTH(jsondata,'$[1].b') json_length FROM json where id = 1;

JSON_TYPE(json_val)
返回JSON 值类型的字符串。这可以是对象、数组或标量类型

SELECT jsonData,JSON_TYPE(jsonData) FROM json;

SELECT JSON_TYPE(jsonData -> '$.age') FROM json where id = 2;

json_valid 判断是否为合法json文档