Must not apply in must query result


(Dilip Kumar) #1

Need to filter apply document having must FE=5 but if some FE=5 also having FE=17, then ignore those document. Please suggest

"query": {
"bool": {
"must": [
{
"term": {
"FE": {
"value": "5"
}
}
},
{
"range": {
"DT": {
"gte": "2019-04-16",
"lte": "2019-04-16"
}
}
}
],
"must_not": [
{
"term": {
"FE": {
"value": "17"
}
}
}
]
}
}


(David Pilato) #2

Can't really tell but it looks ok.

Please format your code, logs or configuration files using </> icon as explained in this guide and not the citation button. It will make your post more readable.

Or use markdown style like:

```
CODE
```

This is the icon to use if you are not using markdown format:

There's a live preview panel for exactly this reasons.

Lots of people read these forums, and many of them will simply skip over a post that is difficult to read, because it's just too large an investment of their time to try and follow a wall of badly formatted text.
If your goal is to get an answer to your questions, it's in your interest to make it as easy to read and understand as possible.

If you can't make it work I suggest that 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.


(Dilip Kumar) #3
ok i will take care , this query is ok but result is not coming accordingly,
suppose some document having data like this :slight_smile:`

` { CID=1,FE=5 }
{ CID=1,FE=17 }
{ CID=2,FE=5 }

Suppose i have three documents, i need CID only those documents having FE=5 only and not require those having FE=5 and FE17 both

When i have applied my query both records are coming.


(David Pilato) #4

As I said. Provide a script someone can start from to help you.


(Dilip Kumar) #5

I have some records having RE =5 and RE =17 , I need those records only having RE=5 but not those having RE=5 and RE=17 both. Please help:

My sample records :
{

    "_source": {
      "RE": "17",
      "RT": "278",
      "PT": "2019-04-16T02:28:01",
      "DT": "2019-04-16T02:15:18",
      "ZI": "221853184142816817"
    }
  },
  {

    "_source": {
      "RE": "5",
      "RT": "278",
      "PT": "2019-04-16T02:28:01",
      "DT": "2019-04-16T02:15:18",
      "ZI": "221853184142816817"
    }
  },
  {

    "_source": {
      "RE": "5",
      "RT": "278",
      "PT": "2019-04-16T14:28:01",
      "DT": "2019-04-16T14:15:18",
      "ZI": "2218531841428168"
    }
  },
  {
    "_source": {
      "RE": "5",
      "RT": "270",
      "PT": "2019-04-15T14:28:01",
      "DT": "2019-04-15T14:15:18",
      "ZI": "2218531841428168"
    }
  }

My query is
{
"query": {
"bool": {
"must": {
"bool": {
"must_not": [
{
"term": {
"RE": "17"
}
}
],
"must": [
{
"term": {
"RE": "5"
}
}
]
}
}
}
}
}


(David Pilato) #6

I'd probably replace the first must by a should.

Then inside have 2 queries:

  • the bool one you already defined
  • a term query on RE=5

(Dilip Kumar) #7

Thanks for reply , But please help me for query structure


(Dilip Kumar) #8

Hi dadoonet, please view updated query according to your instruction, but still that records is coming having 17 and 5 both

      "query": {
        "bool": {
          "should": [
            {
              "bool": {
                "must": [
                  {
                    "term": {
                      "RE": "5"
                    }
                  }
                ]
              }
            },
            {
              "bool": {
                "must": [
                  {
                    "term": {
                      "RE": "5"
                    }
                  }
                ],
                "must_not": [
                  {
                    "term": {
                      "RE": {
                        "value": "17"
                      }
                    }
                  }
                ]
              }
            }
          ]
        }
      }
    }

(David Pilato) #9

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.


(Dilip Kumar) #10

Yes please i have spent 2 days for this{ "query": { "bool": { "should": [ { "bool": { "must": [ { "term": { "RE": "5" } } ], "must_not": [ { "term": { "RE": { "value": "17" } } } ] } } , { "bool": { "must": [ { "term": { "RE": "5" } } ] } } ] } } }

This records should not come because as you can see ZI=221853184142816817
belongs to those having RE=17

 "_source": {
  "RE": "5",
  "RT": "278",
  "PT": "2019-04-16T02:28:01",
  "DT": "2019-04-16T02:15:18",
  "ZI": "221853184142816817"
}

},


(Dilip Kumar) #11

Hi dadoonet please help for query structure


(David Pilato) #12

You have been wasting a lot of time not giving a script that reproduces your problem. But lucky you, although on holidays, I had some few spare minutes to write one for you.

DELETE test
PUT test/_doc/1
{
  "RE": "5"
}
PUT test/_doc/2
{
  "RE": "17"
}
GET test/_search
{
  "query": {
    "bool": {
      "should": [
        {
          "bool": {
            "must": [
              {
                "term": {
                  "RE": "5"
                }
              }
            ],
            "must_not": [
              {
                "term": {
                  "RE": {
                    "value": "17"
                  }
                }
              }
            ]
          }
        },
        {
          "term": {
            "RE": "5"
          }
        }
      ]
    }
  }
}

This gives:

{
  "took" : 13,
  "timed_out" : false,
  "_shards" : {
    "total" : 5,
    "successful" : 5,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : 1,
    "max_score" : 0.5753642,
    "hits" : [
      {wish
        "_index" : "test",
        "_type" : "_doc",
        "_id" : "1",
        "_score" : 0.5753642,
        "_source" : {
          "RE" : "5"
        }
      }
    ]
  }
}

If this is not what you want, or what you have, and if you need further help, please provide a similar script as I just did if you are looking for help. A script should be just as easy as a copy and paste in Kibana Dev Console and run.


(Dilip Kumar) #13

Thanks for valuable timedadoonet, but please insert sample doc i have given above then i run this query which is giving result
"hits": [
{
"_index": "campevents_test",
"_type": "cmev",
"_id": "221853184142816817_278_5",
"_score": 0.5753642,
"_source": {
"RE": "5",
"RT": "278",
"PT": "2019-04-16T02:28:01",
"DT": "2019-04-16T02:15:18",
"ZI": "221853184142816817"
}
},
{
"_index": "campevents_test",
"_type": "cmev",
"_id": "2218531841428168_278_5",
"_score": 0.36464313,
"_source": {
"RE": "5",
"RT": "278",
"PT": "2019-04-16T14:28:01",
"DT": "2019-04-16T14:15:18",
"ZI": "2218531841428168"
}
},
{
"_index": "campevents_test",
"_type": "cmev",
"_id": "2218531841428168_270_5",
"_score": 0.36464313,
"_source": {
"RE": "5",
"RT": "270",
"PT": "2019-04-15T14:28:01",
"DT": "2019-04-15T14:15:18",
"ZI": "2218531841428168"
}
}
]

My expected result should not have this record
"RE": "5",
"RT": "278",
"PT": "2019-04-16T02:28:01",
"DT": "2019-04-16T02:15:18",
"ZI": "221853184142816817"

because as you can see "ZI": "221853184142816817" this ZI also in RE=17 and now in Re=5 so , i don't need those records which is having both Re=5 and RE=17 both,


(David Pilato) #14

I can't help.