Order by timeUpdated, if exists, otherwise timeCreated

Question

I have 2 fields in an elastic type, namely timeCreated and timeUpdated. Now my business case is that we should always order by timeUpdated, unless it's null (or non-existent), which then obviously leaves us with timeCreated, which is always set.

Now by my understanding of how sorting in elasticsearch works, the following won't work because it'll sort by timeUpdated AND timeCreated together, and not in the way I'm looking for.

"query": {
    "match_all": {}
},
"sort": {
    "timeUpdated": {"order": "desc", "missing": "_last"},
    "timeCreated": {"order": "desc"}
}

I'm not really sure whether this is possible or not, but elastic is awesome, so I'm hoping it's a simple thing that I missed in the docs.

I'm using the latest version (1.6 I believe) with PHP using Elastica (though that makes zero difference to the original question).

EDIT

As an example, if I have 3 documents:

1 - {"timeCreated": "2015-07-08T08:00:00Z"},
2 - {"timeCreated": "2015-05-08T10:00:00Z", "timeUpdated": "2015-07-09T08:00:00"}
3 - {"timeCreated": "2015-07-10T08:00:00Z"}

The order it should display in must be 3, 2, 1. What I'm getting is 2, 3, 1.

Answer 1

You need script sorting in this case:

{
  "query": {
    "match_all": {}
  },
  "sort": {
    "_script": {
      "script": "if (doc['timeUpdated'].value != 0) doc['timeUpdated'].value else doc['timeCreated'].value",
      "type": "number",
      "order": "desc"
    }
  }
}