How to do script sorting with inner_hits?

[yanlin.liu]

I have an index with template like this:
"properties": {
"id": {"type": "long"},
"connections": {
"type": "nested",
"properties": {
"connectionId": {"type": "long"},
"targetId": {"type": "long"},
"archive": {"type": "boolean"},
"conType": {"type": "keyword"},
"maxDateTime": {"type": "date", "format": "strict_date_optional_time||epoch_millis"},
"userActionDateTimes": {
"type": "nested",
"properties": {
"userId": {"type": "long"},
"actionDateTime": {"type": "date", "format": "strict_date_optional_time||epoch_millis"}
}
}
}
}
}
So a document would be something like this:
{
"id": 1,
"connections": [
{
"connectionId": 11,
"targetId": 101,
"archive": false,
"maxDateTime": 1541508938,
"userActionDateTimes": [
{
"userId": 123,
"actionDateTime": null
},
{
"userId": 456,
"actionDateTime": 1541508900
}
]
},
{
"connectionId": 12,
"targetId": 102,
"archive": false,
"maxDateTime": 1541508940,
"userActionDateTimes": [
{
"userId": 123,
"actionDateTime": 1541508939
},
{
"userId": 789,
"actionDateTime": null
}
]
},
{
"connectionId": 13,
"targetId": 103,
"archive": false,
"maxDateTime": 1541508800,
"userActionDateTimes": [
{
"userId": 123,
"actionDateTime": 1541508700
},
{
"userId": 456,
"actionDateTime": 1541508700
},
{
"userId": 789,
"actionDateTime": null
},
]
}
]
}

We can do query like this:

1. Our query would have conditions on the fields of nested "connections", such as targetId [101, 102], archive false.
2. We also use inner_hits to return only the "connections" that match the search criteria.

But now we want to sort the root documents base on the returned connections with a given “userId”. The conditions are:

1. For each root document, we get the max “actionDateTime” of the connections that match the search criteria.
2. For each connection that match the search criteria, the “actionDateTime” is calculated with the following rules:
   a. If userActionDateTimes is null or empty or within userActionDateTimes there is no nested document with userId that matches the provided userId, return “maxDateTime” of the connection.
   b. If userActionDateTimes contain the provided “userId”, return the value of that “actionDateTime”.
3. If some of the matched connections return not null values, return the max of those not null values and ignore all nulls.
4. If all of the matched connections return nulls, when sorting with “asc” order, it should be at the end and when sorting with “desc” sort, it should be at the front.
   Right now, with the following query I am able to get the max value of matched connections with matched userId. But I am not able to include rule (a).
   I think I might need to use a script to get the real "maxActionDateTime" value of each connection.
   But I cannot find a way to do it. Any idea how I can make it work with all the rules? Thanks a lot.
   {
   "size":0,
   "query": {
   "bool": {
   "must": [
   {
   "nested": {
   "query": {
   "bool": {
   "must": [
   {
   "match": {
   "connections.archive": "false"
   }
   },
   {
   "terms": {
   "connections.targetId": [
   101,
   102
   ]
   }
   }
   ]
   }
   },
   "path": "connections",
   "inner_hits": {
   }
   }
   }
   ]
   }
   },
   "aggregations": {
   "sortTerms": {
   "terms": {
   "field": "id",
   "size": 100000
   },
   "aggregations": {
   "items": {
   "top_hits": {
   "from": 0,
   "size": 3,
   "_source": {
   "includes": ,
   "excludes": [
   "connections"
   ]
   }
   }
   },
   "nestedAggregation": {
   "nested": {
   "path": "connections"
   },
   "aggregations": {
   "filterAggregation": {
   "filter": {
   "bool": {
   "must": [
   {
   "match": {
   "connections.archive": "false"
   }
   },
   {
   "terms": {
   "connections.targetId": [
   101, 102
   ]
   }
   }
   ]
   }
   },
   "aggregations": {
   "nestedFilterAggregation": {
   "nested": {
   "path": "connections.userActionDateTimes"
   },
   "aggs": {
   "secondFilter": {
   "filter": {
   "bool": {
   "must": [
   {
   "match": {
   "connections.userActionDateTimes.userId": 123
   }
   }
   ]
   }
   },
   "aggs": {
   "maxActionDateTime": {
   "max": {
   "field": "connections.userActionDateTimes.actionDateTime",
   "missing": 0
   }
   }
   }
   }
   }
   }
   }
   }
   }
   },
   "conditionSort": {
   "bucket_sort": {
   "sort": [
   {
   "nestedAggregation>filterAggregation>nestedFilterAggregation>secondFilter>maxActionDateTime": {
   "order": "desc"
   }
   }
   ],
   "from": 0,
   "size": 10
   }
   }
   }
   }
   }
   }