mysql5.7以上支持json的操作，以及增加了json存储类型
一般数据库存储json类型的数据会用json类型或者text类型
注意：用json类型的话
1）JSON列存储的必须是JSON格式数据，否则会报错。
2）JSON数据类型是没有默认值的。

执行查询sql

使用

使用 字段->’$.json属性’ 进行查询条件

  1. SELECT
  2.     chk_rule_cfg -> '$.stdRuleId' AS stdRuleId 
  3. FROM
  4. chk_rule;

查询json数组里面对象的id等于51的记录

用JSON_CONTAINS(字段,JSON_OBJECT(‘json属性’, “内容”))

  1. select *
  2. from 
  3. chk_rule
  4. where JSON_CONTAINS(chk_rule_cfg,JSON_OBJECT('id',"51"));

查询记录

  1. -- 查询记录
  2. SELECT chk_name,JSON_EXTRACT(chk_rule_cfg,'$.stdRuleId') FROM chk_rule;
  3. SELECT chk_name,chk_rule_cfg -> '$.stdRuleId' AS stdRuleId FROM chk_rule;
  4. -- 查询key
  5. SELECT chk_name,json_keys(chk_rule_cfg) FROM chk_rule;

修改记录

  1. -- 增加键
  2. UPDATE chk_rule SET info = json_set(info,'$.stdRuleId','188') WHERE id = 2;
  4. -- 变更值
  5. UPDATE chk_rule SET info = json_set(info,'$.stdRuleId','188') WHERE id = 2;
  7. -- 删除键
  8. UPDATE chk_rule SET info = json_remove(info,'$.stdRuleId') WHERE id = 2;

创建json函数

JSON_ARRAY 生成json数组

  1. -- JSON_ARRAY(val1,val2,val3...)
  2. -- 生成一个包含指定元素的json数组。
  3. SELECT JSON_ARRAY(1, "abc", NULL, TRUE, CURTIME()); -- [1, "abc", null, true, "10:37:08.000000"]

JSON_OBJECT 生成json对象

  1. -- JSON_OBJECT(key1,val1,key2,val2...)
  2. -- 生成一个包含指定K-V对的json object。如果有key为NULL或参数个数为奇数，则抛错。
  3. SELECT JSON_OBJECT('age', 20, 'time', now()); -- {"age": 20, "time": "2021-06-18 14:49:57.000000"}

JSON_QUOTE 加”号

  1. -- JSON_QUOTE(json_val)
  2. -- 将json_val用"号括起来。
  3. SELECT JSON_QUOTE('[1,2,3]'); -- "[1,2,3]"

搜索json函数

JSON_CONTAINS 指定数据是否存在

  1. set @j = '{"a": 1, "b": 2, "c": {"d": 4}}';
  2. -- JSON_CONTAINS(json_doc, val[, path])
  3. -- 查询json文档是否在指定path包含指定的数据，包含则返回1，否则返回0。如果有参数为NULL或path不存在，则返回NULL。
  4. SELECT JSON_CONTAINS(@j, '4', '$.c.d'); -- 1

JSON_CONTAINS_PATH 指定路径是否存在

  1. -- JSON_CONTAINS_PATH(json_doc, one_or_all, path[, path] ...)
  2. -- 查询是否存在指定路径，存在则返回1，否则返回0。如果有参数为NULL，则返回NULL。
  3. -- one_or_all只能取值"one"或"all"，one表示只要有一个存在即可；all表示所有的都存在才行。
  4. SELECT JSON_CONTAINS_PATH(@j, 'one', '$.a', '$.e'); -- 1
  5. SELECT JSON_CONTAINS_PATH(@j, 'all', '$.a', '$.c.d'); -- 1

JSON_EXTRACT 查找所有指定数据

  1. -- JSON_EXTRACT(json_doc, path[, path] ...)
  2. -- 从json文档里抽取数据。如果有参数有NULL或path不存在，则返回NULL。如果抽取出多个path，则返回的数据封闭在一个json array里。
  3. set @j2 = '[10, 20, [30, 40]]';
  4. SELECT JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]'); -- 20
  5. SELECT JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]', '$[0]'); -- [20, 10]
  6. SELECT JSON_EXTRACT('[10, 20, [30, 40]]', '$[2][*]'); -- [30, 40]

JSON_KEYS 查找所有指定键值

  1. -- JSON_KEYS(json_doc[, path])
  2. -- 获取json文档在指定路径下的所有键值，返回一个json array。如果有参数为NULL或path不存在，则返回NULL。
  3. SELECT JSON_KEYS('{"a": 1, "b": {"c": 30}}'); -- ["a", "b"]
  4. SELECT JSON_KEYS('{"a": 1, "b": {"c": 30}}', '$.b'); -- ["c"]
  5. SELECT id,json_keys(info) FROM t_json;

JSON_SEARCH 查找所有指定值的位置

  1. -- JSON_SEARCH(json_doc, one_or_all, search_str[, escape_char[, path] ...])
  2. -- 查询包含指定字符串的paths，并作为一个json array返回。如果有参数为NUL或path不存在，则返回NULL。
  3. -- one_or_all："one"表示查询到一个即返回；"all"表示查询所有。
  4. -- search_str：要查询的字符串。 可以用LIKE里的'%'或‘_’匹配。
  5. -- path：在指定path下查。
  6. SET @j3 = '["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]';
  7. SELECT JSON_SEARCH(@j3, 'one', 'abc'); -- "$[0]"
  8. SELECT JSON_SEARCH(@j3, 'all', 'abc'); -- ["$[0]", "$[2].x"]
  9. SELECT JSON_SEARCH(@j3, 'all', 'abc', NULL, '$[2]'); -- "$[2].x"
  10. SELECT JSON_SEARCH(@j3, 'all', '10'); -- "$[1][0].k"
  11. SELECT JSON_SEARCH(@j3, 'all', '%b%'); -- ["$[0]", "$[2].x", "$[3].y"]
  12. SELECT JSON_SEARCH(@j3, 'all', '%b%', NULL, '$[2]'); -- "$[2].x"

修改json值函数

JSON_ARRAY_APPEND 指定位置追加数组元素

  1. -- JSON_ARRAY_APPEND(json_doc, path, val[, path, val] ...)
  2. -- 在指定path的json array尾部追加val。如果指定path是一个json object，则将其封装成一个json array再追加。如果有参数为NULL，则返回NULL。
  3. SET @j4 = '["a", ["b", "c"], "d"]';
  4. -- SELECT JSON_ARRAY_APPEND(@j4, '$[1][0]', 3); -- ["a", [["b", 3], "c"], "d"]
  5. SET @j5 = '{"a": 1, "b": [2, 3], "c": 4}';
  6. SELECT JSON_ARRAY_APPEND(@j5, '$.b', 'x'); -- {"a": 1, "b": [2, 3, "x"], "c": 4} 
  7. SELECT JSON_ARRAY_APPEND(@j5, '$.c', 'y'); -- {"a": 1, "b": [2, 3], "c": [4, "y"]}
  8. SELECT JSON_ARRAY_APPEND(@j5, '$', 'z'); -- [{"a": 1, "b": [2, 3], "c": 4}, "z"]

JSON_ARRAY_INSERT 指定位置插入数组元素

  1. -- JSON_ARRAY_INSERT(json_doc, path, val[, path, val] ...)
  2. -- 在path指定的json array元素插入val，原位置及以右的元素顺次右移。如果path指定的数据非json array元素，则略过此val；如果指定的元素下标超过json array的长度，则插入尾部。
  3. SET @j6 = '["a", {"b": [1, 2]}, [3, 4]]';
  4. SELECT JSON_ARRAY_INSERT(@j6, '$[1]', 'x'); -- ["a", "x", {"b": [1, 2]}, [3, 4]]
  5. SELECT JSON_ARRAY_INSERT(@j6, '$[100]', 'x'); -- ["a", {"b": [1, 2]}, [3, 4], "x"]
  6. SELECT JSON_ARRAY_INSERT(@j6, '$[1].b[0]', 'x'); -- ["a", {"b": ["x", 1, 2]}, [3, 4]]
  7. SELECT JSON_ARRAY_INSERT(@j6, '$[0]', 'x', '$[3][1]', 'y'); -- ["x", "a", {"b": [1, 2]}, [3, "y", 4]]

JSON_INSERT 指定位置插入

  1. -- JSON_INSERT(json_doc, path, val[, path, val] ...)
  2. -- 在指定path下插入数据，如果path已存在，则忽略此val（不存在才插入）。
  3. SET @j7 = '{ "a": 1, "b": [2, 3]}';
  4. SELECT JSON_INSERT(@j7, '$.a', 10, '$.c', '[true, false]'); -- {"a": 1, "b": [2, 3], "c": "[true, false]"}

JSON_REPLACE 指定位置替换

  1. -- JSON_REPLACE(json_doc, path, val[, path, val] ...)
  2. -- 替换指定路径的数据，如果某个路径不存在则略过（存在才替换）。如果有参数为NULL，则返回NULL。
  3. SELECT JSON_REPLACE(@j7, '$.a', 10, '$.c', '[true, false]'); -- {"a": 10, "b": [2, 3]}

JSON_SET 指定位置设置

  1. -- JSON_SET(json_doc, path, val[, path, val] ...)
  2. -- 设置指定路径的数据（不管是否存在）。如果有参数为NULL，则返回NULL。
  3. SELECT JSON_SET(@j7, '$.a', 10, '$.c', '[true, false]'); -- {"a": 10, "b": [2, 3], "c": "[true, false]"}

JSON_MERGE 合并

  1. -- JSON_MERGE(json_doc, json_doc[, json_doc] ...)
  2. -- merge多个json文档。规则如下：
  3. -- 如果都是json array，则结果自动merge为一个json array；
  4. -- 如果都是json object，则结果自动merge为一个json object；
  5. -- 如果有多种类型，则将非json array的元素封装成json array再按照规则一进行mege。
  6. SELECT JSON_MERGE('[1, 2]', '[true, false]'); -- [1, 2, true, false]
  7. SELECT JSON_MERGE('{"name": "x"}', '{"id": 47}'); -- {"id": 47, "name": "x"}
  8. SELECT JSON_MERGE('1', 'true'); -- [1, true]
  9. SELECT JSON_MERGE('[1, 2]', '{"id": 47}'); -- [1, 2, {"id": 47}]

JSON_REMOVE 指定位置移除

  1. -- JSON_REMOVE(json_doc, path[, path] ...)
  2. -- 移除指定路径的数据，如果某个路径不存在则略过此路径。如果有参数为NULL，则返回NULL。
  3. SET @j8 = '["a", ["b", "c"], "d"]';
  4. SELECT JSON_REMOVE(@j8, '$[1]'); -- ["a", "d"]

JSON_UNQUOTE 去”号

  1. -- JSON_UNQUOTE(val)
  2. -- 去掉val的引号。如果val为NULL，则返回NULL。
  3. SELECT JSON_UNQUOTE("\"123\""); -- 123

返回json值属性的函数

JSON_DEPTH 深度

  1. -- JSON_DEPTH(json_doc)
  2. -- 获取json文档的深度。如果参数为NULL，则返回NULL。
  3. -- 空的json array、json object或标量的深度为1。
  4. SELECT JSON_DEPTH('{}'), JSON_DEPTH('[]'), JSON_DEPTH('true'); -- 1 1 1
  5. SELECT JSON_DEPTH('[10, 20]'), JSON_DEPTH('[[], {}]'); -- 2 2
  6. SELECT JSON_DEPTH('[10, {"a": 20}]'); -- 3

JSON_LENGTH 长度

  1. -- JSON_LENGTH(json_doc[, path])
  2. -- 获取指定路径下的长度。如果参数为NULL，则返回NULL。　
  3. -- 长度的计算规则：
  4. -- 标量的长度为1；
  5. -- json array的长度为元素的个数；
  6. -- json object的长度为key的个数。
  7. SELECT JSON_LENGTH('[1, 2, {"a": 3}]'); -- 3
  8. SELECT JSON_LENGTH('{"a": 1, "b": {"c": 30}}'); -- 2
  9. SELECT JSON_LENGTH('{"a": 1, "b": {"c": 30}}', '$.b'); -- 1

JSON_TYPE 类型

  1. -- JSON_TYPE(json_val)
  2. -- 获取json文档的具体类型。如果参数为NULL，则返回NULL。
  3. select JSON_TYPE('[1,2]'); -- ARRAY

JSON_VALID 是否有效json格式

  1. -- JSON_VALID(val)
  2. -- 判断val是否为有效的json格式，是为1，不是为0。如果参数为NUL，则返回NULL。
  3. SELECT JSON_VALID('{"a": 1}'); -- 1
  4. SELECT JSON_VALID('hello'), JSON_VALID('"hello"'); -- 1

附：

  1. JSON_ARRAY 生成json数组
  2. JSON_OBJECT 生成json对象
  3. JSON_QUOTE 加"号
  4. JSON_CONTAINS 指定数据是否存在
  5. JSON_CONTAINS_PATH 指定路径是否存在
  6. JSON_EXTRACT 查找所有指定数据
  7. JSON_KEYS 查找所有指定键值
  8. JSON_SEARCH 查找所有指定值的位置
  9. JSON_ARRAY_APPEND  指定位置追加数组元素
  10. JSON_ARRAY_INSERT 指定位置插入数组元素
  11. JSON_INSERT 指定位置插入
  12. JSON_REPLACE 指定位置替换
  13. JSON_SET 指定位置设置
  14. JSON_MERGE 合并
  15. JSON_REMOVE 指定位置移除
  16. JSON_UNQUOTE 去"号
  17. JSON_DEPTH 深度
  18. JSON_LENGTH 长度
  19. JSON_TYPE 类型
  20. JSON_VALID 是否有效json格式

mysql官方文档: https://dev.mysql.com/doc/refman/5.7/en/json-utility-functions.html