elastic - query multiple levels on nested object in inner_hits

Question

i have a huge nested object which has lots of levels i want to create a query which will return only the leaf / some object in the middle, and the query is supposed to query multiple levels in the tree.

for example: my DB is saving the whole company structure. company -> wards -> employees -> working hours

i want to make a query that will return only the working hours of the employees in ward 2 which started later than 3pm this month

i tried to use inner_hits - but to no use.

as requested, sample document and expected result:

company:[{
  properties:{companyId: 112}
  ward:[{
    properties: {wardId: 223}
    employee:{
      properties: {employeeId: 334},
      workingHours: [
        { date: "1.1.2021", numOfHours: 4},
        { date: "1.2.2021", numOfHours: 7}
      ]
    }]
  }]
}]

the query: I need to return the working hours of date "1.2.21" , of employee 334, of ward 223. and only the working hours, not the whole tree.

expected result: 4 or { date: "1.1.2021", numOfHours: 4} , whatever is simpler

hope its clear now

Answer 1

You need to add inner_hits to all nested queries

You can either parse entire result to get matched working hours(from inner hits) o can use response filtering to remove additional data

Mapping

PUT index123
{
  "mappings": {
    "properties": {
      "company": {
        "type": "nested",
        "properties": {
          "ward": {
            "type": "nested",
            "properties": {
              "employee": {
                "type": "nested",
                "properties": {
                  "workingHours": {
                    "type": "nested",
                    "properties": {
                      "date": {
                        "type": "date"
                      }
                    }
                  }
                }
              }
            }
          }
        }
      }
    }
  }
}

Data

        "_index" : "index123",
        "_type" : "_doc",
        "_id" : "9gGYI3oBt-MOenya6BcN",
        "_score" : 1.0,
        "_source" : {
          "company" : [
            {
              "companyId" : 112,
              "ward" : [
                {
                  "wardId" : 223,
                  "employee" : {
                    "employeeId" : 334,
                    "workingHours" : [
                      {
                        "date" : "2021-01-01",
                        "numOfHours" : 4
                      },
                      {
                        "date" : "2021-01-02",
                        "numOfHours" : 7
                      }
                    ]
                  }
                }
              ]
            }
          ]
        }
      }

Query

GET index123/_search?filter_path=hits.hits.inner_hits.ward.hits.hits.inner_hits.employee.hits.hits.inner_hits.workingHours.hits.hits._source
{
  "query": {
    "nested": {
      "inner_hits": {
        "name":"ward"
      }, 
      "path": "company.ward",
      "query": {
        "bool": {
          "must": [
            {
              "term": {
                "company.ward.wardId": {
                  "value": 223
                }
              }
            },
            {
              "nested": {
                "inner_hits": {
                  "name":"employee"
                }, 
                "path": "company.ward.employee",
                "query": {
                  "bool": {
                    "must": [
                      {
                        "term": {
                          "company.ward.employee.employeeId": {
                            "value":334
                          }
                        }
                      },
                      {
                        "nested": {
                          "inner_hits": {
                            "name":"workingHours"
                          }, 
                          "path": "company.ward.employee.workingHours",
                          "query": {
                            "range": {
                              "company.ward.employee.workingHours.date": {
                                "gte": "2021-01-01",
                                "lte": "2021-01-01"
                              }
                            }
                          }
                        }
                      }
                    ]
                  }
                }
              }
            }
          ]
        }
      }
    }
  }
}

Result

{
  "hits" : {
    "hits" : [
      {
        "inner_hits" : {
          "ward" : {
            "hits" : {
              "hits" : [
                {
                  "inner_hits" : {
                    "employee" : {
                      "hits" : {
                        "hits" : [
                          {
                            "inner_hits" : {
                              "workingHours" : {
                                "hits" : {
                                  "hits" : [
                                    {
                                      "_source" : {
                                        "date" : "2021-01-01",
                                        "numOfHours" : 4
                                      }
                                    }
                                  ]
                                }
                              }
                            }
                          }
                        ]
                      }
                    }
                  }
                }
              ]
            }
          }
        }
      }
    ]
  }
}

Update:

Query with company ID

GET index123/_search?filter_path=hits.hits.inner_hits.company.hits.hits.inner_hits.ward.hits.hits.inner_hits.employee.hits.hits.inner_hits.workingHours.hits.hits._source
{
  "query": {
    "nested": {
      "path": "company",
      "inner_hits": {
        "name": "company"
      },
      "query": {
        "bool": {
          "must": [
            {
              "term": {
                "company.companyId": {
                  "value": 112
                }
              }
            },
            {
              "nested": {
                "inner_hits": {
                  "name": "ward"
                },
                "path": "company.ward",
                "query": {
                  "bool": {
                    "must": [
                      {
                        "term": {
                          "company.ward.wardId": {
                            "value": 223
                          }
                        }
                      },
                      {
                        "nested": {
                          "inner_hits": {
                            "name": "employee"
                          },
                          "path": "company.ward.employee",
                          "query": {
                            "bool": {
                              "must": [
                                {
                                  "term": {
                                    "company.ward.employee.employeeId": {
                                      "value": 334
                                    }
                                  }
                                },
                                {
                                  "nested": {
                                    "inner_hits": {
                                      "name": "workingHours"
                                    },
                                    "path": "company.ward.employee.workingHours",
                                    "query": {
                                      "range": {
                                        "company.ward.employee.workingHours.date": {
                                          "gte": "2021-01-01",
                                          "lte": "2021-01-01"
                                        }
                                      }
                                    }
                                  }
                                }
                              ]
                            }
                          }
                        }
                      }
                    ]
                  }
                }
              }
            }
          ]
        }
      }
    }
  }
}