Mysql JSON索引与关联查询
Mysql JSON索引与关联查询
·
JSON Path语法
// 比较简单,举两个例子 set @jsonSource = { "aaa": [ {"ccc":"1"}, {"ccc":"2"}, ], "bbb":"bbb的值" }; @jsonSource-> "$.bbb" // 结果为 bbb的值 @jsonSource-> "$.aaa[*].ccc" // 结果为 ["1","2"] |
给JSON字段添加索引
Mysql 8.0.x版本后,支持对json字段创建索引,直接 create index 即可,需要使用cast方法将json目标字段转换成可以创建索引的类型。有两种情况:
普通索引:每条记录和json字段为1对1关系
多值索引:每条记录和json字段为1对多关系(对应array,或是jsonPath取值结果是array情况)
-- 普通索引 ADD INDEX index_modify_user((CAST(permission_json->'$.aaa' AS CHAR(64)))) -- 多值索引 (区别仅在于用cast 方法转化成一个 ARRAY) ADD INDEX index_modify_user((CAST(permission_json->'$.modify[*]' AS CHAR(64) ARRAY ))) |
根据JSON索引查询
只有少数语句支持JSON字段索引。如果需要在索引上查询数据,使用member of。
使用member of语句可以使json上建的索引生效,其他使用json索引语句参考官网。
具体语法: ‘Value’ member of ( 'Path' )
例如:
SELECT topic_id FROM topic WHERE "123" member of (permission_json->'$.modify'); |
对应Jooq语法:
// Jooq不支持member of ,我们自己封装了一个condition:JooqSyntax.memberOfCondition dsl.select(TOPIC.TOPIC_ID).from(TOPIC).where(JooqSyntax.memberOfCondition(userId, TOPIC.PERMISSION_JSON, "$.modify")) |
JSON字段关联查询
先使用JSON_TABLE方法将json转化成一个临时表,再进行关联,例如
SELECT u.* FROM topic AS t, user AS u INNER JOIN JSON_TABLE( t.permission_json, '$.modify[*]' COLUMNS( uid VARCHAR(50) PATH "$") ) AS temp ON u.user_id = temp.uid WHERE t.topic_id= 1481071225944932352; |
注意临时表一定要指定(AS)一个表名
对应Jooq语法:
// 先定义出一个临时表 Table tempTable = DSL.jsonTable(TOPIC.PERMISSION_JSON, DSL.field("{0}", String.class,"$.modify[*]" )) .column("uid", SQLDataType.VARCHAR(32)).path("$").asTable("temp"); // 再使用临时表关联查询 List<UserDto> userDtos = dsl.select(USER.fields()) .from(TOPIC, USER) .innerJoin(tempTable).on(USER.USER_ID.eq(tempTable.field("uid" ))) .where(TOPIC.TOPIC_ID.eq(topicId)).fetchInto(UserDto.class); |
更多推荐
已为社区贡献1条内容
所有评论(0)