title: Elasticsearch - how to do field collapsing and get Distinct results? (actual records, not just counters)
body: <p>In relational db our data looks like this:
  Company -> Department -> Office</p>
  
  <p>Elasticsearch version of the same data (flattened):</p>
  
  <pre><code>{
       "officeID": 123,
       "officeName": "office 1",
       "state": "CA",
       "department": {
            "departmentID": 456,
            "departmentName": "Department 1",
            "company": {
                  "companyID": 789,
                  "companyName": "Company 1",
            }
       } 
  },{
       "officeID": 124,
       "officeName": "office 2",
       "state": "CA",
       "department": {
            "departmentID": 456,
            "departmentName": "Department 1",
            "company": {
                "companyID": 789,
                "companyName": "Company 1",
            }
        }}
  </code></pre>
  
  <p>We need to find department (or company) by providing office information (such as state).</p>
  
  <p>For example, since all I need is a department info, I can specify it like this (we are using Nest)</p>
  
  <pre><code>searchDescriptor = searchDescriptor.Source(x =&gt; x.Include("department"));
  </code></pre>
  
  <p>and get all departments with qualifying offices.</p>
  
  <p>The problem is - I am getting multiple "department" records with the same id (one for each office).</p>
  
  <p>We are using paging and sorting.</p>
  
  <p>Would it be possible to get paged and sorted <strong>Distinct</strong> results? </p>
  
  <p>I have spent a few days trying to find an answer (exploring options like facets, aggregations, top_hits etc) but so far the only working option I see would be a manual one - <strong>get results from Elasticsearch, group data manually and pass to the client</strong>. The problem with this approach is obvious - every time I grab next portion, I'll have to get X extra records just in case some of the records will be duplicate; since I don't know X in advance (and number of such records could be huge)  will be forced either to get lots of data unnecessarily (every time I do the search) or to hit our search engine several times until I get required number of records.</p>
  
  <p>So far I was unable to achieve my goal using aggregations (all I am getting is document count, but I want actual data; when I try to use top_hits, I am getting data, but those are really top hits (sorted by number of offices per department, ignoring sorting I have specified in the query); here is an example of the code I tried:</p>
  
  <pre><code>            searchDescriptor = searchDescriptor.Aggregations(a =&gt; a
              .Terms("myunique",
                  t =&gt;
                      t.Field("department.departmentID")
                      .Size(10)
                      .Aggregations(
                          x=&gt;x.TopHits("mytophits", 
                              y=&gt;y.Source(true)
                                  .Size(1)
                                  .Sort(k =&gt; k.OnField("department.departmentName").Ascending())
                                  )
                              )
                          )
              );
  </code></pre>
  
  <p>Does anyone know if Elasticsearch can perform operations like Distinct and get unique records?</p>
  
  <p><strong><em>Update</em></strong>:
  I can get results using top_hits (see below), but in this case I won't be able to use paging (looks like Elasticsearch aggregations feature doesn't support paging), so I am back to square one...</p>
  
  <pre><code>{
    "from": 0,
    "size": 33,
    "explain": false,
    "sort": [
      {
        "departmentID": {
          "order": "asc"
        }
      }
    ],
    "_source": {
      "include": [
        "department"
      ]
    },
    "aggs": {
      "myunique": {
        "terms": {
          "field": "department.departmentID",
          "order": {
            "mytopscore": "desc"
          }
        },
        "aggs": {
          "mytophits": {
            "top_hits": {
              "size": 5,
              "_source": {
                "include": [
                  "department.departmentID"
                ]
              }
            }
          },
          "mytopscore": {
            "max": {
              "script": "_score"
            }
          }
        }
      }
    },
    "query": {
          "wildcard" : { "officeName" : "some office*" }
    } 
  }
  </code></pre>