mysql 查询json

------------------------ 数组 ------------------------

 

> 数组创建、追加、插入

 

 

-- 定义数组
SET @mapJSON = '';

-- 创建数组
SET @mapJSON = JSON_ARRAY(JSON_OBJECT('goodname', '物品1', 'goodnum', 1), JSON_OBJECT('goodname', '物品2', 'goodnum', 3));
SELECT @mapJSON;
-- [{"goodnum": 1, "goodname": "物品1"}, {"goodnum": 3, "goodname": "物品2"}]

-- 数组追加元素
SET @mapJSON = JSON_ARRAY_APPEND(@mapJSON, '$', JSON_OBJECT('goodname', '物品3', 'goodnum', 7));
SELECT @mapJSON;
-- [{"goodnum": 1, "goodname": "物品1"}, {"goodnum": 3, "goodname": "物品2"}, {"goodnum": 7, "goodname": "物品3"}]

-- 数组在指定位置插入元素
SET @mapJSON = JSON_ARRAY_INSERT(@mapJSON, '$[0]', JSON_OBJECT('goodname', '物品0', 'goodnum', 10));
SELECT @mapJSON;
-- [{"goodnum": 10, "goodname": "物品0"}, {"goodnum": 1, "goodname": "物品1"}, {"goodnum": 3, "goodname": "物品2"}, {"goodnum": 7, "goodname": "物品3"}]

 

 

> 数组提取

-- ------------------------------ 提取 JSON_EXTRACT ------------------------------
-- 定义数组
SET @mapJSON = '[{"id":"10000004555096","name":"1.docx","format":"docx","type":0},{"id":"10000004555098","name":"2.doc","format":"doc","type":0},{"id":"10000004555097","name":"3.html","format":"html","type":1}]';

-- 提取数组下标为 0 的元素
SELECT JSON_EXTRACT(@mapJSON, "$[0]") AS result;
-- {"id": "10000004555096", "name": "1.docx", "type": 0, "format": "docx"}

-- 提取数组每个元素的字段 name
SELECT JSON_EXTRACT(@mapJSON, "$[*].name") AS result;
-- ["1.docx", "2.doc", "3.html"]

-- 提取数组下标为 1 的元素的字段 name
SELECT JSON_EXTRACT(@mapJSON, "$[1].name") AS result;
-- "2.doc"

-- 提取数组下标为 2 的元素的字段 type
SELECT JSON_EXTRACT(@mapJSON, "$[2].type") AS result;
-- 1

 

> 数组查找

-- ------------------------------ 查找 JSON_CONTAINS ------------------------------
-- 定义数组
SET @mapJSON = '[{"id":"10000004555096","name":"1.docx","format":"docx","type":0},{"id":"10000004555098","name":"2.doc","format":"doc","type":0},{"id":"10000004555097","name":"3.html","format":"html","type":1}]';

-- 示例1：查找 id 等于 10000004555096 的元素
SELECT JSON_CONTAINS(JSON_EXTRACT(@mapJSON, "$[*]"), JSON_OBJECT('id', '10000004555096')) AS result;
-- 1

-- 示例1：查找 name 等于 3.htm1 的元素
SELECT JSON_CONTAINS(JSON_EXTRACT(@mapJSON, "$[*].name"), '"3.html"') AS result;
-- 1
SELECT JSON_CONTAINS(JSON_EXTRACT(@mapJSON, "$[*].name"), '"3.HTML"') AS result;
-- 0

 

------------------------ 对象 ------------------------

> 对象提取

 

-- 定义对象
SET @dataObj = '{"wechat": {"openid": "1", "unionid": "", "nickname": "", "headimgurl": ""}}';

-- 示例1：获取 wechat 属性
SELECT JSON_EXTRACT(@dataObj, '$.wechat') AS result;

-- 示例1：获取 wechat.openid 属性
SELECT JSON_EXTRACT(@dataObj, '$.wechat.openid') AS result;

 

------------------------ 其他 ------------------------

-- 定义JSON字符串
SET @jsonStr = '{"name": "zhangsan"}';
-- 将字符串解析成JSON对象
SET @jsonObj = JSON_EXTRACT(@json, '$');