Bool AND search in properties in ElasticSearch

Question

I've got a very small dataset of documents put in ES :

    {"id":1, "name": "John", "team":{"code":"red", "position":"P"}}

    {"id":2, "name": "Jack", "team":{"code":"red", "position":"S"}}

    {"id":3, "name": "Emily", "team":{"code":"green", "position":"P"}}

    {"id":4, "name": "Grace", "team":{"code":"green", "position":"P"}}

    {"id":5, "name": "Steven", "team":[
        {"code":"green", "position":"S"},
        {"code":"red", "position":"S"}]}

    {"id":6, "name": "Josephine", "team":{"code":"red", "position":"S"}}

    {"id":7, "name": "Sydney", "team":[
        {"code":"red", "position":"S"},
        {"code":"green", "position":"P"}]}

I want to query ES for people who are in the red team, with position P. With the request

curl -XPOST 'http://localhost:9200/teams/aff/_search' -d '{
  "query": {
    "bool": {
      "must": [
        {
          "match": {
            "team.code": "red"
          }
        },
        {
          "match": {
            "team.position": "P"
          }
        }
      ]
    }
  }
}'

I've got a wrong result. ES gives

"name": "John",
"team":
  { "code": "red", "position": "P" }
and 
"name": "Sydney",
"team":
 [
  { "code": "red",   "position": "S"},
  { "code": "green", "position": "P"}
 ]

For the last entry, ES took the property code=red in the first record and took the property position=P in the second record.

How can I specify that the search must match the 2 two terms in the same record (within or not a list of nested records) ?

In fact, the good answer is only the document 1, with John.

Here is the gist that creates the dataset : https://gist.github.com/flrt/4633ef59b9b9ec43d68f

Thanks in advance

Answer 1

When you index document like

{
  "name": "Sydney",
  "team": [
    {"code": "red", "position": "S"},
    {"code": "green","position": "P"}
  ]
}

ES implicitly create inner object for your field (team in particular example) and flattens it to structure like

{
  'team.code': ['red', 'green'],
  'team.position: ['S', 'P']
}

So you lose your order. To avoid this you need explicitly put nested mapping, index your document as always and query them with nested query

So, this

PUT so/nest/_mapping
{
  "nest": {
    "properties": {
      "team": {
        "type": "nested"
      }
    }
  }
}

PUT so/nest/
{
  "name": "Sydney",
  "team": [
    {
      "code": "red",
      "position": "S"
    },
    {
      "code": "green",
      "position": "P"
    }
  ]
}

GET so/nest/_search
{
  "query": {
    "nested": {
      "path": "team",
      "query": {
        "bool": {
          "must": [
            {
              "match": {
                "team.code": "red"
              }
            },
            {
              "match": {
                "team.position": "P"
              }
            }
          ]
        }
      }
    }
  }
}

will result with empty hits.

Further reading on relation management: https://www.elastic.co/blog/managing-relations-inside-elasticsearch

Answer 2

You can use a Nested Query so that your searches happen individually on the subdocuments in the team array, rather than across the entire document.

{
  "query": {
    "bool": {
      "must": [
        {
          "nested": {
            "path": "team",
            "query": {
              "bool": {
                "must": [
                  { "match": { "team.code": "red"   } },
                  { "match": { "team.position": "P" } }
                ]
              }
            }
          }
        }
      ]
    }
  }
}