# Efficient way to query Elasticsearch

**URL:** <https://discuss.elastic.co/t/efficient-way-to-query-elasticsearch/220094>\
**Category:** Elasticsearch\
**Created:** [February 20, 2020, 6:06am UTC](https://discuss.elastic.co/t/efficient-way-to-query-elasticsearch/220094 "2020-02-20T06:06:29Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![sidshirke](https://avatars.discourse-cdn.com/v4/letter/s/c67d28/32.png) [@sidshirke](https://discuss.elastic.co/u/sidshirke)\
**Post date:** [February 20, 2020, 6:06am UTC](https://discuss.elastic.co/t/efficient-way-to-query-elasticsearch/220094/1 "2020-02-20T06:06:29Z")

</div>

Below is the short version of the dataset I am working on.

```auto
ID TYPE DATA SERIES_ID VALIDITY 
MOVIE_1 MOVIE MOVIE_DATA_1 NULL 2000-2050
MOVIE_2 MOVIE MOVIE_DATA_2 NULL 2000-2050
SERIES_1 TV_SERIES SERIES_DATA_1 NULL 2000-2050
SERIES_2 TV_SERIES SERIES_DATA_2 NULL 2000-2050
SERIES_3 TV_SERIES SERIES_DATA_3 NULL 2000-2050
EPISODE_1 EPISODE EPISODE_DATA_1 SERIES_1 2000-2050
EPISODE_2 EPISODE EPISODE_DATA_2 SERIES_2 2000-2010 

```

I want to write a query which returns all the documents of TYPE = (MOVIE, TV\_SERIES) that are valid (meaning the present year (2020) is between the years specified in VALIDITY). However, while selecting TV\_SERIES it should also check if that TV\_SERIES has an EPISODE that is valid.

So for the above dataset the query should return

```auto
ID TYPE DATA SERIES_ID VALIDITY 
MOVIE_1 MOVIE MOVIE_DATA_1	NULL 2000-2050 (since it is valid)
MOVIE_2 MOVIE MOVIE_DATA_2	NULL 2000-2050 (since it is valid)
SERIES_1	TV_SERIES	SERIES_DATA_1	NULL 2000-2050 (since it has EPISODE_1 which is valid)

```

SERIES\_2 SHOULD NOT be returned since its episode (EPISODE\_2) is not valid for present year

SERIES\_3 SHOULD NOT be returned since it does not have any episodes.

---

<div class="post-metadata">

**Author:** ![spinscale](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/spinscale/32/25011_2.png) [@spinscale](https://discuss.elastic.co/u/spinscale)\
**Post date:** [February 20, 2020, 9:18am UTC](https://discuss.elastic.co/t/efficient-way-to-query-elasticsearch/220094/2 "2020-02-20T09:18:55Z")

</div>

This is merely a question around data modeling, and thus has the capacity of dozens of different answers.

Episodes being children of series is something that requires modeling, either using the join datatype or the nested datatype - with the latter you store all the episodes within the series document.

This way you could search for movies with validity OR series with a valid episode using a nested query.

Validity could be modeled using a range datatype.

hope that helps to get started.

---

<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 19, 2020, 9:18am UTC](https://discuss.elastic.co/t/efficient-way-to-query-elasticsearch/220094/3 "2020-03-19T09:18:57Z")

</div>

Closed on March 19, 2020, 9:18am UTC

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