# Sorting on nested documents and getting the document that was used for the sort

**URL:** <https://discuss.elastic.co/t/sorting-on-nested-documents-and-getting-the-document-that-was-used-for-the-sort/111992>\
**Category:** Elasticsearch\
**Created:** [December 15, 2017, 1:34pm UTC](https://discuss.elastic.co/t/sorting-on-nested-documents-and-getting-the-document-that-was-used-for-the-sort/111992 "2017-12-15T13:34:18Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![teodor624](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/teodor624/32/25679_2.png) [@teodor624](https://discuss.elastic.co/u/teodor624)\
**Post date:** [December 15, 2017, 1:34pm UTC](https://discuss.elastic.co/t/sorting-on-nested-documents-and-getting-the-document-that-was-used-for-the-sort/111992/1 "2017-12-15T13:34:18Z")

</div>

I'm working on a project for aggregating company data and company financial reports.

Currently I have a mapping similair to this (I have skipped the fields not needed for my question):

> ```
> {
> "companies": {
> "mappings": {
> "company": {
> "properties": {
> "company_id": {
> "type": "long"
> },
> "company_name": {
> "type": "text",
> "fields": {
> "keyword": {
> "type": "keyword",
> "ignore_above": 256
> }
> }
> },
> "financial_reports": {
> "type": "nested",
> "properties": {
> "company_id": {
> "type": "long"
> },
> "created_at": {
> "type": "date"
> },
> "currency": {
> "type": "text"
> },
> "data": {
> "properties": {
> "figures": {
> "properties": {
> ...
> "total_assets": {
> "type": "float"
> },
> ...
> }
> },
> "period": {
> "type": "text",
> "fields": {
> "keyword": {
> "type": "keyword",
> "ignore_above": 256
> }
> }
> },
> }
> },
> "financial_report_id": {
> "type": "long"
> },
> "financial_report_type": {
> "type": "long"
> },
> "financial_report_type_id": {
> "type": "long"
> },
> "from_date": {
> "type": "date"
> },
> "is_consolidated": {
> "type": "boolean"
> },
> "language_id": {
> "type": "long"
> },
> "statement_date": {
> "type": "date"
> },
> "till_date": {
> "type": "date"
> },
> "updated_at": {
> "type": "date"
> }
> }
> },
> ...
> }
> }
> }
> }
> }
> 
> ```

What I am trying to do is get all companies sorted by their financial\_reports.data.figures.total\_assets descending. The specifics are that I also need to retrieve the nested document that was used in that sorting. Here is my query:

> ```
> {
> "query": {
> "bool": {
> "must": [
> {
> "nested": {
> "path": "financial_reports",
> "query": {
> "bool": {
> "must": [
> {
> "exists": {
> "field": "financial_reports.till_date"
> }
> }
> ]
> }
> }
> }
> },
> {
> "nested": {
> "path": "financial_reports",
> "query": {
> "bool": {
> "must": [
> {
> "exists": {
> "field": "financial_reports.data.figures.total_assets"
> }
> }
> ]
> }
> }
> }
> },
> {
> "nested": {
> "path": "financial_reports",
> "inner_hits": {},
> "query": {
> "bool": {
> "must": [
> {
> "match": {
> "financial_reports.is_consolidated": false
> }
> }
> ],
> "should": [],
> "minimum_should_match": 0
> }
> }
> }
> }
> ],
> "should": [
> ...
> ],
> "minimum_should_match": 2
> }
> },
> "from": 0,
> "size": 10,
> "sort": [
> {
> "financial_reports.till_date": {
> "order": "desc",
> "nested_path": "financial_reports"
> }
> },
> {
> "financial_reports.data.figures.total_assets": {
> "order": "desc",
> "nested_path": "financial_reports",
> "nested_filter": {
> "bool": {
> "must": [
> {
> "match": {
> "financial_reports.is_consolidated": false
> }
> }
> ]
> }
> }
> }
> }
> ]
> }
> 
> ```

I currently can't think of any way to retrieve the nested document because the value that the documents are sorted on (in the sort field in the query response) is aproximate.  
Can anyone lend a hand?

---

<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 12, 2018, 1:34pm UTC](https://discuss.elastic.co/t/sorting-on-nested-documents-and-getting-the-document-that-was-used-for-the-sort/111992/2 "2018-01-12T13:34:38Z")

</div>

Closed on January 12, 2018, 1:34pm UTC

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