mysql解析json数组怎么解析_mysql json数组查询

mysql解析json数组怎么解析_mysql json数组查询 来源于网络，只是收集方便以后查找！从mysql 5.7开始支持json字段类型，sqlserver2016版本开始支持json字段，这是一种非常好用的格式！对记录的 *** 作一、创建有json字段的表CREATE TABLE t_json(id INT PRIMARY KEY, sname VARCHAR(20) , info JSON);二、插入记录1.插入含有json数组的记录INSERT INTO t_json(id,sname,info) VALUES( 1, ‘name1’, JSON_ARRAY(1, “abc”, NULL, TRUE, CURTIME()));2.插入含有json对象的记录INSERT INTO t_json(id,sname,info) VALUES( 2, ‘name2’, JSON_OBJECT(“age”, 20, “time”, now()));INSERT INTO t_json(id,sname,info) VALUES( 3, ‘name3’, ‘{“age”:20, “time”:”2018-07-14 10:52:00″}’);三、查询记录1.查询记录SELECT sname,JSON_EXTRACT(info,’$.age’) FROM t_json;SELECT sname,info->’$.age’ FROM t_json;2.查询keySELECT id,json_keys(info) FROM t_json;四、修改记录1.增加键UPDATE t_json SET info = json_set(info,’$.ip’,’192.168.1.1′) WHERE id = 2;2.变更值UPDATE t_json SET info = json_set(info,’$.ip’,’192.168.1.2′) WHERE id = 2;3.删除键UPDATE t_json SET info = json_remove(info,’$.ip’) WHERE id = 2;创建json值函数一、JSON_ARRAY 生成json数组//JSON_ARRAY(val1,val2,val3…)//生成一个包含指定元素的json数组。

SELECT JSON_ARRAY(1, “abc”, NULL, TRUE, CURTIME()); — [1, “abc”, null, true, “10:37:08.000000”]二、JSON_OBJECT 生成json对象//JSON_OBJECT(key1,val1,key2,val2…)//生成一个包含指定K-V对的json object。

如果有key为NULL或参数个数为奇数，则抛错。

SELECT JSON_OBJECT(‘age’, 20, ‘time’, now()); — {“age”: 20, “time”: “2021-06-18 14:49:57.000000”}三、JSON_QUOTE 加”号//JSON_QUOTE(json_val)//将json_val用”号括起来。

SELECT JSON_QUOTE(‘[1,2,3]’); — “[1,2,3]”搜索json值函数一、JSON_CONTAINS 指定数据是否存在set @j = ‘{“a”: 1, “b”: 2, “c”: {“d”: 4}}’;//JSON_CONTAINS(json_doc, val[, path])//查询json文档是否在指定path包含指定的数据，包含则返回1，否则返回0。

如果有参数为NULL或path不存在，则返回NULL。

SELECT JSON_CONTAINS(@j, ‘4’, ‘$.c.d’); — 1二、JSON_CONTAINS_PATH 指定路径是否存在//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(@j, ‘one’, ‘$.a’, ‘$.e’); — 1SELECT JSON_CONTAINS_PATH(@j, ‘all’, ‘$.a’, ‘$.c.d’); — 1三、JSON_EXTRACT 查找所有指定数据//JSON_EXTRACT(json_doc, path[, path] …)//从json文档里抽取数据。

如果有参数有NULL或path不存在，则返回NULL。

如果抽取出多个path，则返回的数据封闭在一个json array里。

set @j2 = ‘[10, 20, [30, 40]]’;SELECT JSON_EXTRACT(‘[10, 20, [30, 40]]’, ‘$[1]’); — 20SELECT JSON_EXTRACT(‘[10, 20, [30, 40]]’, ‘$[1]’, ‘$[0]’); — [20, 10]SELECT JSON_EXTRACT(‘[10, 20, [30, 40]]’, ‘$[2][*]’); — [30, 40]四、JSON_KEYS 查找所有指定键值//JSON_KEYS(json_doc[, path])//获取json文档在指定路径下的所有键值，返回一个json array。

如果有参数为NULL或path不存在，则返回NULL。

SELECT JSON_KEYS(‘{“a”: 1, “b”: {“c”: 30}}’); — [“a”, “b”]SELECT JSON_KEYS(‘{“a”: 1, “b”: {“c”: 30}}’, ‘$.b’); — [“c”]SELECT id,json_keys(info) FROM t_json;五、JSON_SEARCH 查找所有指定值的位置//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 @j3 = ‘[“abc”, [{“k”: “10”}, “def”], {“x”:”abc”}, {“y”:”bcd”}]’;SELECT JSON_SEARCH(@j3, ‘one’, ‘abc’); — “$[0]”SELECT JSON_SEARCH(@j3, ‘all’, ‘abc’); — [“$[0]”, “$[2].x”]SELECT JSON_SEARCH(@j3, ‘all’, ‘abc’, NULL, ‘$[2]’); — “$[2].x”SELECT JSON_SEARCH(@j3, ‘all’, ’10’); — “$[1][0].k”SELECT JSON_SEARCH(@j3, ‘all’, ‘%b%’); — [“$[0]”, “$[2].x”, “$[3].y”]SELECT JSON_SEARCH(@j3, ‘all’, ‘%b%’, NULL, ‘$[2]’); — “$[2].x”修改json值函数一、JSON_ARRAY_APPEND 指定位置追加数组元素//JSON_ARRAY_APPEND(json_doc, path, val[, path, val] …)//在指定path的json array尾部追加val。

如果指定path是一个json object，则将其封装成一个json array再追加。

如果有参数为NULL，则返回NULL。

SET @j4 = ‘[“a”, [“b”, “c”], “d”]’;//SELECT JSON_ARRAY_APPEND(@j4, ‘$[1][0]’, 3); — [“a”, [[“b”, 3], “c”], “d”]SET @j5 = ‘{“a”: 1, “b”: [2, 3], “c”: 4}’;SELECT JSON_ARRAY_APPEND(@j5, ‘$.b’, ‘x’); — {“a”: 1, “b”: [2, 3, “x”], “c”: 4}SELECT JSON_ARRAY_APPEND(@j5, ‘$.c’, ‘y’); — {“a”: 1, “b”: [2, 3], “c”: [4, “y”]}SELECT JSON_ARRAY_APPEND(@j5, ‘$’, ‘z’); — [{“a”: 1, “b”: [2, 3], “c”: 4}, “z”]二、JSON_ARRAY_INSERT 指定位置插入数组元素//JSON_ARRAY_INSERT(json_doc, path, val[, path, val] …)//在path指定的json array元素插入val，原位置及以右的元素顺次右移。

如果path指定的数据非json array元素，则略过此val；如果指定的元素下标超过json array的长度，则插入尾部。

SET @j6 = ‘[“a”, {“b”: [1, 2]}, [3, 4]]’;SELECT JSON_ARRAY_INSERT(@j6, ‘$[1]’, ‘x’); — [“a”, “x”, {“b”: [1, 2]}, [3, 4]]SELECT JSON_ARRAY_INSERT(@j6, ‘$[100]’, ‘x’); — [“a”, {“b”: [1, 2]}, [3, 4], “x”]SELECT JSON_ARRAY_INSERT(@j6, ‘$[1].b[0]’, ‘x’); — [“a”, {“b”: [“x”, 1, 2]}, [3, 4]]SELECT JSON_ARRAY_INSERT(@j6, ‘$[0]’, ‘x’, ‘$[3][1]’, ‘y’); — [“x”, “a”, {“b”: [1, 2]}, [3, “y”, 4]]三、JSON_INSERT 指定位置插入//JSON_INSERT(json_doc, path, val[, path, val] …)//在指定path下插入数据，如果path已存在，则忽略此val（不存在才插入）。

SET @j7 = ‘{ “a”: 1, “b”: [2, 3]}’;SELECT JSON_INSERT(@j7, ‘$.a’, 10, ‘$.c’, ‘[true, false]’); — {“a”: 1, “b”: [2, 3], “c”: “[true, false]”}四、JSON_REPLACE 指定位置替换//JSON_REPLACE(json_doc, path, val[, path, val] …)//替换指定路径的数据，如果某个路径不存在则略过（存在才替换）。

如果有参数为NULL，则返回NULL。

SELECT JSON_REPLACE(@j7, ‘$.a’, 10, ‘$.c’, ‘[true, false]’); — {“a”: 10, “b”: [2, 3]}五、JSON_SET 指定位置设置//JSON_SET(json_doc, path, val[, path, val] …)//设置指定路径的数据（不管是否存在）。

如果有参数为NULL，则返回NULL。

SELECT JSON_SET(@j7, ‘$.a’, 10, ‘$.c’, ‘[true, false]’); — {“a”: 10, “b”: [2, 3], “c”: “[true, false]”}六、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再按照规则一进行mege。

SELECT JSON_MERGE(‘[1, 2]’, ‘[true, false]’); — [1, 2, true, false]SELECT JSON_MERGE(‘{“name”: “x”}’, ‘{“id”: 47}’); — {“id”: 47, “name”: “x”}SELECT JSON_MERGE(‘1’, ‘true’); — [1, true]SELECT JSON_MERGE(‘[1, 2]’, ‘{“id”: 47}’); — [1, 2, {“id”: 47}]七、JSON_REMOVE 指定位置移除//JSON_REMOVE(json_doc, path[, path] …)//移除指定路径的数据，如果某个路径不存在则略过此路径。

如果有参数为NULL，则返回NULL。

SET @j8 = ‘[“a”, [“b”, “c”], “d”]’;SELECT JSON_REMOVE(@j8, ‘$[1]’); — [“a”, “d”]八、JSON_UNQUOTE 去”号//JSON_UNQUOTE(val)//去掉val的引号。

如果val为NULL，则返回NULL。

SELECT JSON_UNQUOTE(“\”123\””); — 123返回json值属性的函数一、JSON_DEPTH 深度//JSON_DEPTH(json_doc)//获取json文档的深度。

如果参数为NULL，则返回NULL。

//空的json array、json object或标量的深度为1。

SELECT JSON_DEPTH(‘{}’), JSON_DEPTH(‘[]’), JSON_DEPTH(‘true’); — 1 1 1SELECT JSON_DEPTH(‘[10, 20]’), JSON_DEPTH(‘[[], {}]’); — 2 2SELECT JSON_DEPTH(‘[10, {“a”: 20}]’); — 3二、JSON_LENGTH 长度//JSON_LENGTH(json_doc[, path])//获取指定路径下的长度。

如果参数为NULL，则返回NULL。

　//长度的计算规则：//标量的长度为1；//json array的长度为元素的个数；//json object的长度为key的个数。

SELECT JSON_LENGTH(‘[1, 2, {“a”: 3}]’); — 3SELECT JSON_LENGTH(‘{“a”: 1, “b”: {“c”: 30}}’); — 2SELECT JSON_LENGTH(‘{“a”: 1, “b”: {“c”: 30}}’, ‘$.b’); — 1三、JSON_TYPE 类型//JSON_TYPE(json_val)//获取json文档的具体类型。

如果参数为NULL，则返回NULL。

select JSON_TYPE(‘[1,2]’); — ARRAY四、JSON_VALID 是否有效json格式//JSON_VALID(val)//判断val是否为有效的json格式，是为1，不是为0。

如果参数为NUL，则返回NULL。

SELECT JSON_VALID(‘{“a”: 1}’); — 1SELECT JSON_VALID(‘hello’), JSON_VALID(‘”hello”‘); — 1附录：函数名描述JSON_APPEND()（废弃的5.7.9）JSON文件追加数据JSON_ARRAY()创建JSON数组JSON_ARRAY_APPEND()JSON文件追加数据JSON_ARRAY_INSERT()插入JSON数组->在评估路径返回JSON列值；相当于json_extract()。

JSON_CONTAINS()是否包含特定对象的JSON文档路径JSON_CONTAINS_PATH()无论是JSON文件包含任何数据路径JSON_DEPTH()JSON文档的最大深度JSON_EXTRACT()从JSON文档返回数据->>在评估路径和结束引语结果返回JSON列值；相当于json_unquote（json_extract()）。

JSON_INSERT()将数据插入到JSON文档JSON_KEYS()从JSON文件密钥数组JSON_LENGTH()在JSON文档中的元素数JSON_MERGE()（废弃的5.7.22）合并的JSON文件，保存重复键。

不json_merge_preserve()的同义词JSON_MERGE_PATCH()合并的JSON文件，免去重复键的值JSON_MERGE_PRESERVE()合并的JSON文件，保存重复键JSON_OBJECT()创建JSON对象JSON_PRETTY()版画在人类可读的格式JSON文档，每个数组元素或对象成员打印在新的行中，缩进两个空格就其母。

JSON_QUOTE()引用JSON文档JSON_REMOVE()从JSON文件中删除数据JSON_REPLACE()在JSON文件的值替换JSON_SEARCH()在JSON文件价值路径JSON_SET()将数据插入到JSON文档JSON_STORAGE_SIZE()用于一个JSON文件的二进制表示形式存储空间；一个JSON柱，空间时使用的文档插入到任何部分更新之前，JSON_TYPE()JSON值类型JSON_UNQUOTE()JSON值而言JSON_VALID()JSON值是否是有效的