ElasticSearch: How do I search within a collection

Question

I have the following structure of objects in ElasticSearch:

{
  _id: 1,
  myObj: {
    myCol: [{id: 1, name:"1"}, {id: 2, name:"2"}, {id: 3, name:"3"}]
  }
},
{
  _id: 2,
  myObj: {
    myCol: [{id: 2, name:"2"}, {id: 3, name:"3"}, {id: 4, name:"4"}]
  }
},

I'm using C# NEST library to create queries. I want to search myCol collection of objects, using collection of identifiers.

Example #1: Search request: identifiers [2, 3] Result: Both objects are returned

Example #2: Search request: identifier: [1] Result: First object is returned

Example #3: Search request: identifier: [1, 2, 3, 4] Result: No objects are returned

---

What i'm actullay trying to do is a query "Contains all".

Please note:

1. C# NEST MultiMatchQuery type does not support integer arrays (only strings. So bad). So please don't offer me to use this type of query
2. I'm using Object Initializer query syntax
3. A correct query in ElasticSearch syntax would be enough.

Answer 1

What you want is to fetch documents that contain all of the specified IDs somewhere in the collection.

When you use collections of objects in ElasticSearch, they are flattened out so what you actually index is something like the following.

myObj.myCol.id = [ 2, 3, 4 ]
myObj.myCol.name = [ "2", "3", "4" ]

In many cases this is problematic because you lose track of which pairs of ID/Name go together (so you can't, for example, query for a document containing an object with ID x and name y - it will produce false positives if the collection contains x and y in different objects).

However, in your case, it's actually beneficial, because you can just query for documents that contain all of the IDs in myObj.myCol.id, e.g.:

{
  "query": {
    "bool": {
      "must": [
        { "match": { "myObj.myCol.id": 1 }},
        { "match": { "myObj.myCol.id": 2 }}
      ]
    }
  }
}

This will only return documents where myObj.myCol contains objects with IDs of both 1 and 2.

More information on how collections work in ES can be found here.