# Nested Aggregation with AND always return 0 match

**URL:** <https://discuss.elastic.co/t/nested-aggregation-with-and-always-return-0-match/315722>\
**Category:** Elasticsearch\
**Created:** [October 3, 2022, 9:09pm UTC](https://discuss.elastic.co/t/nested-aggregation-with-and-always-return-0-match/315722 "2022-10-03T21:09:41Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![chattes](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/chattes/32/111574_2.png) [@chattes](https://discuss.elastic.co/u/chattes)\
**Post date:** [October 3, 2022, 9:09pm UTC](https://discuss.elastic.co/t/nested-aggregation-with-and-always-return-0-match/315722/1 "2022-10-03T21:09:41Z")

</div>

- Mapping

```auto
{
  "example-profiles" : {
    "mappings" : {
      "dynamic" : "false",
      "properties" : {
        "organization" : {
          "properties" : {
            "enabled" : {
              "type" : "boolean"
            },
            "id" : {
              "type" : "keyword"
            },
            "profileId" : {
              "type" : "keyword"
            }
          }
        },
        "skills" : {
          "type" : "nested",
          "properties" : {
            "id" : {
              "type" : "keyword"
            },
            "note" : {
              "type" : "keyword"
            },
            "profileSkillId" : {
              "type" : "keyword"
            },
            "skillLevelId" : {
              "type" : "keyword"
            }
          }
        }
      }
    }
  }
}

```

I want to filter on Skill Id and then Aggregate on the Skill Level Id.  
When I `OR` the Skills , it works fine .  
Example Aggregation Query

```auto
{
  "size": 0,
  "aggs": {
    "skills.skillLevelId": {
      "filter": {
        "bool": {
          "must": [],
          "filter": [
            {
              "terms": {
                "organization.id": [
                  14770
                ]
              }
            }
          ]
        }
      },
      "aggs": {
        "skills.skillLevelId": {
          "nested": {
            "path": "skills"
          },
          "aggs": {
            "agg": {
              "filter": {
                "bool": {
                      "should": [
                             {"term": {"skills.id": 553}},
                             {"term": {"skills.id": 426}}
                           ]

                }

              },
              "aggs": {
                "agg": {
                  "terms": {
                    "field": "skills.skillLevelId"
                  },
                  "aggs": {
                    "profileCounts": {
                      "reverse_nested": {}
                    }
                  }
                }
              }
            }
          }
        }
      }
    }
  }
}

```

ResultSet:

```auto
  "aggregations" : {
    "skills.skillLevelId" : {
      "meta" : { },
      "doc_count" : 102,
      "skills.skillLevelId" : {
        "doc_count" : 136,
        "agg" : {
          "doc_count" : 5,
          "agg" : {
            "doc_count_error_upper_bound" : 0,
            "sum_other_doc_count" : 0,
            "buckets" : [
              {
                "key" : "1",
                "doc_count" : 5,
                "profileCounts" : {
                  "doc_count" : 3
                }
              }
            ]
          }
        }
      }
    }
  }

```

Please help how to do an `AND` , I have tried `MUST` , and other FILTERS , but my result is always 0, even though I have documents like below:

```auto
"hits": [
{
  
        "_index" : "stg-profiles-1632487680259",
        "_type" : "_doc",
        "_id" : "5696966",
        "_score" : null,
        "_source" : {
          "id" : 5696966,
          "skills" : [
            {
              "id" : 553,
              "skillLevelId" : 1,
              "note" : null,
              "profileSkillId" : 73632610
            },
            {
              "id" : 426,
              "skillLevelId" : 1,
              "note" : null,
              "profileSkillId" : 73632596
            },
            {
              "id" : 460,
              "skillLevelId" : 1,
              "note" : null,
              "profileSkillId" : 73632586
            },
          ],
          "organization" : [
            {
              "id" : 14770,
              "profileId" : 5696966
            }
          ],
        },
        "sort" : [
          "bogart",
          5696966
        ]
      
}
...
]

```

---

<div class="post-metadata">

**Author:** ![RabBit\_BR](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/rabbit_br/32/82261_2.png) [@RabBit\_BR](https://discuss.elastic.co/u/RabBit_BR)\
**Post date:** [October 3, 2022, 10:27pm UTC](https://discuss.elastic.co/t/nested-aggregation-with-and-always-return-0-match/315722/2 "2022-10-03T22:27:49Z")

</div>

Hi @chattes

> [@chattes](#):
>
> Please help how to do an `AND` , I have tried `MUST` , and other FILTERS , but my result is always 0

Where AND will be used in the query?

> [@chattes](#):
>
> I want to filter on Skill Id and then Aggregate on the Skill Level Id.

You pass more skill id for query? If yes, try use Terms Query.

---

<div class="post-metadata">

**Author:** ![chattes](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/chattes/32/111574_2.png) [@chattes](https://discuss.elastic.co/u/chattes)\
**Post date:** [October 3, 2022, 11:55pm UTC](https://discuss.elastic.co/t/nested-aggregation-with-and-always-return-0-match/315722/3 "2022-10-03T23:55:25Z")

</div>

@RabBit_BR have an example please

---

<div class="post-metadata">

**Author:** ![chattes](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/chattes/32/111574_2.png) [@chattes](https://discuss.elastic.co/u/chattes)\
**Post date:** [October 4, 2022, 1:26am UTC](https://discuss.elastic.co/t/nested-aggregation-with-and-always-return-0-match/315722/4 "2022-10-04T01:26:03Z")

</div>

I was able to get the Working Solution to the Problem above , thanks to the discussion in the ES Slack Channel!!!

Here is how we would aggregate Skills where User have Skill A and Skill B ...

```auto
GET example-profiles/_search
{
  "size": 0,
  "aggs": {
    "filter_org_id": {
      "filter": {
        "bool": {
          "must": [],
          "filter": [
            {
              "terms": {
                "organization.id": [
                  14770
                ]
              }
            }
          ]
        }
      },
      "aggs": {
        "filter_skills.ids": {
          "filter": {
            "bool": {
              "must": [
                {
                  "nested": {
                    "path": "skills",
                    "query": {
                      "match": {
                        "skills.id": 40
                      }
                    }
                  }
                },
                {
                  "nested": {
                    "path": "skills",
                    "query": {
                      "match": {
                        "skills.id": 37
                      }
                    }
                  }
                }
              ]
            }
          },
          "aggs": {
            "nested_skills": {
              "nested": {
                "path": "skills"
              },
              "aggs": {
                "skills.skillLevelId": {
                  "terms": {
                    "field": "skills.skillLevelId"
                  },
                  "aggs": {
                    "profileCounts": {
                      "reverse_nested": {},
                      "aggs": {
                        "th": {
                          "top_hits": {
                            "size": 1
                          }
                        }
                      }
                    }
                  }
                }
              }
            }
          }
        }
      }
    }
  }
}

```

The main point here is the Filter with Must Bool Query( Both A and B) , along with the Nested Path to the Skills.

---

<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:** [November 1, 2022, 1:26am UTC](https://discuss.elastic.co/t/nested-aggregation-with-and-always-return-0-match/315722/5 "2022-11-01T01:26:24Z")

</div>

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