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

Elastic Stack Elasticsearch
1

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?

2

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