# Query latest 10 parent documents and corresponding child document if exists

**URL:** <https://discuss.elastic.co/t/query-latest-10-parent-documents-and-corresponding-child-document-if-exists/230334>\
**Category:** Elasticsearch\
**Created:** [April 29, 2020, 8:27am UTC](https://discuss.elastic.co/t/query-latest-10-parent-documents-and-corresponding-child-document-if-exists/230334 "2020-04-29T08:27:58Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![shashanka\_arnady](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/shashanka_arnady/32/67351_2.png) [@shashanka\_arnady](https://discuss.elastic.co/u/shashanka_arnady)\
**Post date:** [April 29, 2020, 8:27am UTC](https://discuss.elastic.co/t/query-latest-10-parent-documents-and-corresponding-child-document-if-exists/230334/1 "2020-04-29T08:27:59Z")

</div>

Hello,  
Lets say i want to store Songs data in elasticsearch where users can write comments for any of the songs.  
I am looking at option where i can get 10 latest songs (based on say cretedTime field) and corresponding reviews(if review exists). If i use the parent child  
relationship, i am not sure how to get 10 latest songs(parent document) and reviews(child document) for these songs( please not all songs may not have corresponding reviews).  
Is there other way to achieve this requirement?

---

<div class="post-metadata">

**Author:** ![cbuescher](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/cbuescher/32/60402_2.png) [@cbuescher](https://discuss.elastic.co/u/cbuescher)\
**Post date:** [April 29, 2020, 9:22am UTC](https://discuss.elastic.co/t/query-latest-10-parent-documents-and-corresponding-child-document-if-exists/230334/2 "2020-04-29T09:22:38Z")

</div>

Hi,

is the "createdTime" a property of the song (parent) or the review (child)? Would it be set once when a song gets entered into the system? Would those songs even already have reviews (given that they might be just recently created)?

---

<div class="post-metadata">

**Author:** ![shashanka\_arnady](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/shashanka_arnady/32/67351_2.png) [@shashanka\_arnady](https://discuss.elastic.co/u/shashanka_arnady)\
**Post date:** [April 29, 2020, 11:26am UTC](https://discuss.elastic.co/t/query-latest-10-parent-documents-and-corresponding-child-document-if-exists/230334/3 "2020-04-29T11:26:17Z")

</div>

Thanks a lot for the quick response. Really appreciate the help on this.

CreatedTime is a property of Songs.  
Songs : {  
SongName,  
SongTittle,  
Album,  
Artists,  
CreatedTime  
.....  
}

Comments : {  
commentedBy,  
comment  
}

Lets take one example, among the 10 recently created songs, say Song 3 doesnt have any comments and Song 2 has 5 comments.  
The query response should include Song 3 as well though it doesnt have any comments.  
How to achieve this is the question.

Thanks in advance

---

<div class="post-metadata">

**Author:** ![shashanka\_arnady](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/shashanka_arnady/32/67351_2.png) [@shashanka\_arnady](https://discuss.elastic.co/u/shashanka_arnady)\
**Post date:** [April 30, 2020, 6:27am UTC](https://discuss.elastic.co/t/query-latest-10-parent-documents-and-corresponding-child-document-if-exists/230334/4 "2020-04-30T06:27:27Z")

</div>

What's the best design wrt elastic search index for item and reviews kind of use case?

---

<div class="post-metadata">

**Author:** ![cbuescher](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/cbuescher/32/60402_2.png) [@cbuescher](https://discuss.elastic.co/u/cbuescher)\
**Post date:** [April 30, 2020, 3:57pm UTC](https://discuss.elastic.co/t/query-latest-10-parent-documents-and-corresponding-child-document-if-exists/230334/5 "2020-04-30T15:57:30Z")

</div>

Hi,

one way of doing this is to first filter your parent docs by the "join" field and sorting on the "created\_at" field. Then you want to include the corresponding child documents using ["has\_child"](https://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl-has-child-query.html) and ["inner\_hits"](https://www.elastic.co/guide/en/elasticsearch/reference/7.6/search-request-body.html#request-body-search-inner-hits):

```auto
DELETE my_index

PUT my_index
{
  "mappings": {
    "properties": {
      "song_id": {
        "type": "keyword"
      },
      "created_at" : {
        "type": "date"
      },
      "join_field": { 
        "type": "join",
        "relations": {
          "song": "review" 
        }
      }
    }
  }
}

PUT my_index/_doc/1?refresh
{
  "song_id": "song1",
  "text": "Lalala",
  "created_at" : "2020-01-01",
  "join_field": "song"
}

PUT my_index/_doc/2?refresh
{
  "song_id": "song2",
  "created_at" : "2020-01-02",
  "text": "Yeah Yeah Yeah",
  "join_field": "song"
}

PUT my_index/_doc/3?routing=1&refresh 
{
  "text": "This is an review",
  "join_field": {
    "name": "review", 
    "parent": "1" 
  }
}

PUT my_index/_doc/4?routing=1&refresh
{
  "text": "This is another review",
  "join_field": {
    "name": "review",
    "parent": "1"
  }
}

POST /my_index/_search
{
  "query": {
    "bool": {
      "filter": [
        {
          "match": {
            "join_field": "song"
          }
        }
      ],
      "should": [
        {
          "has_child": {
            "type": "review",
            "query": { "match_all": {}},
            "inner_hits": {} 
          }
        }
      ]
    }
  },
  "sort": [
    {
      "created_at": {
        "order": "desc"
      }
    }
  ]
}

```

Note that I added the "has\_child" query inside a "should" clause, this way you also get the document without any reviews, but there might be other, better ways of doing this. "inner\_hits" by default only returns 3 documents afaik, if you need more you might want to set its "size" parameter higher. There are certainly other, more complex considerations, but this might give you a good start to play with.

---

<div class="post-metadata">

**Author:** ![shashanka\_arnady](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/shashanka_arnady/32/67351_2.png) [@shashanka\_arnady](https://discuss.elastic.co/u/shashanka_arnady)\
**Post date:** [April 30, 2020, 6:18pm UTC](https://discuss.elastic.co/t/query-latest-10-parent-documents-and-corresponding-child-document-if-exists/230334/6 "2020-04-30T18:18:47Z")

</div>

This is excellent.. thanks for the help. Awesome.  
I will do some experimentts and will get back to you.

Thanks a ton

---

<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:** [May 28, 2020, 6:18pm UTC](https://discuss.elastic.co/t/query-latest-10-parent-documents-and-corresponding-child-document-if-exists/230334/7 "2020-05-28T18:18:50Z")

</div>

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