Elastic Search: Query on multiple fields

Question

I have JSON document in elastic search as follows

{
  "animals": [
    {
      "id": 1,
      "name": "cat"
    },
    {
      "id": 2,
      "name": "dog"
    },
    {
      "id": 3,
      "name": "rabbit"
    }
  ]
}

How to query return this document only when all the three animals are present?

This is not working.

curl -H 'Content-Type: application/json' -XPOST http://localhost:9200/*animals*/_search -d '{
  "query": {
    "bool": {
      "must": [
        {
          "term": {
            "animals.name.keyword": "dog"
          }
        },
        {
          "term": {
            "animals.name.keyword": "cat"
          }
        },
        {
          "term": {
            "animals.name.keyword": "rabbit"
          }
        }
      ],
      "must_not": [],
      "should": []
    }
  }
}'

Can you check the mapping for your index? I just tried creating an index with your sample data, and retrieved it with your query. Leaves the issue with something else, like a mapping that does not match the query — Olivier, Jun 23 '19 at 21:10
it works like a charm for me too. But Val's answer is worth considering for this use case — Pierre Mallet, Jun 24 '19 at 09:33

score 0 · Answer 1 · answered Jun 24 '19 at 05:42

In order to achieve what you want to need to make sure that animals is of nested type in your index mapping:

PUT animals
{
  "mappings": {
    "properties": {
      "animals": {
        "type": "nested"
      }
    }
  }
}

Then your query needs to look like this:

curl -H 'Content-Type: application/json' -XPOST http://localhost:9200/*animals*/_search -d '{
  "query": {
    "bool": {
      "must": [
        {
          "nested": {
            "path": "animals",
            "query": {
              "term": {
                "animals.name.keyword": "dog"
              }
            }
          }
        },
        {
          "nested": {
            "path": "animals",
            "query": {
              "term": {
                "animals.name.keyword": "cat"
              }
            }
          }
        },
        {
          "nested": {
            "path": "animals",
            "query": {
              "term": {
                "animals.name.keyword": "rabbit"
              }
            }
          }
        }
      ]
    }
  }
}'

I don't understand why you say this case need a nested field. Without the nested field, the sample document will have ["dog", "cat", "rabbit"] as value for the animals.name.keyword field. So a query with 3 must clause will works. In fact, when I reproduce in local it's working perfectly. Am I mistaken? — Pierre Mallet, Jun 24 '19 at 09:20
@PierreMallet You can also achieve the same without using `nested` fields, however, be warned that if you ever need to apply a second condition on another nested field, it won't work anymore and you'll be toast — Val, Jun 24 '19 at 09:24

Elastic Search: Query on multiple fields

1 Answers1

Linked