Nested Aggregation with AND always return 0 match

Elastic Stack Elasticsearch
1
  • Mapping
{
  "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

{
  "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:

  "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:

"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
        ]
      
}
...
]
2

Hi @chattes

Where AND will be used in the query?

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

3

@RabBit_BR have an example please

4

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 ...

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.

1 Like
5

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