elasticsearch联合查询

联合查询

• 情景：祖孙三代 question->answer->vote

  question是answer父节点
  answer是vote的父节点
  

• 建索引结构

  PUT join_multi_index
  {
    "mappings": {
      "_doc":{
        "properties":{
          "my_join_field":{
            "type":"join",
            "relations":{
              "question":"answer",
              "answer":"vote"
            }
          }
        }
      }
    }
  }
  

• 查询索引情况

  GET join_multi_index
  

• 插入数据

  # 插入question数据
  PUT join_multi_index/_doc/1?refresh
  {
    "text": "This is a question",
    "my_join_field": "question" 
  }
  
  PUT join_multi_index/_doc/2?refresh
  {
    "text": "This is another question",
    "my_join_field": "question"
  }
  
  # 添加answer记录
  PUT join_multi_index/_doc/3?routing=1&refresh
  {
    "text": "This is a answer",
    "my_join_field": {
      "name": "answer",
      "parent": "1"
    }
  }
  
  PUT join_multi_index/_doc/4?routing=2&refresh
  {
    "text": "This is another answer",
    "my_join_field": {
      "name": "answer",
      "parent": "2"
    }
  }
  
  # 孙子节点vote记录
  PUT join_multi_index/_doc/5?routing=2&refresh
  {
    "text": "This is a vote",
    "my_join_field": {
      "name": "vote",
      "parent": "4"
    }
  }
  
  # 由简入繁的查询
  GET join_multi_index/_search
  {
    "query": {
      "has_child": {
        "type": "answer",
        "query": {
          "match": {
            "text": "This is answer"
          }
        }
      }
    }
  }
  
  
  GET join_multi_index/_search
  {
    "query": {
      "has_child": {
        "type": "answer",
        "query": {
          "has_child": {
            "type": "vote",
            "query": {
              "match": {
                "text": "This is vote"
              }
            }
          }
        }
      }
    }
  }
  
  
  GET join_multi_index/_search
  {
    "query": {
      "bool": {
        "must": [
          {
            "match": {
              "text": "This is question"
            }
          },
          {
            "has_child": {
              "type": "answer",
              "query": {
                "match": {
                  "text": "This is answer"
                }
              }
            }
          },
          {
            "has_child": {
              "type": "answer",
              "query": {
                "has_child": {
                  "type": "vote",
                  "query": {
                    "match": {
                      "text": "This is vote"
                    }
                  }
                }
              }
            }
          }
        ]
      }
    }
  }
  

多条件联合查询

GET join_multi_index/_search
{
  "query": {
    "bool": {
      "must": [
        {
          "has_child": {
            "type": "answer",
            "query": {
              "match": {
                "text": "This is answer"
              }
            }
          }
        },
        {
          "has_child": {
            "type": "answer",
            "query": {
              "has_child": {
                "type": "vote",
                "query": {
                  "match": {
                    "text": "This is vote"
                  }
                }
              }
            }
          }
        }
      ],
      "should": [
        {
          "match": {
            "text": "This is question"
          }
        }
      ]
    }
  }
}

inner_hits的使用

GET join_multi_index/_search
{
  "query": {
    "has_child": {
      "type": "answer",
      "query": {
        "has_child": {
          "type": "vote",
          "query": {
            "match": {
              "text": "This is vote"
            }
          },
          "inner_hits": {}
        }
      },
      "inner_hits": {}
    }
  }
}

返回值

{
  "took" : 5,
  "timed_out" : false,
  "_shards" : {
    "total" : 5,
    "successful" : 5,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : 1,
    "max_score" : 1.0,
    "hits" : [
      {
        "_index" : "join_multi_index",
        "_type" : "_doc",
        "_id" : "2",
        "_score" : 1.0,
        "_source" : {
          "text" : "This is another question",
          "my_join_field" : "question"
        },
        "inner_hits" : {
          "answer" : {
            "hits" : {
              "total" : 1,
              "max_score" : 1.0,
              "hits" : [
                {
                  "_index" : "join_multi_index",
                  "_type" : "_doc",
                  "_id" : "4",
                  "_score" : 1.0,
                  "_routing" : "2",
                  "_source" : {
                    "text" : "This is another answer",
                    "my_join_field" : {
                      "name" : "answer",
                      "parent" : "2"
                    }
                  },
                  "inner_hits" : {
                    "vote" : {
                      "hits" : {
                        "total" : 1,
                        "max_score" : 1.2478919,
                        "hits" : [
                          {
                            "_index" : "join_multi_index",
                            "_type" : "_doc",
                            "_id" : "5",
                            "_score" : 1.2478919,
                            "_routing" : "2",
                            "_source" : {
                              "text" : "This is a vote",
                              "my_join_field" : {
                                "name" : "vote",
                                "parent" : "4"
                              }
                            }
                          }
                        ]
                      }
                    }
                  }
                }
              ]
            }
          }
        }
      }
    ]
  }
}

最终版整理

# 三层数据question->answer->vote

GET three_tree_index/_search

PUT three_tree_index
{
  "mappings": {
    "_doc":{
      "properties":{
        "my_join_field":{
          "type":"join",
          "relations":{
            "question":"answer",
            "answer":"vote"
          }
        }
      }
    }
  }
}

PUT three_tree_index/_doc/1?refresh
{
  "text":"This is a question",
  "my_join_field":"question"
}

PUT three_tree_index/_doc/2?refresh
{
  "text":"This is another question",
  "my_join_field":"question"
}

PUT three_tree_index/_doc/3?routing=1&refresh
{
  "text":"This is a answer",
  "my_join_field":{
    "name":"answer",
    "parent":"1"
  }
}

PUT three_tree_index/_doc/4?routing=2&refresh
{
  "text":"This is another answer",
  "my_join_field":{
    "name":"answer",
    "parent":"2"
  }
}

PUT three_tree_index/_doc/5?routing=1&refresh
{
  "text":"This is a vote",
  "my_join_field":{
    "name":"vote",
    "parent":"3"
  }
}

PUT three_tree_index/_doc/6?routing=2&refresh
{
  "text":"This is another vote",
  "my_join_field":{
    "name":"vote",
    "parent":"4"
  }
}

#-----------------------------------开始查询

GET three_tree_index/_search
{
  "query": {
    "has_child": {
      "type": "answer",
      "query": {
        "match": {
          "text": "This is answer"
        }
      }
    }
  }
}


GET three_tree_index/_search
{
  "query": {
    "bool": {
      "should": [
        { "match": { "text":  "This is answer" }},
        { "match": { "texts": "This is answer"   }}
      ]
    }
  }
}

# question->answer->vote

GET three_tree_index/_search
{
  "query": {
    "bool": {
      "should": [
        {
          "match": {
            "text": "this is question"
          }
        },
        {
          "match": {
            "text": "this is question"
          }
        },
        {
          "has_child": {
            "type": "answer",
            "query": {
              "bool": {
                "should": [
                  {
                    "match": {
                      "text": "this is answer"
                    }
                  },
                  {
                    "match": {
                      "text": "this is answer"
                    }
                  }
                ]
              }
            }
          }
        }
      ],
      "must": [
        {
          "has_child": {
            "type": "answer",
            "query": {
              "has_child": {
                "type": "vote",
                "query": {
                  "bool": {
                    "should": [
                      {
                        "term": {
                          "text": "vote"
                        }
                      }
                    ]
                  }
                }
              }
            }
          }
        }
      ]
    }
  }
}