CODEWITHSUNDEEP
sortingelasticsearch
szymond
szymond

Reputation: 1310

Sorting documents by value in one of nested documents

I am having problems with sorting documents based on value in selected nested document. I am using such setup:

curl -XPUT 'http://127.0.0.1:9200/test/' -d '
index :
    number_of_shards : 1
    number_of_replicas : 1
'


curl -XPUT '127.0.0.1:9200/test/item/_mapping' -d '
{
"item" : {
 "properties" : {
  "name" : {"type" : "string", "store": "yes"},
  "children" : {
   "properties" : {
     "name" : {"type" : "string", "store": "yes"},
     "id" : {"type" : "integer", "store": "yes"},
     "size" : {"type" : "integer", "store": "yes"}
   },
   "type": "nested"
  }
 }
}
}' 


curl -XPUT 'http://localhost:9200/test/item/1' -d '{
    "name" : "item1",
    "children": [
      {
        "id": 11,
        "size": 15
      }, 
      {
        "id":3,
        "size": 6
      }
     ]
    }
}'
curl -XPUT 'http://localhost:9200/test/item/2' -d '{
    "name" : "item2",
    "children": [
      {
        "id": 1,
        "size": 2
      }, 
      {
        "id":3,
        "size": 6
      }
     ]
    }
}'
curl -XPUT 'http://localhost:9200/test/item/3' -d '{
    "name" : "item3",
    "children": [
      {
        "id": 1,
        "size": 7
      }, 
      {
        "id":3,
        "size": 36
      }
     ]
    }
}'
curl -XPUT 'http://localhost:9200/test/item/4' -d '{
    "name" : "item4",
    "children": [
      {
        "id": 1,
        "size": 11
      }, 
      {
        "id":3,
        "size": 16
      }
     ]
    }
}'

What I am trying to retrieve are documents with selected children id which would be sorted by selected children size. So the query looks like:

    curl -XGET 'http://127.0.0.1:9200/test/item/_search?pretty=1'  -d '
{
  "query": {
    "filtered": {
      "query": {
        "match_all": {}
      },
      "filter": {
        "nested": {
          "filter": {
            "term": {
              "id": 1
            }
          },
          "path": "children"
        }
      }
    }
  },
  "sort": [
    {
      "children.size": {
        "order": "asc",
        "nested_filter": {
          "nested": {
            "filter": {
              "term": {
                "id": 1
              }
            },
            "path": "children"
          }
        }
      }
    }
  ]
}
'

In this query no matter what I put into "order" field (asc or desc), the returned documents are in same order. What might be a problem?

Upvotes: 3

Views: 1140

Answers (1)

Sloan Ahrens
Sloan Ahrens

Reputation: 8718

It looks like you the way you've structured your nested filter isn't correct. What you have listed here didn't work for me either.

But when I replaced this:

"sort": [
    {
      "children.size": {
        "order": "asc",
        "nested_filter": {
          "nested": {
            "filter": {
              "term": {
                "id": 1
              }
            },
            "path": "children"
          }
        }
      }
    }
]

with this:

"sort": [
   {
      "children.size": {
         "order": "desc",
         "nested_filter": {
            "term": {
               "id": 1
            }
         }
      }
   }
]

it worked.

More precisely, I built the index and added your data:

DELETE /test_index

PUT /test_index/
{
    "settings": {
        "number_of_shards": 1,
        "number_of_replicas": 0
    }
}

PUT /test_index/item/_mapping
{
   "item": {
      "properties": {
         "name": {
            "type": "string",
            "store": "yes"
         },
         "children": {
            "properties": {
               "name": {
                  "type": "string",
                  "store": "yes"
               },
               "id": {
                  "type": "integer",
                  "store": "yes"
               },
               "size": {
                  "type": "integer",
                  "store": "yes"
               }
            },
            "type": "nested"
         }
      }
   }
}

PUT /test_index/item/1
{"name":"item1","children":[{"id":11,"size":15},{"id":3,"size":6}]}

PUT /test_index/item/2
{"name":"item2","children":[{"id":1,"size":2},{"id":3,"size":6}]}

PUT /test_index/item/3
{"name":"item3","children":[{"id":1,"size":7},{"id":3,"size":36}]}

PUT /test_index/item/4
{"name":"item4","children":[{"id":1,"size":11},{"id":3,"size":16}]}

Then searched as follows, with "order": "desc", and it seems to work as expected:

POST /test_index/item/_search
{
   "query": {
      "filtered": {
         "query": {
            "match_all": {}
         },
         "filter": {
            "nested": {
               "filter": {
                  "term": {
                     "id": 1
                  }
               },
               "path": "children"
            }
         }
      }
   },
   "sort": [
      {
         "children.size": {
            "order": "desc",
            "mode": "avg",
            "nested_filter": {
               "term": {
                  "id": 1
               }
            }
         }
      }
   ]
}
...
{
   "took": 2,
   "timed_out": false,
   "_shards": {
      "total": 1,
      "successful": 1,
      "failed": 0
   },
   "hits": {
      "total": 3,
      "max_score": null,
      "hits": [
         {
            "_index": "test_index",
            "_type": "item",
            "_id": "4",
            "_score": null,
            "_source": {
               "name": "item4",
               "children": [
                  {
                     "id": 1,
                     "size": 11
                  },
                  {
                     "id": 3,
                     "size": 16
                  }
               ]
            },
            "sort": [
               11
            ]
         },
         {
            "_index": "test_index",
            "_type": "item",
            "_id": "3",
            "_score": null,
            "_source": {
               "name": "item3",
               "children": [
                  {
                     "id": 1,
                     "size": 7
                  },
                  {
                     "id": 3,
                     "size": 36
                  }
               ]
            },
            "sort": [
               7
            ]
         },
         {
            "_index": "test_index",
            "_type": "item",
            "_id": "2",
            "_score": null,
            "_source": {
               "name": "item2",
               "children": [
                  {
                     "id": 1,
                     "size": 2
                  },
                  {
                     "id": 3,
                     "size": 6
                  }
               ]
            },
            "sort": [
               2
            ]
         }
      ]
   }
}

Here is the code I used:

http://sense.qbox.io/gist/1582560ed13bec82dc321944a639336ad7ae6a60

Upvotes: 1

Related Questions