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.

Norven

What you posted should be exactly what you need. It doesn't work? – Balinese

@AndreiStefan as far as I understand it, with this approach, the "null" valued docs will be right at the "bottom" of the list, and then that "bottom" section of the "sorting list" will be sorted according to timeCreated. If my understanding around this is wrong, I'm clearly missing something. – Norven

Ooh, I see now. You need a script sorting then. – Balinese

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"
    }
  }
}

Balinese

That did the trick, thanks! For anyone else who gets errors about inline scripts not being allowed, elastic.co/guide/en/elasticsearch/reference/1.6/… helped me out – Norven

Update: In elasticsearch 7.3.12. using doc['timeUpdated'].size() != 0 instead. – Bigod