Elasticsearch nested query with aggregation using nested term doesn't return any bucket

Question

I have an ES index with this mapping:

{
    "_doc": {
        "dynamic": "false",
        "properties": {
            "original": {
                "properties":{
                  "id": {
                      "type": "keyword"
                  },
                  "purchaseStatus": {
                      "type": "keyword"
                  },
                  "marketCode": {
                      "type": "keyword"
                  },
                  "salesProfiles": {
                      "type": "nested",
                      "properties": {
                          "marketCode": {
                              "type": "keyword"
                          },
                          "purchaseStatus": {
                              "type": "keyword"
                          }
                      }
                  }
                }
            },
            "recommended": {
                "properties":{
                  "id": {
                      "type": "keyword"
                  },
                  "purchaseStatus": {
                      "type": "keyword"
                  },
                  "marketCode": {
                      "type": "keyword"
                  },
                  "salesProfiles": {
                      "type": "nested",
                      "properties": {
                          "marketCode": {
                              "type": "keyword"
                          },
                          "purchaseStatus": {
                              "type": "keyword"
                          }
                      }
                  }
                }
            },
            "distance": {
                "type": "double"
            },
            "rank": {
                "type": "double"
            },
            "source": {
                "properties": {
                    "application": {
                        "type": "keyword"
                    },
                    "platform": {
                        "type": "keyword"
                    }
                }
            },
            "timestamp": {
                "properties": {
                    "createdAt": {
                        "type": "date"
                    },
                    "updatedAt": {
                        "type": "date"
                    }
                }
            }
        }
    },
    "_default_": {
        "dynamic": "false"
    }
}

and I need to obtain the recommended docs with salesProfiles.marketCode equal to original.marketCode but my query doesn't return any buckets:

GET index/_search
{
  "aggs": {
    "similarities": {
      "filter": {
        "bool": {
          "must": [
            {
              "term": {
                "original.storefrontId": "12345"
              }
            },
            {
              "nested": {
                "path": "recommended.salesProfiles",
                "query": {
                  "bool": {
                    "must": [
                      {
                        "match": {
                          "recommended.salesProfiles.purchaseStatus": "PAID"
                        }
                      }
                    ]
                  }
                }
              }
            }
          ]
        }
      },
      "aggs": {
         "markets": {
           "nested": {
             "path": "recommended.salesProfiles"
           },
           "aggs": {
              "recommendedMarket": {
                "terms": {
                    "field": "recommended.salesProfiles.marketCode",
                    "size": 100 
                }
              }
           }
         }
      }
    }
  },
  "explain": false
}

Any suggestion would be really appreciated. Thanks in advance!

Answer 1

Its hard to debug this without any example docs, but I think this might work

{
  "size": 0,
  "query": {
    "bool": {
      "must": [
        {
          "term": {
            "original.storefrontId": "12345"
          }
        },
        {
          "nested": {
            "path": "recommended.salesProfiles",
            "query": {
              "bool": {
                "must": [
                  {
                    "match": {
                      "recommended.salesProfiles.purchaseStatus": "PAID"
                    }
                  }
                ]
              }
            }
          }
        }
      ]
    }
  },
  "aggs": {
    "Profiles": {
      "nested": {
        "path": "recommended.salesProfiles"
      },
      "aggs": {
        "by_term": {
          "terms": {
            "field": "recommended.salesProfiles.marketCode",
            "size": 100
          }
        }
      }
    }
  }
}

I don't think you can use "nested" under the filter agg without being under a nested aggregation, so I believe that's why you didn't get any docs. I basically moved all the filtering to the query and just aggregated the terms later