mysql中json类型的使用

-- 创建json数组
SELECT JSON_ARRAY(20,30,50)
-- 查询json数组是否包含某个元素,mysql中0和1代表boolean类型.第二个参数必须是字符串
SELECT JSON_CONTAINS(JSON_ARRAY(20,30,50), "100")

-- 查询用户拥有的角色
SELECT r.role_name FROM tb_user u
LEFT JOIN tb_role r ON JSON_CONTAINS(u.role, CAST(r.id AS CHAR))
WHERE U.id = 3

-- 查询用户的权限(即用户的角色对应的权限)
SELECT DISTINCT p.permission_name FROM tb_user u
LEFT JOIN tb_role r ON JSON_CONTAINS(u.role, CAST(r.id AS CHAR))
LEFT JOIN tb_permission p ON JSON_CONTAINS(r.permissions, CAST(p.id AS CHAR))
WHERE U.id = 3

 

有以下json数据

[{"sn": "", "date": ["2020-11-4", "2020-11-5"], "endDate": "2020-11-5", "external": [{"values": ["F53S3C2", "F53S3D"], "fieldName": "standard_model", "displayName": "阿里机型"}], "isDragon": "", "rangeType": "2", "reportNum": 345, "startDate": "2020-11-4"},{"sn": "", "date": ["2020-11-4", "2020-11-5"], "endDate": "2020-11-5", "external": [{"values": ["A53S3C2", "A53S3D"], "fieldName": "standard_model", "displayName": "阿里机型"}], "isDragon": "", "rangeType": "2", "reportNum": 555, "startDate": "2020-11-4"}]

SELECT
 *
FROM
 USER t 
WHERE JSON_SEARCH(
    # 确定搜索范围
    JSON_EXTRACT( t.range_dimension, '$[*].external[*].values[*]' ),
    # 匹配多行
    'all',
    # 模糊匹配关键字
    'F53%'
   ) IS NOT NULL 
;