How can I get all the documents which have at least the properties specified in an Elasticsearch query?

Question

It is possible to select an item from the index which match multiple values for a certain sub-item? I think this is not so clear but I added more details below.

I have the following index:

{ "mappings" : { "entity" : { "properties" : { "name" : {"type" : "string"}, "features" : { "type" : "nested", "include_in_parent" : false, "properties" : { "id" : {"type" : "integer"}, "value_int" : {"type" : "integer"}, "value_text" : {"type" : "string"}, "value_decimal" : {"type" : "integer"}
} } } } }, "settings" : { "number_of_shards" : 1, "number_of_replicas" : 0 } }

Some items from the index

{
"name" : "Bazar",
"features" : [
    {
        "id" : 1,
        "value_text" : null,
        "value_decimal" : null,
        "value_int": 51
    },        
    {
        "id" : 9,
        "value_text" : "Amsterdam",
        "value_decimal" : null,
        "value_int": null
    }      
]

}

{
"name" : "Bazar Test",
"features" : [
    {
        "id" : 1,
        "value_text" : null,
        "value_decimal" : null,
        "value_int": 52
    },        
    {
        "id" : 9,
        "value_text" : "Leiden",
        "value_decimal" : null,
        "value_int": null
    }      
]

}

{
"name" : "Bazar no city",
"features" : [
    {
        "id" : 1,
        "value_text" : null,
        "value_decimal" : null,
        "value_int": 51
    },        
]

}

What I need is a way to find just the items which have the features.id = 1 and features.id = 2 (ex: "Bazar" and "Bazar Test" items).

The query I got some far is

{
"query" : {
    "nested" : {
        "path" : "features",
        "query" : {
            "bool" : {
                "must" : [
                    { "terms" : { "features.id" : [1, 9]} }
                ]
            }
        }
    }
}

}

The problem with this query is that it selects the items which have features.id = 1 OR features.id = 9 so all the items are returned.

Edit Tried a new query

{
"query" : {
    "nested" : {
        "path" : "features",
        "query" : {
            "bool" : {
                "must" : [
                    { "terms" : { 
                            "features.id" : [1, 9], 
                            "minimum_should_match": 2
                        }
                    }
                ]
            }
        }
    }
}

}

But I got no results.

Edit:

After I combined the answers, I managed to get it working. Thank you for help :)

This is my query (a bit modified)

{
"from": 0,
"size": 20,
"query": {
    "filtered": {
        "query": {
            "bool": {
                "must": [
                    {
                        "match_phrase_prefix": {
                            "title": {
                                "query": "deli",
                                "max_expansions": 5
                            }
                        }
                    },
                    {
                        "match": {
                            "entity_type_id": 5
                        }
                    }
                ]
            }
        },
        "filter": {
            "and": {
                "filters": [
                    {
                        "nested": {
                            "path": "features",
                            "query": {
                                "bool": {
                                    "must": [
                                        {
                                            "match": {
                                                "features.id": 31
                                            }
                                        },
                                        {
                                            "match": {
                                                "features.value_int": {
                                                    "query": [
                                                        56, 57
                                                    ],
                                                    "operator": "and"
                                                }
                                            }
                                        }
                                    ]
                                }
                            }
                        }
                    }
                ]
            }
        }
    }
}

}

Thank you.

Answer 1

The match query supports a Boolean operator parameter. You should also wrap the query in a nested query, as the features field is nested in your mapping.

Try this query:

{
   "query": {
      "nested": {
         "query": {
            "match": {
               "features.id": {
                 "query": "1 9",
                 "operator": "and"
               }
            }
         },
         "path": "features"
      }
   }
}

Answer 2

Nested documents are more difficult to query. This should be what you want:

{
  "query": {
    "filtered": {
      "filter": {
        "and": {
          "filters": [
            {
              "nested": {
                "path": "features",
                "query": {
                  "term": {
                    "features.id": {
                      "value": "1"
                    }
                  }
                }
              }
            },
            {
              "nested": {
                "path": "features",
                "query": {
                  "term": {
                    "features.id": {
                      "value": "9"
                    }
                  }
                }
              }
            }
          ]
        }
      }
    }
  }
}