elasticsearch - ElasticSearch适用范围和范围

Question

我的情况:

我正在使用ElasticSearch数据库，因此无法应用几个“OR”加上几个“AND”。我正在编写SQL查询以显示我想要的内容，在我的SQL查询中，我使用了confirmedPlayers和pendingPlayers，因为它们是数组，当然我知道我们无法在SQL中做到这一点，但我只是想举一个例子。

如果您想让我添加我的映射，我会的，只是我不想发表过多的帖子。

这是我在SQL中的查询:

SELECT *
  FROM match
 WHERE (
         "AVnJOMvXOX1s7Ny2Wu9O" in confirmedPlayers OR
         "AVnJOMvXOX1s7Ny2Wu9O" in pendingPlayers OR
         "AVnJOMvXOX1s7Ny2Wu9O" = creator
       )
   AND date >= "20/01/2016"
   /* AND other filter will be added */

这是我的比赛类型信息:

{
   "took": 79,
   "timed_out": false,
   "_shards": {
      "total": 5,
      "successful": 5,
      "failed": 0
   },
   "hits": {
      "total": 8,
      "max_score": 1,
      "hits": [
         {
            "_index": "yojuego",
            "_type": "match",
            "_id": "AVmak0bWIjogo0aNpbGs",
            "_score": 1,
            "_source": {
               "title": "Mi primer match",
               "date": "2016-01-13T20:31:20.000Z",
               "fromTime": "19:00",
               "toTime": "20:00",
               "location": "casa de pablo",
               "creator": "AVmabq-5Ijogo0aNpbGn",
               "matchType": "5",
               "confirmedPlayers": [],
               "pendingPlayers": [],
               "comments": []
            }
         },
         {
            "_index": "yojuego",
            "_type": "match",
            "_id": "AVm0ETbT0Y26YggShbFa",
            "_score": 1,
            "_source": {
               "title": "Mi primer match",
               "date": "2016-01-13T20:31:20.000Z",
               "fromTime": "19:00",
               "toTime": "20:00",
               "location": "casa de pablo",
               "creator": "AVmabVjUIjogo0aNpbGm",
               "matchType": "5",
               "confirmedPlayers": [],
               "pendingPlayers": [
                  "AVmBKi21XRKVuACJGZZZ",
                  "AVmabq-5Ijogo0aNpbGn"
               ],
               "comments": []
            }
         },
         {
            "_index": "yojuego",
            "_type": "match",
            "_id": "AVmab1G5Ijogo0aNpbGo",
            "_score": 1,
            "_source": {
               "title": "Mi primer match",
               "date": "2016-01-13T20:31:20.000Z",
               "fromTime": "19:00",
               "toTime": "20:00",
               "location": "casa de pablo",
               "creator": "AVmabVjUIjogo0aNpbGm",
               "matchType": "5",
               "confirmedPlayers": [
                  "AVmabVjUIjogo0aNpbGm",
                  "AVmBKi21XRKVuACJGZZZ"
               ],
               "pendingPlayers": [
                  "AVmBKi21XRKVuACJGZZZ"
               ],
               "comments": []
            }
         },
         {
            "_index": "yojuego",
            "_type": "match",
            "_id": "AVm0EPX20Y26YggShbFZ",
            "_score": 1,
            "_source": {
               "title": "Mi primer match",
               "date": "2016-01-13T20:31:20.000Z",
               "fromTime": "19:00",
               "toTime": "20:00",
               "location": "casa de pablo",
               "creator": "AVmabVjUIjogo0aNpbGm",
               "matchType": "5",
               "confirmedPlayers": [
                  "AVmabVjUIjogo0aNpbGm",
                  "AVmabq-5Ijogo0aNpbGn"
               ],
               "pendingPlayers": [
                  "AVmBKi21XRKVuACJGZZZ"
               ],
               "comments": []
            }
         },
         {
            "_index": "yojuego",
            "_type": "match",
            "_id": "match",
            "_score": 1,
            "_source": {
               "title": "otro match 3",
               "date": "2017-12-28T00:00:00.000Z",
               "fromTime": "21:00",
               "toTime": "22:00",
               "location": "somewhere",
               "creator": "AVnJOMvXOX1s7Ny2Wu9O",
               "matchType": "5",
               "confirmedPlayers": [],
               "pendingPlayers": [],
               "comments": []
            }
         },
         {
            "_index": "yojuego",
            "_type": "match",
            "_id": "AVnm-9fOJxj9yxI50RS3",
            "_score": 1,
            "_source": {
               "title": "otro match 3",
               "date": "2017-12-28T00:00:00.000Z",
               "fromTime": "21:00",
               "toTime": "22:00",
               "location": "somewhere",
               "creator": "AVmabVjUIjogo0aNpbGm",
               "matchType": "5",
               "confirmedPlayers": [],
               "pendingPlayers": [
                  "AVnJOMvXOX1s7Ny2Wu9O"
               ],
               "comments": []
            }
         },
         {
            "_index": "yojuego",
            "_type": "match",
            "_id": "AVnm-ykMJxj9yxI50RS1",
            "_score": 1,
            "_source": {
               "title": "otro match 3",
               "date": "2017-12-28T00:00:00.000Z",
               "fromTime": "21:00",
               "toTime": "22:00",
               "location": "somewhere",
               "creator": "AVnJOMvXOX1s7Ny2Wu9O",
               "matchType": "5",
               "confirmedPlayers": [],
               "pendingPlayers": [],
               "comments": []
            }
         },
         {
            "_index": "yojuego",
            "_type": "match",
            "_id": "AVnm-73OJxj9yxI50RS2",
            "_score": 1,
            "_source": {
               "title": "otro match 3",
               "date": "2017-12-28T00:00:00.000Z",
               "fromTime": "21:00",
               "toTime": "22:00",
               "location": "somewhere",
               "creator": "AVmabVjUIjogo0aNpbGm",
               "matchType": "5",
               "confirmedPlayers": [
                  "AVnJOMvXOX1s7Ny2Wu9O"
               ],
               "pendingPlayers": [],
               "comments": []
            }
         }
      ]
   }
}

该查询返回4个匹配项，并且确定。

http://localhost:9200/my_index/match
POST _search
{
   "query": {
      "bool": {
         "should": [
            { "term": { "confirmedPlayers": { "value": "AVnJOMvXOX1s7Ny2Wu9O" } } },
            { "term": { "pendingPlayers": { "value": "AVnJOMvXOX1s7Ny2Wu9O" } } },
            { "term": { "creator": { "value": "AVnJOMvXOX1s7Ny2Wu9O" } } }
         ],
         "must": [
            { "range": { "date": { "gte": "20/01/2016", "format": "dd/MM/yyyy" } } }
         ]
      }
   }
}
//RESULT    
{
   "took": 7,
   "timed_out": false,
   "_shards": {
      "total": 5,
      "successful": 5,
      "failed": 0
   },
   "hits": {
      "total": 4,
      "max_score": 1.6931472,
      "hits": [
         {
            "_index": "yojuego",
            "_type": "match",
            "_id": "match",
            "_score": 1.6931472,
            "_source": {
               "title": "otro match 3",
               "date": "2017-12-28T00:00:00.000Z",
               "fromTime": "21:00",
               "toTime": "22:00",
               "location": "somewhere",
               "creator": "AVnJOMvXOX1s7Ny2Wu9O",
               "matchType": "5",
               "confirmedPlayers": [],
               "pendingPlayers": [],
               "comments": []
            }
         },
         {
            "_index": "yojuego",
            "_type": "match",
            "_id": "AVnm-73OJxj9yxI50RS2",
            "_score": 1.6931472,
            "_source": {
               "title": "otro match 3",
               "date": "2017-12-28T00:00:00.000Z",
               "fromTime": "21:00",
               "toTime": "22:00",
               "location": "somewhere",
               "creator": "AVmabVjUIjogo0aNpbGm",
               "matchType": "5",
               "confirmedPlayers": [
                  "AVnJOMvXOX1s7Ny2Wu9O"
               ],
               "pendingPlayers": [],
               "comments": []
            }
         },
         {
            "_index": "yojuego",
            "_type": "match",
            "_id": "AVnm-9fOJxj9yxI50RS3",
            "_score": 1.287682,
            "_source": {
               "title": "otro match 3",
               "date": "2017-12-28T00:00:00.000Z",
               "fromTime": "21:00",
               "toTime": "22:00",
               "location": "somewhere",
               "creator": "AVmabVjUIjogo0aNpbGm",
               "matchType": "5",
               "confirmedPlayers": [],
               "pendingPlayers": [
                  "AVnJOMvXOX1s7Ny2Wu9O"
               ],
               "comments": []
            }
         },
         {
            "_index": "yojuego",
            "_type": "match",
            "_id": "AVnm-ykMJxj9yxI50RS1",
            "_score": 1.287682,
            "_source": {
               "title": "otro match 3",
               "date": "2017-12-28T00:00:00.000Z",
               "fromTime": "21:00",
               "toTime": "22:00",
               "location": "somewhere",
               "creator": "AVnJOMvXOX1s7Ny2Wu9O",
               "matchType": "5",
               "confirmedPlayers": [],
               "pendingPlayers": [],
               "comments": []
            }
         }
      ]
   }
}

但是此查询也返回4个匹配项，在这种情况下，它不应返回任何内容。

POST _search
{
   "query": {
      "bool": {
         "should": [
            { "term": { "confirmedPlayers": { "value": "inexistant" } } },
            { "term": { "pendingPlayers": { "value": "inexistant" } } },
            { "term": { "creator": { "value": "inexistant" } } }
         ],
         "must": [
            { "range": { "date": { "gte": "20/01/2016", "format": "dd/MM/yyyy" } } }
         ]
      }
   }
}
//RESULT
{
   "took": 7,
   "timed_out": false,
   "_shards": {
      "total": 5,
      "successful": 5,
      "failed": 0
   },
   "hits": {
      "total": 4,
      "max_score": 1,
      "hits": [
         {
            "_index": "yojuego",
            "_type": "match",
            "_id": "match",
            "_score": 1,
            "_source": {
               "title": "otro match 3",
               "date": "2017-12-28T00:00:00.000Z",
               "fromTime": "21:00",
               "toTime": "22:00",
               "location": "somewhere",
               "creator": "AVnJOMvXOX1s7Ny2Wu9O",
               "matchType": "5",
               "confirmedPlayers": [],
               "pendingPlayers": [],
               "comments": []
            }
         },
         {
            "_index": "yojuego",
            "_type": "match",
            "_id": "AVnm-9fOJxj9yxI50RS3",
            "_score": 1,
            "_source": {
               "title": "otro match 3",
               "date": "2017-12-28T00:00:00.000Z",
               "fromTime": "21:00",
               "toTime": "22:00",
               "location": "somewhere",
               "creator": "AVmabVjUIjogo0aNpbGm",
               "matchType": "5",
               "confirmedPlayers": [],
               "pendingPlayers": [
                  "AVnJOMvXOX1s7Ny2Wu9O"
               ],
               "comments": []
            }
         },
         {
            "_index": "yojuego",
            "_type": "match",
            "_id": "AVnm-ykMJxj9yxI50RS1",
            "_score": 1,
            "_source": {
               "title": "otro match 3",
               "date": "2017-12-28T00:00:00.000Z",
               "fromTime": "21:00",
               "toTime": "22:00",
               "location": "somewhere",
               "creator": "AVnJOMvXOX1s7Ny2Wu9O",
               "matchType": "5",
               "confirmedPlayers": [],
               "pendingPlayers": [],
               "comments": []
            }
         },
         {
            "_index": "yojuego",
            "_type": "match",
            "_id": "AVnm-73OJxj9yxI50RS2",
            "_score": 1,
            "_source": {
               "title": "otro match 3",
               "date": "2017-12-28T00:00:00.000Z",
               "fromTime": "21:00",
               "toTime": "22:00",
               "location": "somewhere",
               "creator": "AVmabVjUIjogo0aNpbGm",
               "matchType": "5",
               "confirmedPlayers": [
                  "AVnJOMvXOX1s7Ny2Wu9O"
               ],
               "pendingPlayers": [],
               "comments": []
            }
         }
      ]
   }
}

映射:

{
    "match": {
        "properties": {
            "title": { "type": "string" },
            "date": { "type": "date" },
            "fromTime": { "type": "string" },
            "toTime": { "type": "string" },
            "location": { "type": "string" },
            "matchType": { "type": "integer" },
            "creator": {
                "type": "string",
                "index": "not_analyzed"
            },
            "confirmedPlayers" : {
                "type": "string",
                "index": "not_analyzed"
            },
            "pendingPlayers" : {
                "type": "string",
                "index": "not_analyzed"
            },
            "comments" : {
                "properties" : {
                    "id" : { "type" : "integer" },
                    "owner" : { "type" : "string" },
                    "text" : { "type" : "string" },
                    "writtenOn": { "type": "date" }
                }
            }
        }
    }
}

当我使用所有必需品时，都会出现问题。如果我使用应该和必须分别使用它们，它们可以正常工作。

Answer 1

根据第二个示例查询的结果(您声称应返回0个结果)，似乎对should在elasticsearch中的工作方式有些困惑。

我会引用the documentation

should

The clause (query) should appear in the matching document. In a boolean query with no must or filter clauses, one or more should clauses must match a document. The minimum number of should clauses to match can be set using the minimum_should_match parameter.

如果您使用带有 should和 must的查询，则实际上并不需要 should子句命中，只有 must子句命中。如果 should子句确实命中，它们将在结果中排​​名更高。

你有选择。一种选择:您可以编写一个简单的 should查询，并设置 minimum_should_match参数，然后将该查询包装在 filtered子句中以根据日期进行过滤。第二种选择:创建一个嵌套查询，并在 must子句中包含 should子句。