Kibana Query - match and not eixsts on same field in a single query

Elastic Stack Elasticsearch
1

i've a index document field called "historicalData". Usually it has a value set [true,false]. But few documents of an index are not having the above said field "historicalData" at all. Wanted to query the documents with the below conditions. Please help out:

documents with historicalData='false' and no field "historicalData".

Index has 5 documents:
2 documents -> with historicalData='true'
2 documents -> with historicalData ='false'
1 document -> no field exists with historicalData

my query should return 3 documents.

2

Welcome!

What about a bool query which has a must clause with a term query on historicalData with value false and a must_not clause with an exist query on field historicalData?

3

thanks for the response. Used below query but no use: index name is enrl

GET /enrl/_search
{
"query": {
"bool": {
"must": [
{"term": {
"FIELD": {
"historicalData": "false"
}
}}
],
"must_not": [
{"exists": {
"field": "historicalData"
}}
]
}
}
}

output of the query is:
{
"error": {
"root_cause": [
{
"type": "parsing_exception",
"reason": "[term] query does not support [historicalData]",
"line": 7,
"col": 31
}
],
"type": "parsing_exception",
"reason": "[term] query does not support [historicalData]",
"line": 7,
"col": 31
},
"status": 400
}

4

Please don't post unformatted code, logs, or configuration as it's very hard to read.

Instead, paste the text and format it with </> icon or pairs of triple backticks (```), and check the preview window to make sure it's properly formatted before posting it. This makes it more likely that your question will receive a useful answer.

It would be great if you could update your post to solve this.

There is an example on how to write a Term Query in the documentation: https://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl-term-query.html

You need to fix that part.

If you still can't find the way to write it, could you provide a full recreation script as described in About the Elasticsearch category. It will help to better understand what you are doing. Please, try to keep the example as simple as possible.

A full reproduction script will help readers to understand, reproduce and if needed fix your problem. It will also most likely help to get a faster answer.

5

Sorry.. since this is first time post here, did not check the preview. Below is the code that i've used. and error i see is "parsing exception". Reason mentioned in the error as :"[term] query does not support [historicalData]"

GET /enrollment/_search
{
  "query": {
    "bool": {
      "must": [
        {"term": {
          "FIELD": {
            "historicalData": "false"
          }
        }}
      ],
      "must_not": [
        {"exists": {
          "field": "historicalData"
        }}
      ]
    }
  }
}
6

I answered you already about the term query issue. Please read the documentation I linked to. And...

If you still can't find the way to write it, could you provide a full recreation script.

7

here you go. Thanks a lot for your prompt response.

POST enrl/_bulk
  {"index":{"_id":200}}
  {"name":"Espresso Machine","price":199,"in_stock":5,"historicalData":false}
  {"index":{"_id":201}}
  {"name":"Milk Frother","price":250,"in_stock":15,"historicalData":true}
  {"index":{"_id":202}}
  {"name":"Toaster","price":199,"in_stock":35,"historicalData":false}
  {"index":{"_id":203}}
  {"name":"Appetizer","price":200,"in_stock":25,"historicalData":true}
  {"index":{"_id":204}}
  {"name":"Mixer","price":300,"in_stock":5}


GET enrl/_search
{
  "query": {
    "bool": {
      "must": [
        {"term": {
          "historicalData": {
            "value": "false"
          }
        }}
      ],
      "must_not": [
        {"exists": {
          "field": "historicalData"
        }}
      ]
    }
  }
}
8

So I guess you wanted to write:

documents with historicalData='false' OR no field "historicalData".

In which case, you can do:

DELETE enrl
POST enrl/_bulk
{"index":{"_id":200}}
{"name":"Espresso Machine","price":199,"in_stock":5,"historicalData":false}
{"index":{"_id":201}}
{"name":"Milk Frother","price":250,"in_stock":15,"historicalData":true}
{"index":{"_id":202}}
{"name":"Toaster","price":199,"in_stock":35,"historicalData":false}
{"index":{"_id":203}}
{"name":"Appetizer","price":200,"in_stock":25,"historicalData":true}
{"index":{"_id":204}}
{"name":"Mixer","price":300,"in_stock":5}


GET enrl/_search
{
  "query": {
    "bool": {
      "should": [
        {
          "term": {
            "historicalData": {
              "value": false
            }
          }
        },
        {
          "bool": {
            "must_not": [
              {
                "exists": {
                  "field": "historicalData"
                }
              }
            ]
          }
        }
      ]
    }
  }
}

Which gives:

{
  "took" : 4,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 3,
      "relation" : "eq"
    },
    "max_score" : 0.6931472,
    "hits" : [
      {
        "_index" : "enrl",
        "_type" : "_doc",
        "_id" : "200",
        "_score" : 0.6931472,
        "_source" : {
          "name" : "Espresso Machine",
          "price" : 199,
          "in_stock" : 5,
          "historicalData" : false
        }
      },
      {
        "_index" : "enrl",
        "_type" : "_doc",
        "_id" : "202",
        "_score" : 0.6931472,
        "_source" : {
          "name" : "Toaster",
          "price" : 199,
          "in_stock" : 35,
          "historicalData" : false
        }
      },
      {
        "_index" : "enrl",
        "_type" : "_doc",
        "_id" : "204",
        "_score" : 0.0,
        "_source" : {
          "name" : "Mixer",
          "price" : 300,
          "in_stock" : 5
        }
      }
    ]
  }
}
1 Like
9

Thanks a lot.. you made my day!! that's it i wanted

10

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.