# Aggregation with additional document fields? (Like a SQL inner join to nested group by)

**URL:** <https://discuss.elastic.co/t/aggregation-with-additional-document-fields-like-a-sql-inner-join-to-nested-group-by/321767>\
**Category:** Elasticsearch\
**Tags:** language-clients\
**Created:** [December 21, 2022, 12:43pm UTC](https://discuss.elastic.co/t/aggregation-with-additional-document-fields-like-a-sql-inner-join-to-nested-group-by/321767 "2022-12-21T12:43:23Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![rd8388](https://avatars.discourse-cdn.com/v4/letter/r/c6cbf5/32.png) [@rd8388](https://discuss.elastic.co/u/rd8388)\
**Post date:** [December 21, 2022, 12:43pm UTC](https://discuss.elastic.co/t/aggregation-with-additional-document-fields-like-a-sql-inner-join-to-nested-group-by/321767/1 "2022-12-21T12:43:23Z")

</div>

Thanks in advance for any help with achieving our goal or on the terminology I may use to find an example in Elasticsearch documentation and/or forum.

When performing a Max Terms aggregation to get the date of the newest doc for each specified doc field, how would we also bring back other field values for each document?

In SQL you would perform an INNER JOIN against the table to bring back the row that matches the GROUP BY aggregated maximum.

e.g.

```auto
SELECT one, two, three, four FROM
(SELECT one, max(four) AS MaxTime
FROM mytable GROUP BY one) AS agg
INNER JOIN mytable AS mt
ON mt.one = agg.one AND mt.four = agg.MaxTime

```

How do you achieve this in Elasticsearch please?  
We have the aggregation for the nested group by working, we just aren't sure how to also get the additional properties back at the same time.

(Examples using NEST C# would be awesome, thank you, but knowing how to achieve in JSON/Kibana would hopefully point us in the right direction too.)

---

<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:** [January 18, 2023, 12:43pm UTC](https://discuss.elastic.co/t/aggregation-with-additional-document-fields-like-a-sql-inner-join-to-nested-group-by/321767/2 "2023-01-18T12:43:38Z")

</div>

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