CODEWITHSUNDEEP
elasticsearch
GammaVega
GammaVega

Reputation: 779

ElasticSearch Sort on nested field across all documents

I am trying to figure out if it is possible to sort based on nested field across all documents, for eg. in below JSON, fields is a nested object, what i am looking for is to retrieve all fields.name from all documents and then sort by name field. I know i can do this using inner hit query if it is part of single document.

The other option is to retrieve all documents and then do sorting on front end/middle tier which is not a good option. 

[ {
          "id": "42ddf6e1-23f5-4aed-9d3a-1ba3ac3677b1",
          "fields": [
            {
              "name": "asofheading"
            },
            {
              "name": "SEC_TYPE"
            }
          ],
},
{
          "id": "7579928e-2196-4f7d-aaf8-4bfe9e67b330",
          "fields": [
            {
              "name": "asofheading"
            },
            {
              "name": "SEC_TYPE"
            },
            {
              "name": "CUSIP"
            }
          ]
 },
        {
          "id": "3a0940c1-7495-400c-a204-cd9bc6966fae",
          "fields": [
            {
              "name": "AsofHeading"
            },
            {
              "name": "SECT_PROFILE"
            },
            {
              "name": "SECT_PROFILESP"
            }
        },

]

I want to extract only field name from all documents and then sort by field name

Upvotes: 7

Views: 25963

Answers (2)

Niels Abildgaard
Niels Abildgaard

Reputation: 2990

For recent version (6.8 here) the syntax has changed slightly (cf. documentation), so it would now be:

POST /test_index/_search
{
    "sort" : [
       {
          "fields.name" : {
             "mode" :  "max",
             "order" : "asc",
             "nested": { "path": "fields" }
          }
       }
    ]
}

Upvotes: 5

Sloan Ahrens
Sloan Ahrens

Reputation: 8718

Use nested sorting (assuming the inner docs are of type nested).

So, for example, with the documents you provided:

POST /test_index/_search
{
    "sort" : [
       {
          "fields.name" : {
             "mode" :  "max",
             "order" : "asc",
             "nested_path" : "fields"
          }
       }
    ]
}

returns:

{
   "took": 8,
   "timed_out": false,
   "_shards": {
      "total": 5,
      "successful": 5,
      "failed": 0
   },
   "hits": {
      "total": 3,
      "max_score": null,
      "hits": [
         {
            "_index": "test_index",
            "_type": "doc",
            "_id": "2",
            "_score": null,
            "_source": {
               "id": "7579928e-2196-4f7d-aaf8-4bfe9e67b330",
               "fields": [
                  {
                     "name": "asofheading"
                  },
                  {
                     "name": "SEC_TYPE"
                  },
                  {
                     "name": "CUSIP"
                  }
               ]
            },
            "sort": [
               "sec_type"
            ]
         },
         {
            "_index": "test_index",
            "_type": "doc",
            "_id": "1",
            "_score": null,
            "_source": {
               "id": "42ddf6e1-23f5-4aed-9d3a-1ba3ac3677b1",
               "fields": [
                  {
                     "name": "asofheading"
                  },
                  {
                     "name": "SEC_TYPE"
                  }
               ]
            },
            "sort": [
               "sec_type"
            ]
         },
         {
            "_index": "test_index",
            "_type": "doc",
            "_id": "3",
            "_score": null,
            "_source": {
               "id": "3a0940c1-7495-400c-a204-cd9bc6966fae",
               "fields": [
                  {
                     "name": "AsofHeading"
                  },
                  {
                     "name": "SECT_PROFILE"
                  },
                  {
                     "name": "SECT_PROFILESP"
                  }
               ]
            },
            "sort": [
               "sect_profilesp"
            ]
         }
      ]
   }
}

Here's the code I used to test it:

http://sense.qbox.io/gist/2de98ca3e72663ce7af63c435deb5e85c6070088

Upvotes: 10

Related Questions