ElasticSearch bool filter on nested fields

Question

As a beginner in ElasticSearch we have our document structured like this:

{
"Id": 1246761,
"Title": "Official statement Title",    
"Categories": [
    {
        "Id": 3,
        "Type": 1,
        "Name": "Category 1-A"            
    },
    {
        "Id": 7,
        "Type": 1,
        "Name": "Category 1-B"
    },
    {
        "Id": 104,
        "Type": 3,
        "Name": "Category 3-C"
    },
    {
        "Id": 2001,
        "Type": 7,
        "Name": "Category 7-D"
    }
]
}

We would like to search our document on a combinaison of keyword in the title, and a combinaison of category ids. For example I want to get documents matching this :

• keyword = "Official"
• AND (Category.Id = (3 OR 7))
• AND (Category.Id = (2001))

So far this is my query:

    "query": {
    "bool": {
        "must": [
            {
                "multi_match": {
                    "operator": "and",
                    "query": "Official"
                }
            }
        ],
        "filter": [
          { "nested": {
            "path": "Categories",
            "query": {
              "bool": {
                "minimum_should_match": 2, 
                "should": [
                 { "terms": { "Categories.Id": [3,7] }},
                 { "terms": { "Categories.Id": [2001] }}
                ]
              }
            }
          }}
        ]
    }
}

But it's not giving me back any result... How should I rewrite this ?

---UPDATE--- Here's the mapping:

{

  "properties" : {
    "Id" : {
      "type" : "long"
    },
    "Title" : {
      "type" : "text",
      "fields" : {
        "keyword" : {
          "type" : "keyword",
          "ignore_above" : 256
        }
      }
    },
    "Categories" : {
      "type" : "nested",
      "properties" : {
        "Id" : {
          "type" : "long"
        },
        "Name" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          }
        },
        "ParentId" : {
          "type" : "long"
        },
        "Type" : {
          "type" : "long"
        }
      }
    }
  }

Found out the solution was to write multiple nested filters:

    "query": {
    "bool": {
        "must": [
            {
                "multi_match": {
                    "operator": "and",
                    "query": "Official"
                }
            }
        ],
        "filter": [
          {
            "bool": {
              "must": [
                    { "nested": {
                    "path": "Categories",
                    "query": {
                      "bool": {
                        "must": [
                          {"terms": {
                            "Categories.Id": [
                              "3,7"
                            ]
                          }}
                        ]
                      }
                    }
                  }},
                  { "nested": {
                    "path": "Categories",
                    "query": {
                      "bool": {
                        "must": [
                          {"terms": {
                            "Categories.Id": [
                              "2000"
                            ]
                          }}
                        ]
                      }
                    }
                  }}
                ]
            }
          }
        ]
    }
}

Answer 1

There's a typo in the first should query -- you're targeting .Id instead of .Type. Here's a fix:

{
  "query": {
    "bool": {
      "must": [
        {
          "multi_match": {
            "operator": "and",
            "query": "Official"
          }
        }
      ],
      "filter": [
        {
          "nested": {
            "path": "Categories",
            "query": {
              "bool": {
                "minimum_should_match": 2,
                "should": [
                  {
                    "terms": {
                      "Categories.Type": [ 3, 7 ]   <---
                    }
                  },
                  {
                    "terms": {
                      "Categories.Id": [ 2001 ]
                    }
                  }
                ]
              }
            }
          }
        }
      ]
    }
  }
}