Get document with sorted nested objects

Question

I have the following mapping: http://pastebin.com/FgtC5aaw (includes sample data)

http://localhost:9200/databases/database/1

{
 "columns": [
   {
     "name": "First column",
     "position": 1
   },
   {
     "name": "Second column",
     "position": 2
   },
   {
     "name": "Third column",
     "position": 3
   }
 ],
 "name": "Database name"
}

What I would like to get is the "database" document(s), but each one with columns sorted in descending order by columns.position field, so:

{
    "name": "Database name",
    "columns": [
        {
            "name": "Third column",
            "position": 3
        },
        {
            "name": "Second column",
            "position": 2
        },
        {
            "name": "First column",
            "position": 1
        }
    ]
}

I tried using "sort" on "columns.position", but I think it would just sort all "database" documents on it's nested columns.

Is such a result possible in Elasticsearch?

Val · Accepted Answer · 2015-09-02 03:42:27Z

If you know in advance you'll always need to retrieve the columns nested object in decreasing order of columns.position, you can simply index your database documents with the columns list already properly sorted.

If that's not the case, there is a way to sort the nested columns list using inner_hits like this:

curl -XPOST localhost:9200/databases/database/_search -d '{
  "_source": false,            <---- set this to true to get database fields too
  "query": {
    "nested": {
      "path": "columns",
      "query": {
        "match_all": {}
      },
      "inner_hits": {
        "sort": {
          "columns.position": "desc"
        }
      }
    }
  }
}'

Which will yield:

{
  ...
  "hits" : {
    "total" : 1,
    "max_score" : 1.0,
    "hits" : [ {
      "_index" : "databases",
      "_type" : "database",
      "_id" : "1",
      "_score" : 1.0,
      "inner_hits" : {
        "columns" : {
          "hits" : {
            "total" : 3,
            "max_score" : null,
            "hits" : [ {
              "_index" : "databases",
              "_type" : "database",
              "_id" : "1",
              "_nested" : {
                "field" : "columns",
                "offset" : 2
              },
              "_score" : null,
              "_source":{"name":"Third column","position":3},
              "sort" : [ 3 ]
            }, {
              "_index" : "databases",
              "_type" : "database",
              "_id" : "1",
              "_nested" : {
                "field" : "columns",
                "offset" : 1
              },
              "_score" : null,
              "_source":{"name":"Second column","position":2},
              "sort" : [ 2 ]
            }, {
              "_index" : "databases",
              "_type" : "database",
              "_id" : "1",
              "_nested" : {
                "field" : "columns",
                "offset" : 0
              },
              "_score" : null,
              "_source":{"name":"First column","position":1},
              "sort" : [ 1 ]
            } ]
          }
        }
      }
    } ]
  }
}

Collectives™ on Stack Overflow

Get document with sorted nested objects

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related