Nested Should query(OR) inside a Must query(AND) in elastic Search

Question

I have elastic Search data in following format:

{
    "is_cricketer": 1,
    "name": "Abraham",
    "cities": [
        { "name": "stellenbosch" },
        { "name": "Nelspruit" },
        { "name": "East London" }
    ]
},
{
    "is_cricketer": 1,
    "name": "Abraham",
    "cities": [
        { "name": "Rustenburg" },
        { "name": "Nelspruit" },
        { "name": "East London" }
    ]
},
{
    "is_cricketer": 0,
    "name": "deVilliers",
    "cities": [
        { "name": "Cape town" },
        { "name": "Nelspruit" },
        { "name": "East London" }
    ]
}

I need to query elastic search to get all the profiles with is_cricketer = 1 and an OR query over the field for cities.name and name field. ie

( profile.is_cricketer == 1 && (profile.name == 'Abraham' || profile.cities[i].name == 'Nelspruit' ))

To get the profiles with OR query on the fields cities.name and name field for matching query string is as follows and it works a expected:

"should": [
    {
        "nested": {
            "path": "cities",
            "query": {
                "multi_match": {
                    "query": "Nelspruit",
                    "fields": [
                        "cities.name"
                    ]
                }
            }
        }
    },
    {
        "multi_match": {
            "query": "Abraham",
            "fields": [
                "name"
            ]
        }
    }
]

And the Must query to get all the profiles with field is_cricketer = 1 is follows:

{
    "must": {
        "match": {
            "is_cricketer": "1"
        }
    }
}

Above both queries working fine and i am trying to combine both query as follows:

{
    "query": {
        "bool": {
            "must": {
                "match": {
                    "is_cricketer": "1"
                }
            },
            "should": [
                {
                    "nested": {
                        "path": "cities",
                        "query": {
                            "multi_match": {
                                "query": "Nelspruit",
                                "fields": [
                                    "cities.name"
                                ]
                            }
                        }
                    }
                },
                {
                    "multi_match": {
                        "query": "Abraham",
                        "fields": [
                            "name"
                        ]
                    }
                }
            ]
        }
    }
}

which is not returning expected results, its returning all the profiles with is_cricketer = 1 without filtering for name and cities.name.

I also tried to include the should query inside must query as follows:

{
    "query": {
        "bool": {
            "must": [{
                "match": {
                    "is_cricketer": "1"
                }
            }, {
                "should": [
                    {
                        "nested": {
                            "path": "cities",
                            "query": {
                                "multi_match": {
                                    "query": "Nelspruit",
                                    "fields": [
                                        "cities.name"
                                    ]
                                }
                            }
                        }
                    },
                    {
                        "multi_match": {
                            "query": "Abraham",
                            "fields": [
                                "name"
                            ]
                        }
                    }
                ]
            }]
        }
    }
}

But i got following error for the above query:

"Error: [parsing_exception] [should] query malformed, no start_object after query name, with { line=1 & col=64 } at respond (/GitRepo/project/node_modules/elasticsearch/src/lib/transport.js:307:15) at checkRespForFailure (/GitRepo/project/node_modules/elasticsearch/src/lib/transport.js:266:7) at HttpConnector. (/GitRepo/project/node_modules/elasticsearch/src/lib/connectors/http.js:159:7) at IncomingMessage.bound (/GitRepo/project/node_modules/elasticsearch/node_modules/lodash/dist/lodash.js:729:21) at emitNone (events.js:111:20) at IncomingMessage.emit (events.js:208:7) at endReadableNT (_stream_readable.js:1056:12) at _combinedTickCallback (internal/process/next_tick.js:138:11) at process._tickCallback (internal/process/next_tick.js:180:9)"

How to combine both the queries to get desired result. Any help will be appreciated.

I am new to elastic search and i am trying out hit and trial to get response. i can also use query_string there is no problem with it. for now i am facing issue at combining above must and nested should queries. — kgangadhar
that is because you don't close the squared bracket in the must clause — Lupanoide
No, in the second one you open the squared bracket in must clause, and you close in should clause. If the first one works, why you don't try to put all in must clause? In effect traslating it in SQL you should write ever with AND operator — Lupanoide
Can you update and show me what's wrong here with this query here in this gist link. — kgangadhar

jhilden jhilden · Accepted Answer · 2018-04-10T19:57:14

This worked for me on ES 6.0.

Setup

PUT test1
{
  "mappings": {
    "type1": {
      "properties": {
        "cities": {
          "type": "nested" 
        }
      }
    }
  }
}

POST test1/type1
{
    "is_cricketer": 1,
    "name": "Abraham",
    "cities": [
        { "name": "stellenbosch" },
        { "name": "Nelspruit" },
        { "name": "East London" }
    ]
}

POST test1/type1
{
    "is_cricketer": 1,
    "name": "Abraham",
    "cities": [
        { "name": "Rustenburg" },
        { "name": "Nelspruit" },
        { "name": "East London" }
    ]
}

POST test1/type1
{
    "is_cricketer": 0,
    "name": "deVilliers",
    "cities": [
        { "name": "Cape town" },
        { "name": "Nelspruit" },
        { "name": "East London" }
    ]
}

Query

GET test1/type1/_search
{
  "query": {
    "bool": {
      "must": [
        {
          "term": {
            "is_cricketer": {
              "value": 1
            }
          }
        }
      ],
      "should": [
        {
          "term": {
            "name.keyword": {
              "value": "Abraham"
            }
          }
        },
        {
          "nested": {
            "path": "cities",
            "query": {
              "term": {
                "cities.name.keyword": {
                  "value": "Nelspruit"
                }
              }
            }
          }
        }
      ]
    }
  }
}

Results - 2 hits

 "hits": {
    "total": 2,
    "max_score": 2.2685113,
    "hits": [
      {
        "_index": "test1",
        "_type": "type1",
        "_id": "zgcesWIBVwCaLf8KSuDi",
        "_score": 2.2685113,
        "_source": {
          "is_cricketer": 1,
          "name": "Abraham",
          "cities": [
            {
              "name": "stellenbosch"
            },
            {
              "name": "Nelspruit"
            },
            {
              "name": "East London"
            }
          ]
        }
      },
      {
        "_index": "test1",
        "_type": "type1",
        "_id": "eAQesWIBbxh35CpKckEH",
        "_score": 2.2685113,
        "_source": {
          "is_cricketer": 1,
          "name": "Abraham",
          "cities": [
            {
              "name": "Rustenburg"
            },
            {
              "name": "Nelspruit"
            },
            {
              "name": "East London"
            }
          ]
        }
      }
    ]
  }

Nested Should query(OR) inside a Must query(AND) in elastic Search

2 Answers

Setup

Query

Results - 2 hits