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.

What you posted should be exactly what you need. It doesn't work? — Andrei Stefan, Jul 10 '15 at 11:57
@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. — iLikeBreakfast, Jul 10 '15 at 12:01

score 7 · Accepted Answer · answered Jul 10 '15 at 12:16

7

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

answered Jul 10 '15 at 12:16

Andrei Stefan

51,654
6
98
89

That did the trick, thanks! For anyone else who gets errors about inline scripts not being allowed, https://www.elastic.co/guide/en/elasticsearch/reference/1.6/modules-scripting.html helped me out – iLikeBreakfast Jul 10 '15 at 13:15
Update: In `elasticsearch` 7.3.12. using doc['timeUpdated'].size() != 0 instead. – logbasex Mar 15 '23 at 16:25

Order by timeUpdated, if exists, otherwise timeCreated

1 Answers1