当数据库中存在json类型的数据,如图
json类型的值在数据库中保存的时候,会先字母排序并加空格后保存
场景:业务上需要校验,此json字段是否跟库里的json完全匹配(验重)
原理:利用mysql的cast json方法,把json完成转换为跟库里匹配的格式后,查询
SELECT
Id,
`Name`,
`Status`,
TaskJson,
CreatedAt,
UpdatedAt
FROM
sys_taskinfo
WHERE
( Deleted = 0 AND
TaskJson -> '$' =
CAST( '{"dealerShortName":[{"label":"哈哈3","value":"789"},{"label":"哈哈4","value":"122"}],"dmsCity":{"label":"哈哈2","value":"456"},"dmsPro":{"label":"哈哈","value":"123"},"locationTimeMax":"2022-02-08 12:23:23","locationTimeMin":"2021-02-08 12:23:23","reportTerminalTimeMax":"2022-08-08 12:23:23"
,"reportTerminalTimeMin":"2021-07-08 12:23:23"}' AS json ) )
//这里,json字母可无序,会自动字母排序并加空格
QueryWrapper写法:
QueryWrapper queryWrapper = Wrappers.query()
.eq(SysTaskinfoEntity.COL_DELETED, false)
.apply("TaskJson ->'$'= CAST('" + filterJson + "' as json)");
service.getOne(queryWrapper);