CODEWITHSUNDEEP
elasticsearch
oderfla
oderfla

Reputation: 1797

Must not with and in elastic search

I have 4 fields in an elastic search schema.

date
status
type
createdAt

Now, I need to fetch all the rows where 

date=today
status = "confirmed" 
and where type is not equals to "def"

However, it is ok if 

type=def exists

but only when the field createdAt is not equals to today.

My current query looks like this:

{
    must: [
        { "bool":
            {
                "must": [
                    {"term": {"date": 'now/d'}},
                    {"term": {"status": 'confirmed'}},
                ]
          }
        }
    ],
    mustNot: [
        {"match": {'createdAt': 'now/d'}},
        {"match":{"type": "def"}}
    ]
}

The rows where type is not equals to "def" are fetched. However, if a row has the type=def AND createdAT any date but today, then the row doesn't show up.

What am I doing wrong?

Upvotes: 0

Views: 9617

Answers (2)

Vincent
Vincent

Reputation: 1651

Assuming a setup like this:

PUT twitter
{
  "settings": {
    "index": {
      "number_of_shards": 1,
      "number_of_replicas": 0
    }
  },
  "mappings": {
    "_doc": {
      "properties": {
        "date": {
          "type": "date",
          "format": "epoch_millis"
        },
        "createdAt": {
          "type": "date",
          "format": "epoch_millis"
        },
        "status": {
          "type": "keyword"
        },
        "type": {
          "type": "keyword"
        }
      }
    }
  }
}

and a sample doc like this (adjust values to test the query): 

post twitter/_doc/1
{
 "date": 1536562800000, //start of TODAY September 10, 2018 in UTC
 "createdAt": 1536562799999,
 "status": "confirmed",
 "type": "def"
}

the following query should work:

get twitter/_search
{
  "query": {
    "bool": {
      "filter": {
        "bool": {
          "must": [
            {
              "range": {
                "date": {
                  "gte": "now/d",
                  "lte": "now/d"
                }
              }
            },
            {
              "term": {
                "status": "confirmed"
              }
            }
          ],
          "must_not": [
            {
              "range": {
                "createdAt": {
                  "gte": "now/d",
                  "lte": "now/d"
                }
              }
            },
            {
              "term": {
                "type": "def"
              }
            }
          ]
        }
      }
    }
  }
}

This is a filtered query which i think for this scenario is better because it doesn't calculate the score. If you do want to calculate the score, just remove the bool and the filter from the top.

Upvotes: 1

wanton
wanton

Reputation: 365

This query should work.

{
  "query": {
    "bool": {
       "must": [
          { "term": {"date": "now/d" } },
          { "term": {"status": "confirmed" } }
       ],
       "must_not": {
         "bool": {
           "must": [
              { "match": { "createdAt": "now/d" } },
              { "match": { "type": "def" } }
           ]
         }
       }
    }
  } 
}

I believe the reason that your version is not working is that every query in the must_not must not match. https://www.elastic.co/guide/en/elasticsearch/guide/current/bool-query.html#_controlling_precision

All the must clauses must match, and all the must_not clauses must not match, but how many should clauses should match? By default, none of the should clauses are required to match, with one exception: if there are no must clauses, then at least one should clause must match.

Upvotes: 5

Related Questions