elasticsearch - Elasticsearch匹配日期范围或数组中的数字

Question

我的目标是按日期和星期几过滤记录(Mo = 1，Tue = 2，Thu = 3，...，Sun = 7)。在这种情况下，日期或工作日都应与数组中的任何天匹配。或两者兼而有之。我是Elasticsearch的新手，在查询中似乎有很多错误。我已在此处记录了所有内容，并希望能提供一些有用的见解。提前致谢。

当前映射

{
  "index":{
    "mappings":{
      "entity":{
        "_meta":{
           "model":"AppBundle\\Entity\\Entity"
        },
        "properties":{
          "subEntity":{
            "properties":{
              "date":{
                "type":"date",
                "format":"strict_date_optional_time||epoch_millis"
              },
              "days":{
                "properties":{
                  "day":{
                    "type":"string"
                  }
                }
              }
            }
          }
        }
      }
    }
  }
}

当前记录

curl -XGET 'localhost:9200/index/_search?pretty=1'
{
  "took" : 2,
  "timed_out" : false,
  "_shards" : {
    "total" : 5,
    "successful" : 5,
    "failed" : 0
  },
  "hits" : {
    "total" : 4,
    "max_score" : 1.0,
    "hits" : [ {
      "_index" : "index",
      "_type" : "entity",
      "_id" : "1",
      "_score" : 1.0,
      "_source" : {
        "subEntity" : [ {
          "date" : "2016-09-20T00:00:00+02:00",
          "days" : [ ]
        }, {
          "date" : "2016-09-21T00:00:00+02:00",
          "days" : [ ]
        }, {
          "date" : "2016-09-22T00:00:00+02:00",
          "days" : [ {
            "day" : 4
          }, {
            "day" : 5
          }, {
            "day" : 6
          } ]
        }, {
          "date" : "2016-09-20T00:00:00+02:00",
          "days" : [ ]
        } ]
      }
    },
    [...]
  }
}

当前请求

{
  "query":{
    "should":{
      "filter":[ {
        "range":{
          "entity.subEntity.date":{
            "gte":"2016-09-20",
            "lte":"2016-09-21"
          }
        }
      }, {
        "term":{
          "entity.subEntity.days.day": 2
        }
      } ]
    }
  }
}

MySQL等效

SELECT entity
FROM entity
LEFT JOIN subEntity ON (subEntity.entity_id = entity.id)
LEFT JOIN day ON (day.subEntity_id = subEntity.id)
WHERE subEntity.date BETWEEN 2016-09-20 AND 2016-09-21
OR day = 2

Answer 1

如果要查询文档内子对象的属性(文档中可能包含此类子对象的集合)，则需要将subEntity映射为nested type。在您的示例中，由于您只查找或日期范围内的日期与日期值匹配的文档，因此可以像以前一样使用object映射，但是如果您需要将查询与和操作结合使用，则您可以需要一个nested类型映射。如果您需要执行此操作，则将其映射为nested类型是有意义的。此外，由于day是一个数字值，因此应将其映射为byte。

{
  "index":{
    "mappings":{
      "entity":{
        "_meta":{
           "model":"AppBundle\\Entity\\Entity"
        },
        "properties":{
          "subEntity":{
            "type": "nested",
            "properties":{
              "date":{
                "type":"date",
                "format":"strict_date_optional_time||epoch_millis"
              },
              "days":{
                "properties":{
                  "day":{
                    "type":"byte"
                  }
                }
              }
            }
          }
        }
      }
    }
  }
}

现在 subEntity已映射为 nested类型，现在需要使用 a nested query对其进行查询，因此查询变为

{
  "query": {
    "nested": {
      "query": {
        "bool": {
          "should": [
            {
              "bool": {
                "filter": [
                  {
                    "range": {
                      "subEntity.date": {
                        "gte": "2016-09-20",
                        "lte": "2016-09-21"
                      }
                    }
                  }
                ]
              }
            },
            {
              "bool": {
                "filter": [
                  {
                    "terms": {
                      "subEntity.days.day": [
                        2
                      ]
                    }
                  }
                ]
              }
            }
          ]
        }
      },
      "path": "subEntity"
    }
  }
}

这两个查询均以 bool过滤器查询的形式发出，因为我们不需要为它们中的任何一个计算相关性得分，我们只需要知道文档是否匹配即可，即简单的是/否答案。在 bool过滤器中扭曲查询意味着该查询在过滤器上下文中运行。

接下来，两个查询都可以匹配，因此我们将两者作为 should子句添加到外部 bool查询中。

举一个完整的例子:

创建索引和映射

PUT http://localhost:9200/entities?pretty=true 
{
  "settings": {
    "index.number_of_replicas": 0,
    "index.number_of_shards": 1
  },
  "mappings": {
    "entity": {
      "properties": {
        "id": {
          "type": "integer"
        },
        "subEntity": {
          "type": "nested",
          "properties": {
            "date": {
              "type": "date"
            },
            "days": {
              "properties": {
                "day": {
                  "type": "short"
                }
              },
              "type": "object"
            }
          }
        }
      }
    }
  }
}

批量索引四个实体

POST http://localhost:9200/_bulk?pretty=true 
{"index":{"_index":"entities","_type":"entity","_id":"1"}}
{"subEntity":{"date":"2016-09-19T05:00:00+00:00"}}
{"index":{"_index":"entities","_type":"entity","_id":"2"}}
{"subEntity":{"date":"2016-09-20T05:00:00+00:00"}}
{"index":{"_index":"entities","_type":"entity","_id":"3"}}
{"subEntity":{"date":"2016-09-18T18:00:00+00:00","days":[{"day":2},{"day":5}]}}
{"index":{"_index":"entities","_type":"entity","_id":"4"}}
{"subEntity":{"date":"2016-09-18T18:00:00+00:00","days":[{"day":3},{"day":4}]}}

发出上面的搜索查询

POST http://localhost:9200/entities/entity/_search?pretty=true 
{
  "query": {
    "nested": {
      "query": {
        "bool": {
          "should": [
            {
              "bool": {
                "filter": [
                  {
                    "range": {
                      "subEntity.date": {
                        "gte": "2016-09-20",
                        "lte": "2016-09-21"
                      }
                    }
                  }
                ]
              }
            },
            {
              "bool": {
                "filter": [
                  {
                    "terms": {
                      "subEntity.days.day": [
                        2
                      ]
                    }
                  }
                ]
              }
            }
          ]
        }
      },
      "path": "subEntity"
    }
  }
}

我们应该只取回ID为2和3的实体； ID 2日期匹配，ID 3日期匹配

{
  "took" : 4,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "failed" : 0
  },
  "hits" : {
    "total" : 2,
    "max_score" : 0.0,
    "hits" : [ {
      "_index" : "entities",
      "_type" : "entity",
      "_id" : "2",
      "_score" : 0.0,
      "_source" : {
        "subEntity" : {
          "date" : "2016-09-20T05:00:00+00:00"
        }
      }
    }, {
      "_index" : "entities",
      "_type" : "entity",
      "_id" : "3",
      "_score" : 0.0,
      "_source" : {
        "subEntity" : {
          "date" : "2016-09-18T18:00:00+00:00",
          "days" : [ {
            "day" : 2
          }, {
            "day" : 5
          } ]
        }
      }
    } ]
  }
}