# Query with "has\_child" filter and date range

**URL:** <https://discuss.elastic.co/t/query-with-has-child-filter-and-date-range/75107>\
**Category:** Elasticsearch\
**Created:** [February 14, 2017, 8:23pm UTC](https://discuss.elastic.co/t/query-with-has-child-filter-and-date-range/75107 "2017-02-14T20:23:49Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![eshlox](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/eshlox/32/13792_2.png) [@eshlox](https://discuss.elastic.co/u/eshlox)\
**Post date:** [February 14, 2017, 8:23pm UTC](https://discuss.elastic.co/t/query-with-has-child-filter-and-date-range/75107/1 "2017-02-14T20:23:49Z")

</div>

Hi,

Elasticsearch 5.1 data structure:

```
"test123": {
    "mappings": {
        "doctype1": {
            "dynamic": "strict",
            "_parent": {
                "type": "doctype2"
            },
            "_routing": {
                "required": true
            },
            "properties": {
                "title": {
                    "type": "text"
                }
            }
        },
	    "doctype2": {...},
        "doctype3": {
            "dynamic": "strict",
            "_parent": {
                "type": "doctype1"
            },
            "_routing": {
                "required": true
            },
            "properties": {
                "start_datetime": {
                    "type": "date"
                }
            }
        }
    }
}

```

Any idea why the last query returns only 18 hits? (should return 474 because everything in doctype3 has start\_datetime = datetime.datetime(2017, 2, 14, 6, 39, 36, 989408)).

Query on [http://localhost:9200/test123/doctype1/\_search](http://localhost:9200/test123/doctype1/_search) - result: 474

```
{
   "_source":{
      "includes":[
         "title"
      ]
   },
   "query":{
      "match_all":{

      }
   }
}

```

Query on [http://localhost:9200/test123/doctype1/\_search](http://localhost:9200/test123/doctype1/_search) - result: 474

```
{
   "_source":{
      "includes":[
         "title"
      ]
   },
   "query":{
      "bool":{
         "filter":[
            {
               "has_child":{
                  "type":"doctype3"
               }
            }
         ]
      }
   }
}

```

Query on [http://localhost:9200/test123/doctype3/\_search](http://localhost:9200/test123/doctype3/_search) - result: 2495

```
{
   "_source":{
      "includes":[
         "start_datetime"
      ]
   },
   "query":{
      "bool":{
         "filter":[
            {
               "range":{
                  "start_datetime":{
                     "lte":"2017-02-15",
                     "gte":"2017-02-13"
                  }
               }
            }
         ]
      }
   }
}

```

Query on [http://localhost:9200/test123/doctype1/\_search](http://localhost:9200/test123/doctype1/_search) - result: 18, why?

```
{
   "_source":{
      "includes":[
         "_id",
         "title"
      ]
   },
   "query":{
      "bool":{
         "filter":[
            {
               "has_child":{
                  "type":"doctype3",
                  "query":{
                     "range":{
                        "start_datetime":{
                           "gte":"2017-02-13",
                           "lte":"2017-02-15"
                        }
                     }
                  }
               }
            }
         ]
      }
   }
}

```

Maybe the query is incorrect?

---

<div class="post-metadata">

**Author:** ![jpountz](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jpountz/32/45836_2.png) [@jpountz](https://discuss.elastic.co/u/jpountz)\
**Post date:** [February 15, 2017, 9:48am UTC](https://discuss.elastic.co/t/query-with-has-child-filter-and-date-range/75107/2 "2017-02-15T09:48:16Z")

</div>

Note that the below query is invalid. The fact that no child query is provided makes Elasticsearch ignore the query entirely. We had to do this for backward compatibility reasons, as of Elasticsearch 6.0 such a query would fail complaining that no `query` object is provided under the `has_child` query. Can you try to run it again with a `match_all` query, which I think is what you wanted to test?

```auto
{
   "_source":{
      "includes":[
         "title"
      ]
   },
   "query":{
      "bool":{
         "filter":[
            {
               "has_child":{
                  "type":"doctype3"
               }
            }
         ]
      }
   }
}

```

---

<div class="post-metadata">

**Author:** ![eshlox](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/eshlox/32/13792_2.png) [@eshlox](https://discuss.elastic.co/u/eshlox)\
**Post date:** [February 15, 2017, 8:55pm UTC](https://discuss.elastic.co/t/query-with-has-child-filter-and-date-range/75107/3 "2017-02-15T20:55:49Z")

</div>

Thanks, good to know because I thought that this query was correct. Now I see that it's not an issue with date range but with parent-child relationship. This query:

```
{
   "_source":{
      "includes":[
         "title"
      ]
   },
   "query":{
      "bool":{
         "filter":[
            {
               "has_child":{
                  "type":"performance",
                  "query": {
                  	"match_all": {}
                  }
               }
            }
         ]
      }
   }
}

```

returns incorrect number of results. I noticed that it returns random results after every reindex. Could it be an issue with shards because parent and child should exist in the same shard? Should I set routing during data index? My mapping looks (I removed most of properties) like this:

```
"test123":{
   "mappings":{
      "doctype1":{
         "dynamic":"strict",
         "properties":{
            "name":{
               "type":"keyword"
            },

         }
      },
      "doctype2":{
         "dynamic":"strict",
         "_parent":{
            "type":"doctype1"
         },
         "_routing":{
            "required":true
         },
         "properties":{
            "title":{
               "type":"text"
            }
         }
      },
      "doctype3":{
         "dynamic":"strict",
         "_parent":{
            "type":"doctype2"
         },
         "_routing":{
            "required":true
         },
         "properties":{
            "id":{
               "type":"integer"
            },
            "start":{
               "type":"date"
            }
         }
      },
      "doctype4":{
         "dynamic":"strict",
         "_parent":{
            "type":"doctype4"
         },
         "_routing":{
            "required":true
         },
         "properties":{
            "section":{
               "type":"keyword"
            }
         }
      }
   }
}

```

I want to have a possibility to get all data from doctype2 when doctype2 has a doctype3 as a parent and start property of doctype3 is between some data range.

---

<div class="post-metadata">

**Author:** ![jpountz](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jpountz/32/45836_2.png) [@jpountz](https://discuss.elastic.co/u/jpountz)\
**Post date:** [February 16, 2017, 9:01am UTC](https://discuss.elastic.co/t/query-with-has-child-filter-and-date-range/75107/4 "2017-02-16T09:01:22Z")

</div>

Indeed. You have 3 levels of parent/child so you should make sure to use the id of the grandparent as a routing key for every index operation of a document in `doctype3`.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [March 16, 2017, 9:01am UTC](https://discuss.elastic.co/t/query-with-has-child-filter-and-date-range/75107/5 "2017-03-16T09:01:46Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.