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

javascript node.js elasticsearch

12,522

Solution 1

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"
            }
          ]
        }
      }
    ]
  }

Solution 2

if you want a should query inside a must you can use it in the following way

{
  "query": {
    "bool": {
      "must": [
        {
          "bool": {
            "should": [
              {
                ... your query here
              }
            ]
          }
        }
      ]
    }
  }
}

12,522

Author by

kgangadhar

A passionate developer who worked as Fullstack Developer on Nodejs and Ruby-on-Rails technologies. Some of my posts here in stackoverflow: Use of cursorTo in Nodejs VScode single file debugging Nodejs Callback Connect event in http-server For node cricket fans: Ape-cricket For Rails cricket fans: CricApi Some of my open-source projects: Pencil-Run game using python and pygame Node REST api Contact Info: [email protected] Linked-In

Updated on June 09, 2022

Comments

kgangadhar almost 2 years

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.