1、数据:表名(sys_instance)
两条数据的instance_id和json:
1544934805590097921
{
“1544858400449740802”: “192.168.0.1”,
“1544859614050631681”: “192.168.0.1”,
“1544883963344306178”: “未监测”
}
1544934964273201153
{
“1544864900727554049”: “test”,
“1544865021708058625”: “test”,
“1544865147423932417”: “已上线”,
“1544883963344306178”: “已监测”
}
2.查询实例
2.1查询第一条数据instance_id为1544934805590097921的json字段中 "1544858400449740802"属性的值。
使用方式:
SELECT json字段名 -> '$."json内属性的key"' as 别名
FROM 表名
WHERE instance_id = id
sql实例:
SELECT json -> '$."1544858400449740802"' as ip
FROM sys_instance
WHERE instance_id = 1544934805590097921
该查询语句在java的mapper中的写法:
//instanceMap为instance对象和instance表的映射
<select id="getIp" resultType="String">
SELECT json -> '$."${ipKey}"' as ip
FROM sys_instance
WHERE instance_id = #{instanceId}
</select>
查询结果:
2.2查询json字段中 "1544858400449740802"属性值为:192.168.0.1的数据
SELECT *
FROM sys_instance
WHERE json -> '$."1544858400449740802"' = '192.168.0.1'
该查询语句在java的mapper中的写法:
<resultMap id="instanceMap" type="com.dxc.cmdb.entity.SysInstance">
<id column="instance_id" property="instanceId"/>
<result column="model_id" property="modelId"/>
<result column="json" property="json" typeHandler="com.dxc.cmdb.handler.JsonTypeHandler"/>
</resultMap>
<select id="adoptIPGetInstance" resultMap="instanceMap">
SELECT *
FROM sys_instance
WHERE json -> '$."${ipKey}"' =#{ip}
</select>
查询结果: