# Match entities where field with specific value does not exists

**URL:** <https://discuss.elastic.co/t/match-entities-where-field-with-specific-value-does-not-exists/280902>\
**Category:** Elasticsearch\
**Created:** [August 10, 2021, 9:32am UTC](https://discuss.elastic.co/t/match-entities-where-field-with-specific-value-does-not-exists/280902 "2021-08-10T09:32:50Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![farkasaba](https://avatars.discourse-cdn.com/v4/letter/f/ebca7d/32.png) [@farkasaba](https://discuss.elastic.co/u/farkasaba)\
**Post date:** [August 10, 2021, 9:32am UTC](https://discuss.elastic.co/t/match-entities-where-field-with-specific-value-does-not-exists/280902/1 "2021-08-10T09:32:50Z")

</div>

Hello there,

I have my documents indexed in the following format:

```auto
{
...
"special_id"=1,
"phase"="create"
},
{
...
"special_id"=1,
"phase"="update"
},
{
...
"special_id"=2,
"phase"="update"
},
{
...
"special_id"=3,
"phase"="create"
},
{
...
"special_id"=3,
"phase"="update"
}

```

I would like to obtain a list of `special_id` s, where there are no documents with `"phase" = "create"`. Is there a way to obtain such list without external scripts?  
Thanks!  
Aba

---

<div class="post-metadata">

**Author:** ![farkasaba](https://avatars.discourse-cdn.com/v4/letter/f/ebca7d/32.png) [@farkasaba](https://discuss.elastic.co/u/farkasaba)\
**Post date:** [August 10, 2021, 11:38am UTC](https://discuss.elastic.co/t/match-entities-where-field-with-specific-value-does-not-exists/280902/2 "2021-08-10T11:38:17Z")

</div>

What I've tried so far:

```auto
GET my_index/_search
{
  "size": 0,
  "aggs": {
    "All_IDs": {
      "terms": {
        "field": "special_id",
        "size": 10
      },
      "aggs": {
        "phasetype": {
          "terms": {
            "field": "phase",
            "size": 10
          }
        }
      }
    }
  }
}

```

What I don't know how to do, is to extract the values, where `"doc_count" = 0` for the `"key" = "create"`. I think it would be enough if I could sort by `"All_IDs.phasetype.doc_count"`, but I have no idea how.

---

<div class="post-metadata">

**Author:** ![farkasaba](https://avatars.discourse-cdn.com/v4/letter/f/ebca7d/32.png) [@farkasaba](https://discuss.elastic.co/u/farkasaba)\
**Post date:** [August 10, 2021, 7:46pm UTC](https://discuss.elastic.co/t/match-entities-where-field-with-specific-value-does-not-exists/280902/3 "2021-08-10T19:46:49Z")

</div>

I made some progress in the meantime:

```auto
POST my_index/_search
{
  "size": 0,
  "query": {
    "bool": {
      "must": [
        {
          "terms": {
            "special_id": ["423580","423581","423582"]
          }
        }
      ]
    }
  },
  "aggs": {
    "All_IDs": {
      "terms": {
        "field": "special_id",
        "size": 10000
      },
      "aggs": {
        "filter_thing": {
          "filters": {
            "filters": {
              "phase_create": {"match": {"phase": "create"}},
              "phase_update": {"match": {"phase": "update"}}
            }
          },
          "aggs": {
            "my_bucket_selector": {
              "bucket_selector": {
                "buckets_path": {
                  "phaseCreate": "_count"
                },
                "script": "params.phaseCreate == 0"
              }
            }
          }
        }
      }
    }
  }
}

```

There is still a problem with this approach. I want to have only those values in the result, where the `doc_count = 0`, but this one still prints everything. See below result:

```auto
"aggregations" : {
    "All_IDs" : {
      "doc_count_error_upper_bound" : 0,
      "sum_other_doc_count" : 0,
      "buckets" : [
        {
          "key" : "423580",
          "doc_count" : 16,
          "filter_thing" : {
            "buckets" : {
              "phase_create" : {
                "doc_count" : 0
              }
            }
          }
        },
        {
          "key" : "423582",
          "doc_count" : 16,
          "filter_thing" : {
            "buckets" : { }
          }
        },
        {
          "key" : "423581",
          "doc_count" : 6,
          "filter_thing" : {
            "buckets" : {
              "phase_create" : {
                "doc_count" : 0
              }
            }
          }
        }
      ]
    }
  }

```

For this specific set of `special_id` s, `423582` should not appear. How can I achieve this?

---

<div class="post-metadata">

**Author:** ![BenTrent](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/bentrent/32/33915_2.png) [@BenTrent](https://discuss.elastic.co/u/BenTrent)\
**Post date:** [August 10, 2021, 8:09pm UTC](https://discuss.elastic.co/t/match-entities-where-field-with-specific-value-does-not-exists/280902/4 "2021-08-10T20:09:52Z")

</div>

Here is a minimal repeatable example. This will filter out `special_id` buckets based on the count of values that match the `create` phase. Specifically, when `count` is `0`

```auto
# Create the index
PUT test_index
{
  "mappings": {
    "properties": {
      "special_id": {
        "type": "long"
      },
      "phase": {
        "type": "keyword"
      }
    }
  }
}

# Index some test data
POST test_index/_bulk?refresh=true
{"index":{}}
{"special_id":1,"phase":"create"}
{"index":{}}
{"special_id":1,"phase":"update"}
{"index":{}}
{"special_id":2,"phase":"update"}
{"index":{}}
{"special_id":3,"phase":"create"}
{"index":{}}
{"special_id":3,"phase":"update"}

# Search it
POST test_index/_search
{
  "size": 0,
  "aggs": {
    "All_IDs": {
      "terms": {
        "field": "special_id",
        "include": [
          1,
          2
        ],
        "size": 10000
      },
      "aggs": {
        "filter_thing": {
          "filters": {
            "filters": {
              "phase_create": {
                "match": {
                  "phase": "create"
                }
              },
              "phase_update": {
                "match": {
                  "phase": "update"
                }
              }
            }
          },
          "aggs": {
            "count": {
              "value_count": {
                "field": "special_id"
              }
            }
          }
        },
        "select": {
          "bucket_selector": {
            "buckets_path": {
              "c": "filter_thing['phase_create']>count"
            },
            "script": "params.c==0"
          }
        }
      }
    }
  }
}

```

Please note:

- Terms aggs supports the `include` parameter. This is much more performant than adding your own top level filter if you are only filtering on those term values
- bucket\_selector is a sub agg for the `ALL_IDs` and is filtering based on the sub-agg within the `phase_create`aggregation

Honestly, it seems to me that the `_count` path should work with `filters` aggregations. I am not 100% sure why it doesn't but that smells like a bug to me.

---

<div class="post-metadata">

**Author:** ![farkasaba](https://avatars.discourse-cdn.com/v4/letter/f/ebca7d/32.png) [@farkasaba](https://discuss.elastic.co/u/farkasaba)\
**Post date:** [August 10, 2021, 8:52pm UTC](https://discuss.elastic.co/t/match-entities-where-field-with-specific-value-does-not-exists/280902/5 "2021-08-10T20:52:40Z")

</div>

In the meantime I also came up with a very complicated solution, which seems to work, but yours is looking better. Here is mine:

```auto
POST my_index/_search
{
  "size": 0,
  "aggs": {
    "All_IDs": {
      "terms": {
        "field": "special_id",
        "size": 20000
      },
      "aggs": {
        "filter_thing": {
          "filters": {
            "filters": {
              "phase_create": {"match": {"phase": "create"}}
            }
          },
          "aggs": {
            "my_bucket_selector": {
              "bucket_selector": {
                "buckets_path": {
                  "phaseCreate": "_count"
                },
                "script": "params.phaseCreate == 0"
              }
            }
          }
        },
        "min_bucket_count": {
          "bucket_selector": {
            "buckets_path": {
              "count": "filter_thing._bucket_count"
            },
            "script": "params.count != 0"
          }
        }
      }
    }
  }
}

```

I removed the `"phaseUpdate"` filter, so I can use the `_count` and I added a second `bucket_selector` to filter out the empty buckets.

Anyhow, your solution looks better. Thank you very much for all your help!

Now the only problem which remains to be solved is to use this `_search` on all the IDs (there are approximately 500000), and all the documents (approx 10million documents). I think I should split it somehow by `special_id` intervals. The only issue is, that `special_id` is really special. It is `keyword` ☹ .

---

<div class="post-metadata">

**Author:** ![Mark\_Harwood](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mark_harwood/32/10538_2.png) [@Mark\_Harwood](https://discuss.elastic.co/u/Mark_Harwood)\
**Post date:** [August 11, 2021, 8:32am UTC](https://discuss.elastic.co/t/match-entities-where-field-with-specific-value-does-not-exists/280902/6 "2021-08-11T08:32:09Z")

</div>

Any system that tries to get a holistic view of each entity is going to struggle if the related data for each entity is distributed somewhat randomly across multiple machines.

The `terms` aggregation is useful for grouping together related items under a common key but will [fail to do so reliably](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-bucket-terms-aggregation.html#terms-agg-doc-count-error) if there are very large numbers of keys spread across multiple machines. Not all data nodes may agree exactly on which keys to gather for final analysis on the search coordinating node, meaning some data may be missing for keys presented in the final result.

Your sort of behavioural analysis looks like a classic use case for an entity centric index created using the [transforms api](https://www.elastic.co/guide/en/elasticsearch/reference/current/transforms.html).

---

<div class="post-metadata">

**Author:** ![farkasaba](https://avatars.discourse-cdn.com/v4/letter/f/ebca7d/32.png) [@farkasaba](https://discuss.elastic.co/u/farkasaba)\
**Post date:** [August 11, 2021, 9:05am UTC](https://discuss.elastic.co/t/match-entities-where-field-with-specific-value-does-not-exists/280902/7 "2021-08-11T09:05:40Z")

</div>

My data is not yet distributed across multiple machines. This is index has only 1 shard.

```auto
GET _cat/shards/restored_prod_event_store20210729?v

index shard prirep state docs store ip node
my_index 0 p STARTED 450548050 37.9gb 123.123.123.123 test-node-3

```

Sidenote: The doc count is `450548050`, however the number of documents I am interested in is actually smaller. (I am also filtering based on another field.)  
See below result for the search call written above in the earlier posts:

```auto
{
  "took" : 431,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 10000,
      "relation" : "gte"
    },
    "max_score" : null,
    "hits" : []
  },
  "aggregations" : {
    "All_IDs" : {
      "doc_count_error_upper_bound" : 0,
      "sum_other_doc_count" : 9846594,
      "buckets" : []
    }
  }
}

```

But I understand what you are saying, the `"relation" : "gte"`is clearly not ok for me, and the `"sum_other_doc_count" : 9846594` is also way too high. I will review the `transforms api` as you suggested, and I hope I can figure something out. Any other tips which can help me on the way are very much appreciated. Thank you for your help!

---

<div class="post-metadata">

**Author:** ![Mark\_Harwood](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mark_harwood/32/10538_2.png) [@Mark\_Harwood](https://discuss.elastic.co/u/Mark_Harwood)\
**Post date:** [August 11, 2021, 9:18am UTC](https://discuss.elastic.co/t/match-entities-where-field-with-specific-value-does-not-exists/280902/8 "2021-08-11T09:18:44Z")

</div>

What you may find is that by transforming low-level log records into entities you can do a lot more interesting analysis e.g. average length of time taken for different phases, max number of phase changes per entity etc.  
Derived entity attributes like "duration" or "lastKnownState" are typically useful things the transform API can help with. It shifts the computational cost and complexity of doing this sort of analysis from query-time to index-time.

---

<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:** [September 8, 2021, 9:19am UTC](https://discuss.elastic.co/t/match-entities-where-field-with-specific-value-does-not-exists/280902/9 "2021-09-08T09:19:15Z")

</div>

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