Nested Query in Elastic Search

Question

I have a schema in elastic search of this form:

{
   "index1" : {
      "mappings" : {
         "properties" : {
            "key1" : {
               "type" : "keyword"
            },
            "key2" : {
               "type" : "keyword"
            },
            "key3" : {
               "properties" : {
                  "components" : {
                     "type" : "nested",
                     "properties" : {
                        "sub1" : {
                           "type" : "keyword"
                        },
                        "sub2" : {
                           "type" : "keyword"
                        },
                        "sub3" : {
                           "type" : "keyword"
                        }
                     }
                  }
               }
            }
         }
      }
   }
}

and then the data stored in elastic search would be of the format:

{
   "_index" : "index1",
   "_type" : "_doc",
   "_id" : "1",
   "_score" : 1.0,
   "_source" : {
      "key1" : "val1",
      "key2" : "val2",
      "key3" : {
         components : [
            {
               "sub1" : "subval11",
               "sub3" : "subval13"
            },
            {
               "sub1" : "subval21",
               "sub2" : "subval22",
               "sub3" : "subval23"
            },
            {
               "sub1" : "subval31",
               "sub2" : "subval32",
               "sub3" : "subval33"
            }
         ]
      }
   }
}

As you can see that the sub1, sub2 and sub3 might not be present in few of the objects under key3.

Now if I try to write a query to fetch the result based on key3.sub2 as subval22 using this query

GET index1/_search
{
  "query": {
    "nested": {
      "path": "components",
      "query": {
        "bool": {
          "must": [
            {
              "match": {"key3.sub2": "subval22"}
            }
          ]
        }
      }
    }
  }
}

I always get the error as

{
  "error": {
    "root_cause": [
      {
        "type": "query_shard_exception",
        "reason": "failed to create query: {...}",
        "index_uuid": "1",
        "index": "index1"
      }
    ],
    "type": "search_phase_execution_exception",
    "reason": "all shards failed",
    "phase": "query",
    "grouped": true,
    "failed_shards": [
      {
        "shard": 0,
        "index": "index1",
        "node": "1aK..",
        "reason": {
          "type": "query_shard_exception",
          "reason": "failed to create query: {...}",
          "index_uuid": "1",
          "index": "index1",
          "caused_by": {
            "type": "illegal_state_exception",
            "reason": "[nested] failed to find nested object under path [components]"
          }
        }
      }
    ]
  },
  "status": 400
}

I understand that since sub2 is not present in all the objects under components, this error is being thrown. I am looking for a way to search such scenarios such that it matches and finds all the objects in the array. If a value is matched, then this doc should get returned.

Can someone help me to get this working.

Hey, good ques, just a minor mistake, provided my answer, pls hv a look — Amit
– Amit, Commented Apr 14, 2020 at 4:31

Amit · Accepted Answer · 2020-04-14 14:19:42Z

1

You made mistake while defining your schema, below schema works fine, Note I just defined key3 as nested. and changed the nested path to key3

Index def

{
    "mappings": {
        "properties": {
            "key1": {
                "type": "keyword"
            },
            "key2": {
                "type": "keyword"
            },
            "key3": {
                "type": "nested"
            }
        }
    }
}

Index you sample doc without any change

 {
  "key1": "val1",
  "key2": "val2",
  "key3": {
    "components": [ --> this was a diff
      {
        "sub1": "subval11",
        "sub3": "subval13"
      },
      {
        "sub1": "subval21",
        "sub2": "subval22",
        "sub3": "subval23"
      },
      {
        "sub1": "subval31",
        "sub2": "subval32",
        "sub3": "subval33"
      }
    ]
  }
}

Searching with your criteria

 {
    "query": {
        "nested": {
            "path": "key3", --> note this
            "query": {
                "bool": {
                    "must": [
                        {
                            "match": {
                                "key3.components.sub2": "subval22" --> note this
                            }
                        }
                    ]
                }
            }
        }
    }
}

This brings the proper search result

   "hits": [
      {
        "_index": "so_nested_61200509",
        "_type": "_doc",
        "_id": "2",
        "_score": 0.2876821,
        "_source": {
          "key1": "val1",
          "key2": "val2",
          "key3": {
            "components": [ --> note this
              {
                "sub1": "subval11",
                "sub3": "subval13"
              },
              {
                "sub1": "subval21",
                "sub2": "subval22",
                "sub3": "subval23"
              },
              {
                "sub1": "subval31",
                "sub2": "subval32",
                "sub3": "subval33"
              }
            ]

Edit:- Based on the comment from OP, updated sample doc, search query and result.

edited Apr 14, 2020 at 14:19

answered Apr 14, 2020 at 4:30

Amit

32.5k7 gold badges68 silver badges101 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Val Over a year ago

The search query is missing, probably bad copy/paste from the sample doc

Amit Over a year ago

@Val, thanks for pointing this, yeah bad copy/paste from my postman collection

Satyam Roy Over a year ago

@OpsterElasticsearchNinja Thank you for your answer. I made a mistake in the sample doc. The doc should be like this. The schema is correct.

{      "key1": "val1",      "key2": "val2",      "key3": {        "components": [          {            "sub1": "subval11",            "sub3": "subval13"          },          {            "sub1": "subval21",            "sub2": "subval22",            "sub3": "subval23"          },          {            "sub1": "subval31",            "sub2": "subval32",            "sub3": "subval33"          }        ]      }    }

Amit Over a year ago

sorry, yeah with this doc, it doesn't bring doc, let me spend some time on it and now I got it working 😀, updated both the doc and search query

Satyam Roy Over a year ago

That makes sense now. Thanks a lot @OpsterElasticsearchNinja. One more query, what if I want to club more queries together. Something like this.

{   "query": {     "nested": {       "path": "key3",       "query": {         "bool": {           "must": [             {"match": {"key3.components.sub2": "subval22"}},             {"match": {"key1": "val1"}}           ]         }       }     }   } }

Both queries must match to return results.

|

Collectives™ on Stack Overflow

Nested Query in Elastic Search

1 Answer 1

Index def

Index you sample doc without any change

Searching with your criteria

This brings the proper search result

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Index def

Index you sample doc without any change

Searching with your criteria

This brings the proper search result

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related