# Aggregate from one doc table to select items from another

**URL:** <https://discuss.elastic.co/t/aggregate-from-one-doc-table-to-select-items-from-another/238902>\
**Category:** Elasticsearch\
**Created:** [June 26, 2020, 6:28pm UTC](https://discuss.elastic.co/t/aggregate-from-one-doc-table-to-select-items-from-another/238902 "2020-06-26T18:28:24Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![CapitanRedBeard](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/capitanredbeard/32/33034_2.png) [@CapitanRedBeard](https://discuss.elastic.co/u/CapitanRedBeard)\
**Post date:** [June 26, 2020, 6:28pm UTC](https://discuss.elastic.co/t/aggregate-from-one-doc-table-to-select-items-from-another/238902/1 "2020-06-26T18:28:24Z")

</div>

Consider the following, I have a ratings and posts index.

The ratings doc is structured like:

```auto
    {
         "postId": {
             "type": "text",
             "fields": {
             "keyword": {
               "type": "keyword",
               "ignore_above": 256
             }
           },
           "analyzer": "keyword",
           "fielddata": true
        },
        "value": {
            "type": "long"
        }
    }

```

The posts doc is structured like:

```auto
    {
         "postId": {
             "type": "text",
             "fields": {
             "keyword": {
               "type": "keyword",
               "ignore_above": 256
             }
           },
           "analyzer": "keyword",
           "fielddata": true
        },
        ... other fields
    }

```

Is it possible in a single query to aggregate all the ratings, sort them by highest value field and then return the posts documents that are associated with each of those values?

I've needed to do this for awhile now but have always just done extra steps to add the aggregated value field to the posts document whenever it gets updated. This doesn't seem to scale the best and it would be wonderful if I could accomplish this in a single query.

I'm using ES v6.6.2

Thanks!

---

<div class="post-metadata">

**Author:** ![Rahul\_Kumar4](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/rahul_kumar4/32/67369_2.png) [@Rahul\_Kumar4](https://discuss.elastic.co/u/Rahul_Kumar4)\
**Post date:** [June 26, 2020, 7:26pm UTC](https://discuss.elastic.co/t/aggregate-from-one-doc-table-to-select-items-from-another/238902/2 "2020-06-26T19:26:11Z")

</div>

I guess you are trying to denormalize the data that is already inside the elasticsearch in two different indices and doing an SQL equivalent of something like below:

```auto
SELECT 
posts.*, 
ratings.rating_value

From ratings 
LEFT OUTER JOIN posts 
ON ratings.post_id = posts.post_id 
ORDER BY ratings.value DESC; 

```

The problem, as documented in the Elastic blog below, is the limited SQL JOIN capability which can only be done on fields of data type `nested` in one index which does not seem to be in your case.

> **[An Introduction to Elasticsearch SQL with Practical Examples - Part 2](https://www.elastic.co/blog/an-introduction-to-elasticsearch-sql-with-practical-examples-part-2)**
>
> Part 2 in our series about learning to use Elasticsearch SQL. Take it for a spin in our demo environment with practical examples.

Even their latest docs seem to limit the JOIN type for a single index for nested and parent/child relationships  
[https://www.elastic.co/guide/en/elasticsearch/reference/current/joining-queries.html](https://www.elastic.co/guide/en/elasticsearch/reference/current/joining-queries.html)

I think expectation/understanding here is that JOINs tends to shuffle data between nodes and Elastisearch wants to discourage that as that potentially interferes with cluster performance so your best bet could be to:

1. Denormalize your data outside of Elasticsearch and then do the indexing. Outside would mean not to rely on using Logstash's Elasticsearch filter plugin because yes as you mentioned the look ups become expensive as your data volume grows. I have run into this situation too.
2. Have your posts and ratings all live in a single index with a child/parent nested relationship which could help you do the JOIN since there won't be any shuffling then.

I am curious what the Elasticsearch experts have to say on this topic.

---

<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 24, 2020, 7:26pm UTC](https://discuss.elastic.co/t/aggregate-from-one-doc-table-to-select-items-from-another/238902/3 "2020-07-24T19:26:14Z")

</div>

Closed on July 24, 2020, 7:26pm UTC

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