Elasticsearch: Sort on different fields depending on type -

i have 2 types in index (event , city) , i'm trying sort them date. date's field name different each type: event value in updated_at field , city date in update_at field in 1 of nested objects of city_events nested object array (note filtering region_id).

i've tried specifying each field in sort array this:

  "sort": [     {       "city_events.updated_at": {         "order": "desc",         "nested_path": "city_events",         "nested_filter": {           "term": {             "city_events.region_id": 1           }         }       }     },     {       "updated_at": "desc"     }   ] 

but unfortunately doesn't mix 2 types together. instead, first sorts cities nested city_events.updated_at field , appends events @ bottom sorted updated_at field. how mix , sort 2 together?

as alternative solution i've tried sorting nested city_events.updated_at field , specifying "missing": "updated_at", threw "number_format_exception" error despite both fields being in same format:

{   "error": {     "root_cause": [       {         "type": "number_format_exception",         "reason": "for input string: \"updated_at\""       }     ],     "type": "search_phase_execution_exception",     "reason": "all shards failed",     "phase": "query_fetch",     "grouped": true,     "failed_shards": [       {         "shard": 0,         "index": "events_1461095196252",         "node": "syqstsw_sn62ojmxggjplg",         "reason": {           "type": "number_format_exception",           "reason": "for input string: \"updated_at\""         }       }     ]   },   "status": 400 } 

update 1: based on the answer andrei stefan below i've tried developing groovy script looped on city_events each city document selecting 1 matching region_id , returning city_event's updated_at value scoring, had problems accessing nested fields within script: https://stackoverflow.com/questions/36781476/elasticsearch-access-fields-inside-array-of-nested-objects-in-a-groovy-script

try script based sorting, , need nested field have include_in_parent: true accessible in script:

    "city_events": {       "type": "nested",       "include_in_parent": true,        "properties": {         "updated_at": {           "type": "date"         }       }     } 

and sorting part:

  "sort": {     "_script": {       "type": "number",       "script": {         "inline": "if (doc['_type'].value=='event') return doc['updated_at'].date.getmillis(); else if (doc['_type'].value=='city') return doc['city_events.updated_at'].date.getmillis()",         "lang": "groovy"       },       "order": "desc"     }   } 

---

later edit

even if add city_events.region_id==1 condition groovy script, not feel elasticsearch, pure groovy programming , not power of elasticsearch.

i've tried other approaches (all in es 2.3.1):

• copy_to regular updated_at field nested field inside event, regular nested sorting performed on types. didn't work.
• even if copy_to have worked, elasticsearch wouldn't have matched "term": {"city_events.region_id": 1} (as region_id doesn't exist in event) sort part in event type , values have used -9223372036854776000 instead of actual date (that values comes tests performed).
• use nested field in event , @ indexing time, put updated_at in nested field. not work same reason attempt #2 above: there has region_id in event nested filter sort part apply both types.

what suggest, proper way of dealing this, re-think bit data structure sorting part (at least) follow elasticsearch way of doing things. types called city , event , inside city have list of (nested) city_events. can't include event in city , duplicate events' details in each city? doesn't have normalized, rdb data structure. on contrary, es happier non-normalized data.

---

for completeness sake i don't recommend this:

  "sort": {     "_script": {       "type": "number",       "script": {         "inline": "if (doc['_type'].value=='event') return doc['updated_at'].date.getmillis(); else if (doc['_type'].value=='city') {for(nestedobj in _source.city_events) {if(nestedobj.region_id==1) return nestedobj.updated_at.tolong();}}",         "lang": "groovy"       },       "order": "desc"     }   } 

note haven't done proper checks in groovy script above (checking if there nested objects in document example).