# Retrieving parent document according to relations between child documents

**URL:** <https://discuss.elastic.co/t/retrieving-parent-document-according-to-relations-between-child-documents/16699>\
**Category:** Elasticsearch\
**Created:** [March 30, 2014, 11:24am UTC](https://discuss.elastic.co/t/retrieving-parent-document-according-to-relations-between-child-documents/16699 "2014-03-30T11:24:53Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![Tzahi](https://avatars.discourse-cdn.com/v4/letter/t/b77776/32.png) [@Tzahi](https://discuss.elastic.co/u/Tzahi)\
**Post date:** [March 30, 2014, 11:24am UTC](https://discuss.elastic.co/t/retrieving-parent-document-according-to-relations-between-child-documents/16699/1 "2014-03-30T11:24:53Z")

</div>

I am new to ES – so, please bear with me.

My data model is parent-child relationship.

The parent document contains attributes of people. The child document  
contains time and location for that person. In a relational model, it would  
look like:

Create Table Parent (

```
            personId int,

            personName varchar);

```

Create table child (

```
            personId Int,

            Location varchar,

            detectionTime dateTime);

```

A possible query on this model is:

"A person named X that was spotted at location A, and then, within 10  
minutes, was spotted at location B"

In SQL, it would look like:

"select personId, C1.detectionTime

From person, child as C1, child as C2

Where

Parent.personId = C1.personId,

Parent.personId = C2.personId,

C1.location = A,

C2.location = B,

personName = X,

C2.detectionTime between C1.detectionTime and C1.detectionTime + 10  
(minutes);"

The "between" part of the query is the problem. No retrieval system that I  
am aware of can do it.

I guess the way to ask it is to request a parent document with name=X,  
that has child document\s with location A, and child document\s with  
location B. Once the parent and child documents are retrieved – the  
requesting program will filter the results that do not match the "within 10  
minutes" condition.

This solution is far from optimal:

1. 

```
  Wasted bandwidth in returning documents that will be filtered out.

```

2. 

```
  Wasted computation on ranking and sorting those documents

```

3. 

```
  Invalidates facets

```

I there a way do the filtering at the shard level? (Even if it requires  
programming)

--  
You received this message because you are subscribed to the Google Groups "elasticsearch" group.  
To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com).  
To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/0eee6b66-c5b4-41d0-9eb7-c5b99d272988%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/0eee6b66-c5b4-41d0-9eb7-c5b99d272988%40googlegroups.com).  
For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout).

---

<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:** [July 6, 2017, 1:39am UTC](https://discuss.elastic.co/t/retrieving-parent-document-according-to-relations-between-child-documents/16699/2 "2017-07-06T01:39:40Z")

</div>

Closed on July 6, 2017, 1:39am UTC