Filter to exclude hits in a deeply nested document

Elastic Stack Elasticsearch
1

Hi All,
I have recently started exploring Elasticsearch and hit a roadblock. I have a document with 3 level of nesting as follows:

{
"sales": {
"properties" :
{
"id" : {
"type" : "long"
},
......
"outlets" : {
"type" : "nested",
"properties" : {
"name" : {
"type" : "string","index":"not_analyzed"
},
"branchId" : {
"type" : "long"
}
"products" : {
"type" : "nested",
"properties" : {
"code" : {
"type" : "string","index":"not_analyzed"
},
"inventory" : {
"type" : "integer"
},
"price" : {
"type" : "double"
}
}
}
}
}
}
}
}

So Sales would have records of several nested outlets, which in turn will again have nested records of various products available.

Now I would like to retrieve the list names of all outlets which do NOT sell a particular code. In a completely flat mapping I could get the same using not or must_not filters. However for a scenario where the documents lists an outlet 'X' which sells 2 product with codes 'a' & 'b'. Now when i try the adding a nested query with a "not"/"must_not" filter for a match with 'a', inner hits still return the outlet X.

I have used the following queries and their variations:

Query 1

{
"query":{
"filtered" : {
"filter" : {
"nested" : {
"path" : "outlets.products",
"filter" : {
"not":{
"term" : {"outlets.products.code" : 'a'}
}
}
}
}
}
}
}

Query 2

{

"_source" : {
    "includes" : ["outlets.name","outlets.products.code" ],
    "excludes" : [ ]
},

"inner_hits":{
    "outlets.products" : {
        "path" : { 
            "outlets.products" : { 
                "query" : {
                  "filtered":{
                    "filter": {
                        "not":{
                            "term" : {"outlets.products.code" : 'a'}                    
                        }
                    }
                  }
                }
                
                
            }
        }
    }

}

}

I have tried using a parent/child based mapping but the result still remains the same.

Please help me understand where I am going wrong and what should be my approach.

Thanks :smile: