Query string with AND operator in nested query not working. Any idea?

Question

I want to get the document in which nested child contains both words Mifune AND Miller-Meteor.

For more detail of nested, I've gone through https://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl-nested-query.html

here are the mappings

{
    "mappings" : {
        "properties" : {
            "driver" : {
                "type" : "nested",
                "properties" : {
                    "last_name" : {
                        "type" : "text"
                    },
                    "vehicle" : {
                        "type" : "nested",
                        "properties" : {
                            "make" : {
                                "type" : "text"
                            },
                            "model" : {
                                "type" : "text"
                            }
                        }
                    }
                }
            }
        }
    }
}

i've two documents in the index

{
  "driver" : {
        "last_name" : "McQueen",
        "vehicle" : [
            {
                "make" : "Powell Motors",
                "model" : "Canyonero"
            },
            {
                "make" : "Miller-Meteor",
                "model" : "Ecto-1"
            }
        ]
    }
},{
  "driver" : {
        "last_name" : "Hudson",
        "vehicle" : [
            {
                "make" : "Mifune",
                "model" : "Mach Five"
            },
            {
                "make" : "Miller-Meteor",
                "model" : "Ecto-1"
            }
        ]
    }
}

query as below

{
    "query" : {
        "nested" : {
            "path" : "driver",
            "query" : {
                "nested" : {
                    "path" :  "driver.vehicle",
                    "query" :  {
                        "bool" : {
                            "must" : [
                                { "match" : { "driver.vehicle.make" : "Mifune" } },
                                { "match" : { "driver.vehicle.make" : "Miller-Meteor" } }
                            ]
                        }
                    }
                }
            }
        }
    }
}

I tried the above query but it did not work also tried with query_string AND operator but it also not worked

{
    "query": {
        "nested": {
            "path": "driver",
            "query": {
                "nested": {
                    "path": "driver.vehicle",
                    "query": {
                        "bool": {
                            "must": [
                                {
                                    "query_string": {
                                        "query": "Mifune AND Miller-Meteor",
                                        "fields": ["driver.vehicle.make"]
                                    }
                                }
                            ]
                        }
                    }
                }
            }
        }
    }
}

Try terms maybe: https://www.elastic.co/guide/en/elasticsearch/reference/7.x/query-dsl-terms-query.html — D.T, Nov 20 '19 at 07:52

Assael Azran · Accepted Answer · 2019-11-20T09:18:41.787

0

This is how you should query multiple nested fields.

There are two Nested queries inside your must clause.

The bool->must operator should be outside of your internal nested fields.

GET my_index/_search
{
  "query": {
    "nested": {
      "path": "driver",
      "query": {
        "bool": {
          "must": [
            {
              "nested": {
                "path": "driver.vehicle",
                "query": {
                  "match": {
                    "driver.vehicle.make": "Mifune"
                  }
                }
              }
            },
            {
              "nested": {
                "path": "driver.vehicle",
                "query": {
                  "match": {
                    "driver.vehicle.make": "Miller-Meteor"
                  }
                }
              }
            }
          ]
        }
      }
    }
  }
}

Results:

"timed_out" : false,
 "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 1,
      "relation" : "eq"
    },
    "max_score" : 2.769686,
    "hits" : [
      {
        "_index" : "my_index",
        "_type" : "_doc",
        "_id" : "2",
        "_score" : 2.769686,
        "_source" : {
          "driver" : {
            "last_name" : "Hudson",
            "vehicle" : [
              {
                "make" : "Mifune",
                "model" : "Mach Five"
              },
              {
                "make" : "Miller-Meteor",
                "model" : "Ecto-1"
              }
            ]
          }
        }
      }
    ]
  }
}

Nested DataType

Hope this helps

edited Nov 20 '19 at 09:18

answered Nov 20 '19 at 08:01

Assael Azran

2,863
1
9
13

Thanks for the reply. I tried the give solution but it is not working. – Devang Prajapati Nov 20 '19 at 09:12
I tried it and it worked. what do you get when you run it? see Results in my answer – Assael Azran Nov 20 '19 at 09:14
let me check is there anything wrong with my query. – Devang Prajapati Nov 20 '19 at 09:22
Base on the mappings and indexed documents you provide just copy paste my query and change **my_index** to your actual index. – Assael Azran Nov 20 '19 at 09:24
Thanks, it worked. there's a little mistake in my query. – Devang Prajapati Nov 20 '19 at 09:27
can i use terms query within single must object for all words ? – Devang Prajapati Nov 20 '19 at 09:30
terms means OR between your values. and you will have to change your mapping. in order to use terms you will have to add a keyword type to your fields – Assael Azran Nov 20 '19 at 09:38
I've added keyword to the field make and tried with terms query not working. It is giving me all two documents instead of second one which is expected result. – Devang Prajapati Nov 20 '19 at 10:18
is there any other efficient way for this? because in the case for more than two words i can not add match blocks for all the given words? Thanks in advance. – Devang Prajapati Nov 20 '19 at 10:28
As i told you. terms means OR so you will get both documents and btw my answer solved your issue with **AND Operator** and the **ACCEPT** shouldn't have been removed. – Assael Azran Nov 20 '19 at 10:40
Sorry for that. it was by mistakenly removed. i'm new to here after a long time so. You are right but i've to search for multiple words for all the words i've to add blocks. Let me know the better option if you have. – Devang Prajapati Nov 20 '19 at 10:46
No problem. i'll see what i can do. but the best option is to use **must** – Assael Azran Nov 20 '19 at 11:07

Query string with AND operator in nested query not working. Any idea?

1 Answers1