# Creating a transform with must\_not bool not working as expected

**URL:** <https://discuss.elastic.co/t/creating-a-transform-with-must-not-bool-not-working-as-expected/267330>\
**Category:** Elasticsearch\
**Tags:** transforms\
**Created:** [March 16, 2021, 8:25am UTC](https://discuss.elastic.co/t/creating-a-transform-with-must-not-bool-not-working-as-expected/267330 "2021-03-16T08:25:39Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![vigneshr35](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/vigneshr35/32/32636_2.png) [@vigneshr35](https://discuss.elastic.co/u/vigneshr35)\
**Post date:** [March 16, 2021, 8:25am UTC](https://discuss.elastic.co/t/creating-a-transform-with-must-not-bool-not-working-as-expected/267330/1 "2021-03-16T08:25:39Z")

</div>

Hi everyone,

I am trying to create a transform and facing some issues with using the must\_not for a specific condition I have. Here are the details:

Source index sample document:

```auto
{
  "app.status": "COMPLETED",
   "@timestamp": "2021-03-15T00:01:01.013Z"
}

```

Possible values for _app.status_ are COMPLETED, TERMINATED, SUSPENDED or RETRY

I want to apply a transform that pulls only the documents that have _app.status_ other than COMPLETED or TERMINATED.

Here is the full bool I tried using

```auto
{
    "must_not": [
        {"term": {"app.status": "COMPLETED" }},
        {"term": {"app.status": "TERMINATED" }}
    ]
}    

```

But I am not getting the right result. I verified this by checking the number of records that match the condition from the discover screen.

When I use a must as shown below I am able to accomplish the same. But there is a possibility that _app.status_ can have other different values in future. So I will not need to update my transform in the future if I can accomplish this with must\_not instead of should.

```auto
"bool": {
            "should": [
              {
                "term": {
                  "app.status": "RETRY"
                }
              },
              {
                "term": {
                  "app.status": "SUSPENDED"
                }
              }
            ],
            "minimum_should_match": 1
          }

```

Can someone please help me out here.

---

<div class="post-metadata">

**Author:** ![przemekwitek](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/przemekwitek/32/79526_2.png) [@przemekwitek](https://discuss.elastic.co/u/przemekwitek)\
**Post date:** [March 16, 2021, 9:26am UTC](https://discuss.elastic.co/t/creating-a-transform-with-must-not-bool-not-working-as-expected/267330/2 "2021-03-16T09:26:32Z")

</div>

> [@vigneshr35](#):
>
> Hi everyone,
> 
> I am trying to create a transform and facing some issues with using the must\_not for a specific condition I have. Here are the details:
> 
> Source index sample document:
> 
> ```auto
> {
> "app.status": "COMPLETED",
> "@timestamp": "2021-03-15T00:01:01.013Z"
> }
> 
> ```
> 
> Possible values for _app.status_ are COMPLETED, TERMINATED, SUSPENDED or RETRY
> 
> I want to apply a transform that pulls only the documents that have _app.status_ other than COMPLETED or TERMINATED.
> 
> Here is the full bool I tried using
> 
> ```auto
> {
> "must_not": [
> {"term": {"app.status": "COMPLETED" }},
> {"term": {"app.status": "TERMINATED" }}
> ]
> }    
> 
> ```
> 
> But I am not getting the right result.

The query looks good. Just to clarify, are you providing this query in `transform.source.query`? Or in some other place?  
Could you send the full transform config?

I tried to reproduce your issue locally, using the following transform config:

```auto
{
  "source": {
    "index": [
      "my-index"
    ],
    "query": {
      "bool": {
        "must_not": [
          {"term": {"app.status": "COMPLETED"}},
          {"term": {"app.status": "TERMINATED"}}
        ]
      }
    }
  },
  "pivot": {
    "group_by": {
      "app.status": {"terms": { "field": "app.status.keyword" } }
    },
    "aggregations": {
      "max_timestamp": { "max": { "field": "@timestamp" } }
    }
  }
}

```

and the results were as expected.

> [@](#):
>
> I verified this by checking the number of records that match the condition from the discover screen.

Could you clarify what exact numbers were you comparing?

> [@](#):
>
> When I use a must as shown below I am able to accomplish the same. But there is a possibility that _app.status_ can have other different values in future. So I will not need to update my transform in the future if I can accomplish this with must\_not instead of should.

+1 That's definitely a good approach to use exclusions rather than inclusions in this case.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [April 13, 2021, 9:26am UTC](https://discuss.elastic.co/t/creating-a-transform-with-must-not-bool-not-working-as-expected/267330/3 "2021-04-13T09:26:48Z")

</div>

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