How to use another field for query if the first does not exist value

[adrianog3 (Adriano Souza Arruda)]

I would like to do a date filter on a field called "last_update_date", however, if the value is "null", I should use another field called "creation_date". Is there any way to do this?

The filter for the "last_update_date" field is this:

"query": {
"range": {
"last_update_date": {
"gte": "2020-02-01T23: 20: 16.537Z"
}
}
}

[spinscale (Alexander Reelsen)]

hey,

how about this structure. You have a query that checks for last_update_date OR checks for last_update_date being null/not exists AND creation_date

bool:
  filter: [
    bool: {
      "should" : [
        { "range" : { "last_update_date"} },
        { "bool": { "must_not" : { "exists" : "last_update_date" }, "filter" : { "range" : { "creation_date"} } } }
      ]
    }
  ]

Note, this is on top of my head and untested, but might be worth a try. A bool query, with only should clauses, treats those as an or, so I think this should work, if not, we can reiterate.

[adrianog3 (Adriano Souza Arruda)]

Many thanks for the reply.

I found a solution by making a query similar to the one you made. Perhaps more complex than it should. But it was like this:

{
"query": {
"bool": {
"should": [
{
"bool": {
"must": [
{
"exists": {
"field": "last_update_date"
}
},
{
"range": {
"last_update_date": {
"lte": "2020-01-18T01:58:03.478Z"
}
}
}
]
}
},
{
"bool": {
"must": [
{
"bool": {
"must_not": [
{
"exists": {
"field": "last_update_date"
}
}
]
}
},
{
"range": {
"creation_date": {
"lte": "2020-01-18T01:58:03.478Z"
}
}
}
]
}
}
]
}
}
}