Hi all,
This is a problem related to https://github.com/elastic/elasticsearch/issues/9305.
The inner nested filter described in the issue works in Elasticsearch 1.5 (as following query 1).
However, it doesn't work when I use the workstation level filed for sorting and use users level filter in nested_filter (as following query 2).
The mapping is described as below:
There is a nested users field inside mytype and an workstation nested field inside users.
curl -XPOST localhost:9200/my_test_sort -d '{"mappings":{"mytype":{"properties":{"officelocation":{"type":"string"},"users":{"type":"nested","properties":{"first":{"type":"string"},"last":{"type":"string"},"workstation":{"type":"nested","properties":{"stationid":{"type":"string"},"phoneid":{"type":"string"}}}}}}}}}'
document 1:
curl -XPOST localhost:9200/my_test_sort/mytype/1 -d '{"officelocation":"glendale","users":[{"first":"fname1","last":"lname1","workstation":[{"stationid":"s1","phoneid":"p1"},{"stationid":"s2","phoneid":"p2"}]},{"first":"fname2","last":"lname2","workstation":[{"stationid":"s3","phoneid":"p3"},{"stationid":"s4","phoneid":"p4"}]},{"first":"fname3","last":"lname3","workstation":[{"stationid":"s5","phoneid":"p5"},{"stationid":"s6","phoneid":"p6"}]}]}'
document 2:
curl -XPOST localhost:9200/my_test_sort/mytype/2 -d '{"officelocation":"glendale","users":[{"first":"fname4","last":"lname4","workstation":[{"stationid":"s1","phoneid":"p1"},{"stationid":"s2","phoneid":"p2"}]},{"first":"fname5","last":"lname5","workstation":[{"stationid":"s3","phoneid":"p3"},{"stationid":"s4","phoneid":"p4"}]},{"first":"fname1","last":"lname1","workstation":[{"stationid":"s5","phoneid":"p5"},{"stationid":"s6","phoneid":"p6"}]}]}'
query 1 (sort by user level filed but filter by workstation level filter) :
curl -XPOST localhost:9200/my_test_sort/_search? -d '{"sort":[{"users.first":{"order":"asc", "mode":"max", "nested_filter":{"nested": {"path": "users.workstation","filter": {"term":{"users.workstation.stationid":"s5"}}}}}}]}'
This correctly return the two documents as following:
{"took":3,"timed_out":false,"_shards":{"total":5,"successful":5,"failed":0},"hits":{"total":2,"max_score":null,"hits":[{"_index":"my_test_sort","_type":"mytype","_id":"2","_score":null,"_source":{"officelocation":"glendale","users":[{"first":"fname4","last":"lname4","workstation":[{"stationid":"s1","phoneid":"p1"},{"stationid":"s2","phoneid":"p2"}]},{"first":"fname5","last":"lname5","workstation":[{"stationid":"s3","phoneid":"p3"},{"stationid":"s4","phoneid":"p4"}]},{"first":"fname1","last":"lname1","workstation":[{"stationid":"s5","phoneid":"p5"},{"stationid":"s6","phoneid":"p6"}]}]},"sort":["fname1"]},{"_index":"my_test_sort","_type":"mytype","_id":"1","_score":null,"_source":{"officelocation":"glendale","users":[{"first":"fname1","last":"lname1","workstation":[{"stationid":"s1","phoneid":"p1"},{"stationid":"s2","phoneid":"p2"}]},{"first":"fname2","last":"lname2","workstation":[{"stationid":"s3","phoneid":"p3"},{"stationid":"s4","phoneid":"p4"}]},{"first":"fname3","last":"lname3","workstation":[{"stationid":"s5","phoneid":"p5"},{"stationid":"s6","phoneid":"p6"}]}]},"sort":["fname3"]}]}}
query 2:
curl -XPOST localhost:9200/my_test_sort/_search? -d '{"sort":[{"users.workstation.phoneid":{"order":"asc", "mode":"max", "nested_filter":{"nested": {"path": "users","filter": {"term":{"users.first":"fname1"}}}}}}]}'
The query failed with the error message below:
{"took":9,"timed_out":false,"_shards":{"total":5,"successful":4,"failed":1,"failures":[{"index":"my_test_sort","shard":2,"status":500,"reason":"RemoteTransportException[[myeshost]][indices:data/read/search[phase/query]]]; nested: QueryPhaseExecutionException[[my_test_sort][2]: query[ConstantScore(cache(org.elasticsearch.index.search.nested.NonNestedDocsFilter@7d5d21e9))],from[0],size[10],sort[<custom:\"users.workstation.phoneid\": org.elasticsearch.index.fielddata.fieldcomparator.BytesRefFieldComparatorSource@375ea54d>]: Query Failed [Failed to execute main query]]; nested: UncheckedExecutionException[java.lang.ArrayIndexOutOfBoundsException]; nested: ArrayIndexOutOfBoundsException; "}]},"hits":{"total":1,"max_score":null,"hits":[{"_index":"my_test_sort","_type":"mytype","_id":"2","_score":null,"_source":{"officelocation":"glendale","users":[{"first":"fname4","last":"lname4","workstation":[{"stationid":"s1","phoneid":"p1"},{"stationid":"s2","phoneid":"p2"}]},{"first":"fname5","last":"lname5","workstation":[{"stationid":"s3","phoneid":"p3"},{"stationid":"s4","phoneid":"p4"}]},{"first":"fname1","last":"lname1","workstation":[{"stationid":"s5","phoneid":"p5"},{"stationid":"s6","phoneid":"p6"}]}]},"sort":["p4"]}]}}
Did I make a mistake in query 2?
Or nested filters with parent nested object are not supported for sorting by nested field in Elasticsearch?
Thanks in advance!
Angela