跨索引过滤 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" : "物理" }
                    }
                }
              }
            ]
        }
    }
}