Querying a multiple level nested object

Hello everyone,

I would like to precise that I've searched before annoy you with my question but I didn't found the answer on Google or even through this forum (but maybe that it has already been asked somewhere with some other keywords).

Here is the concerned mapping :

body: {
	categories: {
		type: "nested",
		properties: {
			name: { type: "string" },
			list: {
				type: "nested",
				properties: {
					url_site: { type: "string" },
					persons: {
						total_customers: { type: "integer" },
						total_subscribers: { type: "integer" },
						details: {
							type: "nested",
							properties: {
								person_id: { type: "string" },
								person_date_registration: { type: "date" },
								person_date_subscription: { type: "date" }
							}
						}
					}
				}
			}
		}
	}
}

Yeah, I know, complex mapping. But my question is kind of tricky. I'm trying to do a research through the javascript API like this :

elasticClient.search({
	index: indexName,
	type: typeName,
	q: customer_id
}).then(function (body) {
	return body.hits.hits[0];
}, function (err) {
	console.trace("\nTRACE : " + err.message);
});

Do you know how I could pass a correct q parameter to search all the persons with the person_id equals to my customer_id ?

I tried the following using SENSE but failed (hard customer ID given):

POST /indexName/typeName/_search
{
   "query": {
      "bool": {
         "must": {
            "match": {
               "categories.list.persons.details.person_id": "50"
            }
         }
      }
   }
}

and even

POST /indexName/typeName/_search
{
   "query": {
      "nested": {
         "path": "categories[0].list[0].persons",
         "query": {
            "bool": {
               "must": [
                  {
                     "match": {
                        "categories[0].list[0].persons.id": 50
                     }
                  }
               ]
            }
         }
      }
   }
}

You guys already help me a lot with ElasticSearch and I know that here is the best place to ask this question.

Have a good day.

A lost intern.

Still working on this problem, I think that it's almost done. Thanks to the help of Github and its issues part, I've done this for the moment :

 POST index/type/_search
{
   "_source": false,
   "query": {
      "nested": {
         "path": "categories",
         "query": {
            "nested": {
               "path": "categories.list",
               "query": {
                  "nested": {
                     "path": "categories.list.persons",
                     "query": {
                        "nested": {
                           "path": "categories.list.persons.details",
                           "query": {
                              "match": {
                                 "categories.list.persons.details.person_id": "50"
                              }
                           }
                        }
                     }
                  }
               }
            }
         },
         "inner_hits": {}
      }
   }
}

It's not perfect and returns an error about nested item but I continue to work on it. This answer aims to help people who could be in the same case than me, with multiple level of nested objects.

I've followed the instructions of this issue : https://github.com/elastic/elasticsearch/issues/11830

If you have the solution, here the error returned :

{
   "error": {
      "root_cause": [
         {
            "type": "query_parsing_exception",
            "reason": "[nested] nested object under path [categories.list.persons] is not of nested type",
            "index": "index",
            "line": 11,
            "col": 22
         }
      ],
      "type": "search_phase_execution_exception",
      "reason": "all shards failed",
      "phase": "query",
      "grouped": true,
      "failed_shards": [
         {
            "shard": 0,
            "index": "index",
            "node": "nodeID",
            "reason": {
               "type": "query_parsing_exception",
               "reason": "[nested] nested object under path [categories.list.persons] is not of nested type",
               "index": "index",
               "line": 11,
               "col": 22
            }
         }
      ]
   },
   "status": 400
}

Have a nice day

This error message suggests you haven't defined a type for the persons field. At least I can't see one in your initial posting. I'm still wondering if you really need that deep level of nested objects or if you could flatten the structure. This depends on what you do with this kind of documents though.

Hello @cbuescher

Nice memory of the project. I have to admit that this architecture might not be the best one but it has been "validated" by my bosses and it was my really first experience with NoSQL. Nobody know ElasticSearch in my company so I have to handle it alone. I've tried to have the best model but I failed on some points. If I had enough time to think about it and reset the mapping from scratch, I would (but my internship is almost done and I really have to succeed with this request).

Concerning the request, I'm still unable to find the correct structure. It seems to be good until the "persons" object. I don't know how to pass it to go the next nested level. I'm blocked with this request which currently return all the data in my index/type :

POST sa_bigbrother/sites/_search
{
   "query": {
      "nested": {
         "path": "categories",
         "query": {
            "nested": {
               "path": "categories.list",
               "query": {
                  "nested": {
                     "path": "categories.list.persons.details",
                     "query": {
                        "bool": {
                           "must": {
                              "match": {
                                 "categories.list.persons.details.person_id": "50"
                              }
                           }
                        }
                     }
                  }
               }
            }
         }
      }
   },
   "inner_hits": {}
}

Do you know how I should process?

Hi,
I tried to recreate your mapping (not 100% exactly probably, but more or less) and your query worked for me, just returning one user with that id. Here's what I did, you can compare that to your own mapping to spot possible differences:

PUT /nested_test
{
  "mappings": {
    "t": {
      "properties": {
        "categories": {
          "type": "nested",
          "properties": {
            "name": {
              "type": "string"
            },
            "list": {
              "type": "nested",
              "properties": {
                "url_site": {
                  "type": "string"
                },
                "persons": {
                  "type": "nested",
                  "properties": {
                    "total_customers": {
                      "type": "integer"
                    },
                    "total_subscribers": {
                      "type": "integer"
                    },
                    "details": {
                      "type": "nested",
                      "properties": {
                        "person_id": {
                          "type": "string"
                        },
                        "person_date_registration": {
                          "type": "date"
                        },
                        "person_date_subscription": {
                          "type": "date"
                        }
                      }
                    }
                  }
                }
              }
            }
          }
        }
      }
    }
  }
}

PUT /nested_test/t/1
{
  "categories" : {
    "name" : "cat1",
    "list" : {
      "url_site" : "www.bla.org",
      "persons" : {
        "total_customers" : 10,
        "total_subscribers" : 10,
        "details" : {
          "person_id" : 1
        }
      }
    }
  }
}

PUT /nested_test/t/2
{
  "categories" : {
    "name" : "cat2",
    "list" : {
      "url_site" : "www.bleep.org",
      "persons" : {
        "total_customers" : 10,
        "total_subscribers" : 10,
        "details" : {
          "person_id" : 2
        }
      }
    }
  }
}

PUT /nested_test/t/3
{
  "categories" : {
    "name" : "cat3",
    "list" : {
      "url_site" : "www.blubb.org",
      "persons" : {
        "total_customers" : 10,
        "total_subscribers" : 10,
        "details" : {
          "person_id" : 3
        }
      }
    }
  }
}

POST /nested_test/t/_search
{
  "query": {
    "nested": {
      "path": "categories",
      "query": {
        "nested": {
          "path": "categories.list",
          "query": {
            "nested": {
              "path": "categories.list.persons.details",
              "query": {
                "bool": {
                  "must": {
                    "match": {
                      "categories.list.persons.details.person_id": 1
                    }
                  }
                }
              }
            }
          }
        }
      }
    }
  }
}

returns ==>

"hits": {
    "total": 1,
    "max_score": 1.9162908,
    "hits": [
      {
        "_index": "nested_test",
        "_type": "t",
        "_id": "1",
        "_score": 1.9162908,
        "_source": {
          "categories": {
            "name": "cat1",
            "list": {
              "url_site": "www.bla.org",
              "persons": {
                "total_customers": 10,
                "total_subscribers": 10,
                "details": {
                  "person_id": 1
                }
              }
            }
          }
        }
      }
    ]
  }

btw. if you are just interested in the document without scoring, you can use the filter section in the matchquery instead of must.

1 Like

God bless you for trying to help a desperate intern.

I've tried your example and when I add another person in "cat1" and retry your request, it returns me the other person too. If I have to get the parent data, it's still cool but I cannot get all the data for this request, only the customer which answer to the filter or I will burst the stack overflow.

To not say silly stuff, I tried to use the filter like you said but unfortunately It returns the same result.

Sorry, I've re-run my example from above with all docs in "cat1" and I can filter for one person_id only sucessfully with the given query. So this works for me. Make sure to closely look at the differences between that example and your real setup, hope you find those differences and get your own query working.

Sorry, tiredness might be playing tricks on me. I'll double-check now!

I've found a difference. It's about the "persons" object that you set the type from "object" to "nested". :slight_smile:

"persons" shouldn't be nested following what have been done. Is it a problem to set it as an object ? Or do I have to set it as a nested to navigate inside ?

If I set it as a nested object, it could be done by the fact that I won't push another "persons" object in this "site" object.

There's a good blog post about comparing object to nested here: Managing Relations Inside Elasticsearch | Elastic Blog
I think I can't give you a better explanation right now.

Hello I have gone the same problem as you did. Had exactly the same problem you did, instead of match I used match_phrase and it worked as a charm.