跳到主要内容

跨索引过滤 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 : 科目表
注意: 关联外键类型需使用数值类型(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" : "物理" }
}
}
}
]
}
}
}