跨索引过滤 JoinedQuery
JoinedQuery 从引擎层面实现了跨索引过滤功能,用以简化 ES 在多个索引间的关系处理。
何时使用
需要通过主子表关联进行结果集过滤的场景
Join基本上是基于共同属性的两组文档之间的semi-join,其中结果仅包含文档集合的一个属性。该连接用于过滤基于第二文档集的一个文档集,类似于SQL中的EXISTS算子。
POST ${主表}/_search
{
"query": {
"joined": {
"query": {
"match_all": {} #主表原始Query
},
"join": [
{
"${主表关联字段}": {
"indices": "${子表}",
"field": "${子表关联字段}",
"query": {
"term": { #子表过滤Query
"field": "field_value"
}
}
}
}
]
}
}
}
示例
准备一些测试数据
主表
- score : 成绩表
子表
- user : 学生表
- subject : 科目表
info
注意: 关联外键类型需使用数值类型integer或long
-- 录入成绩表
POST score/_bulk
{ "index" : { "_type" : "_doc" } }
{ "score" : 95, "user_id": 1, "subject_id": 1 }
{ "index" : { "_type" : "_doc" } }
{ "score" : 66, "user_id": 1, "subject_id": 2 }
{ "index" : { "_type" : "_doc" } }
{ "score" : 88, "user_id": 2, "subject_id": 1 }
{ "index" : { "_type" : "_doc" } }
{ "score" : 100, "user_id": 2, "subject_id": 2 }
-- 录入学生表及科目表
PUT user
{
"mappings": {
"_doc": {
"properties": {
"id": {
"type": "integer"
},
"name": {
"type": "keyword"
}
}
}
}
}
PUT subject
{
"mappings": {
"_doc": {
"properties": {
"id": {
"type": "integer"
},
"name": {
"type": "keyword"
}
}
}
}
}
POST user/_doc
{
"id": 1,
"name": "jack"
}
POST user/_doc
{
"id": 2,
"name": "rose"
}
POST subject/_doc
{
"id": 1,
"name": "物理"
}
POST subject/_doc
{
"id": 2,
"name": "化学"
}
关联关系
score.user_id = user.id
score.subject_id = subject.id
示例1 - 查看 jack 的成绩表 (单表连接)
使用JoinedQuery跨索引过滤,功能类似如下sql
SELECT score.* FROM score
LEFT JOIN user
ON user.id=score.user_id
WHERE user.name='jack'
GET score/_search
{
"query": {
"joined": {
"query": {
"match_all": {}
},
"join": [{
"user_id": {
"indices": "user",
"field": "id",
"query": {
"term" : { "name" : "jack" }
}
}
}]
}
}
}
示例2 -多子表连接
类似
SELECT score.* FROM score
LEFT JOIN user
ON user.id=score.user_id
LEFT JOIN subject
ON subject.id=score.subject_id
WHERE user.name='?' AND subject.name='?'
查看 jack 同学的物理成绩表
GET score/_search
{
"query": {
"joined": {
"query": {
"match_all": {}
},
"join": [
{
"user_id": {
"indices": "user",
"field": "id",
"query": {
"term" : { "name" : "jack" }
}
}
},
{
"subject_id": {
"indices": "subject",
"field": "id",
"query": {
"term" : { "name" : "物理" }
}
}
}
]
}
}
}