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);

Logo

为开发者提供学习成长、分享交流、生态实践、资源工具等服务,帮助开发者快速成长。

更多推荐