mysql新类型json操作技巧

查询json对象money属性值

select ext -> '$.money' from test where id = 1;

select ext ->> '$.money' from test  where id = 1;

查询json对象等于某个值

-- 转成字符串比较
SELECT ext FROM `test` WHERE json_unquote(json_extract(ext, '$.money')) = '{"max": 2, "min": 1}';

-- 转成字符串比较
SELECT customer_info FROM `test` WHERE json_unquote(json_extract(customer_info, '$')) = '{"cId": "1001", "cLevel": "V1"}';

-- 比较对象
SELECT customer_info FROM `test` WHERE customer_info = CAST('{"customerId": "1001", "creditLevel": "V1"}' AS JSON);

查询json属性是空

SELECT ext -> '$.fee' FROM test x
where  id  IN (5) and JSON_VALUE(x.ext, '$.fee') is null

查询json属性是空数组

SELECT ext  -> '$.prices',JSON_VALUE(x.ext , '$.prices') FROM test x
where  id  IN (5) and  JSON_VALUE(x.ext , '$.prices')='[]' 

包含查询

-- 包含值
SELECT
    JSON_CONTAINS('[11, 22, {"x": 33}]', '11') as `11`,
    JSON_CONTAINS('[11, 22, {"x": 33}]', '{"x": 33}') as `{"x": 33}`,
    JSON_CONTAINS('[11, 22, {"x": 33}]', '33') as `33`;

-- 是否包含值22,指定路径$[1]    
 SELECT
    JSON_CONTAINS('[11, 22, [33, 44]]', '22'),
    JSON_CONTAINS('[11, 22, [33, 44]]', '22', '$[1]'),
    JSON_CONTAINS('[11, 22, [33, 44]]', '22', '$[2]');
	
-- 是否包含值bb,指定路径$[1]     
  SELECT
    JSON_CONTAINS('["aa", "bb"]', '"bb"'),
    JSON_CONTAINS('["aa", "bb"]', '"bb"', '$[1]');  	
	
 SELECT
    JSON_CONTAINS('{"x": 11, "y": 22, "z": 33}', '22'),
    JSON_CONTAINS('{"x": 11, "y": 22, "z": 33}', '22', '$.d'),
    JSON_CONTAINS('{"x": 11, "y": 22, "z": 33}', '22', '$.x'),
    JSON_CONTAINS('{"x": 11, "y": 22, "z": 33}', '22', '$.y'); 		

 -- 数组索引从0开始,所有路径有值,$[0]路径  
 SELECT
    JSON_CONTAINS_PATH('[11, 22, {"x": 33}]', 'all', '$[0]') as `$[0]`,
    JSON_CONTAINS_PATH('[11, 22, {"x": 33}]', 'all', '$[3]') as `$[3]`,
    JSON_CONTAINS_PATH('[11, 22, {"x": 33}]', 'all', '$[2].x') as `$[2].x`;
   
SELECT
    JSON_CONTAINS_PATH('[11, 22, {"x": 33}]', 'one', '$[0]', '$[3]') as `one`,
    JSON_CONTAINS_PATH('[11, 22, {"x": 33}]', 'all', '$[0]', '$[3]') as `all`;

-- 获取json属性值
SELECT JSON_VALUE('[0, {"x": 11, "y": 22, "z": 33}]', '$[1].y');
SELECT JSON_VALUE('{"x": 11, "y": 22, "z": 33}', '$.y');    

检查一个指定的值是否是一个JSON数组中的元素

SELECT 1 MEMBER OF('[1, 2, "a"]'),
    '{"x": 0}' MEMBER OF('[{"x": 0},{"x": 1}]'),
    CAST('{"x": 0}' AS JSON) MEMBER OF('[{"x": 0},{"x": 1}]');

更新属性值

update `test` set money='{"max": 4300.0, "min": 4300.0}' where id=2;

更新属性值为空数组

UPDATE `test` SET ext = JSON_SET(ext,'$.labels',JSON_ARRAY()) WHERE id = 1;

更新属性值为null

UPDATE `test` SET ext = JSON_SET(ext,'$.labels',null) WHERE id = 2;

更新同时更新多个属性

UPDATE `test` 
SET ext = JSON_SET(ext,'$.contact','张三','$.contactPhone','189222225431') 
WHERE id = 2;

更新复杂属性嵌套json

update test set ext = JSON_SET(ext,'$.moeny',CAST('{"max": 2, "min": 1}' AS JSON)) 
where id in (3);