Boolean query with two conditions on a field

Applying range filter on a boolean field

In a search query, I am trying to get product records that are either not suppressed or if suppressed then the suppression range is either before current time or after current time. Here is the query that I came up with, which certainly have some issue. I could not find any relevant article on this/similar issue thus posting it here to get community/Elastic team's help:

   {
      "_source": {
        "includes": [
          "ProductId",
          "Active",
          "ActiveFrom",
          "ActiveTo",
          "Suppressed",
          "SuppressedFrom",
          "SuppressedTo"
        ]
      },
      "size": 1000,
      "query": {
        "constant_score": {
          "filter": {
            "bool": {
              "must": [
                {
                  "match": {
                    "Active": true
                  }
                },
                {
                  "range": {
                    "ActiveFrom": {
                      "lte": **CurrentTime**
                    }
                  }
                },
                {
                  "range": {
                    "ActiveTo": {
                      "gte": **CurrentTime**
                    }
                  }
                },
                {
                  "match": {
                    "Suppressed": false
                  }
                },
                {
                  "match": {
                    "Suppressed": true
                  },
                  "range": {
                    "SuppressedFrom": {
                      "gte": **CurrentTime**
                    }
                  }
                },
                {
                  "match": {
                    "Suppressed": true
                  },
                  "range": {
                    "SuppressedTo": {
                      "lte": **CurrentTime**
                    }
                  }
                }
              ]
            }
          }
        }
      }
    }

I am getting parsing exception [match] malformed query, expected [END_OBJECT] but found [FIELD_NAME] for the use of range clause after match clause where Suppressed is true.

Thanks,
T

You query seem to be quite different from the condition you explained. Can you explain with sample documents.

Thanks Nishant for the reply. Perhaps, breaking it down will help a bit and you may update it with the clauses causing the error. Here is the query that worked fine without the Suppressed True and related range conditions:

{
  "_source": {
    "includes": [
      "ProductId",
      "Active",
      "ActiveFrom",
      "ActiveTo",
      "Suppressed",
      "SuppressedFrom",
      "SuppressedTo"
    ]
  },
  "size": 1000,
  "query": {
    "constant_score": {
      "filter": {
        "bool": {
          "must": [
            {
              "match": {
                "Active": true
              }
            },
            {
              "range": {
                "ActiveFrom": {
                  "lte": 1584646746467
                }
              }
            },
            {
              "range": {
                "ActiveTo": {
                  "gte": 1584646746467
                }
              }
            },
            {
              "match": {
                "Suppressed": false
              }
            }
          ]
        }
      }
    }
  }
}

The response of this query is:

{
    "took": 2,
    "timed_out": false,
    "_shards": {
        "total": 5,
        "successful": 5,
        "skipped": 0,
        "failed": 0
    },
    "hits": {
        "total": 0,
        "max_score": null,
        "hits": []
    }
}

Can you extend this working query to accommodate the true Suppressed and SuppressedFrom and SuppressedTo ranges?

Clauses in a bool must/filter are ANDed together, for OR you need to use should. Have a look here.

And you can nest bool queries...

What you need is two bool queries withing the must clause either of which is true.
So lets understand in pseudo code way. Lets assume you existing query as below:

  • Active : true => A
  • ActiveFrom <= 1584646746467 => B
  • ActiveTo >= 1584646746467 => C
  • Suppressed: false => D

Current query : A AND B AND C AND D => X

Another condition that you want to add:

  • Suppressed: true => A'
  • SuppressedFrom <= 1584646746467 => B'
  • SuppressedTo >= 1584646746467 => C'

Required second query: A' AND B' AND C' => Y

Final required query: X OR Y

Query dsl:

{
  "query": {
    "bool": {
      "filter": [
        {
          "bool": {
            "should": [
              { X },
              { Y }
            ]
          }
        }
      ]
    }
  }
}

Further expanding X, Y

{
  "query": {
    "bool": {
      "filter": [
        {
          "bool": {
            "should": [
              {
                "bool": {
                  "must": [
                    { A },
                    { B },
                    { C },
                    { D }
                  ]
                }
              },
              {
                "bool": {
                  "must": [
                    { A' },
                    { B' },
                    { C' }
                  ]
                }
              }
            ]
          }
        }
      ]
    }
  }
}

I hope this will help you writing the correct query.

Terrific! This was basically what I had in mind but did not know the Elastic way! Got so spoiled by plain and traditional SQL. In fact, in this manner the suppressed True need to separate bool conditions, 1) Supressed True and SuppressedFrom > specified date time, 2) Suppressed True and SuppressedTo < specified date time, means three bool conditions but all based upon your idea!! Will give it a shot. Thanks so much!

Thanks so much for the tip and reference link, somehow I got the results I was looking for, but still not convinced why Elastic treats should as OR!

Here is my updated query:

{
    "_source": {
        "includes": [
            "ProductId",
            "Active",
            "ActiveFrom",
            "ActiveTo",
            "Suppressed",
            "SuppressedFrom",
            "SuppressedTo"
        ]
    },
    "size": 1000,
    "query": {
        "constant_score": {
            "filter": {
                "bool": {
                    "must": [
                        {
                            "match": {
                                "Active": true
                            }
                        },
                        {
                            "range": {
                                "ActiveFrom": {
                                    "lte": 1584646746467
                                }
                            }
                        },
                        {
                            "range": {
                                "ActiveTo": {
                                    "gte": 1584646746467
                                }
                            }
                        },
                        {
                            "match": {
                                "Suppressed": false
                            }
                        }
                    ],
                    "should": [
                        {
                            "match": {
                                "Suppressed": true
                            }
                        },
                        {
                            "range": {
                                "SuppressedFrom": {
                                    "gte": 1584646746467
                                }
                            }
                        },
                        {
                            "range": {
                                "SuppressedTo": {
                                    "lte": 1584646746467
                                }
                            }
                        }
                    ]
                }
            }
        }
    }
}

Actually must, should should not be taken as traditional sql way of AND, OR respectively. Instead they behave more of like intersection and union. Since I felt that you were stuck thinking in traditional way of SQL I tried to explain in that way :wink:

1 Like

Thanks Nishant for bringing further clarity and the concept of intersections and unions which makes sense.

Now comes another challenge, the record count of query 2 and query 3 matches but the record count of query 1 is different, however the returned records in query 1 satisfies the expectations. Can you throw some light how Elastic processes query 1, 2, and 3 probably with a reference to a tradition SQL execution plan?

Query 1:

{
    "_source": {
        "includes": [
            "ProductId",
            "Active",
            "ActiveFrom",
            "ActiveTo",
            "Suppressed",
            "SuppressedFrom",
            "SuppressedTo"
        ]
    },
    "size": 1000,
    "query": {
        "constant_score": {
            "filter": {
                "bool": {
                    "must": [
                        {
                            "match": {
                                "Active": true
                            }
                        },
                        {
                            "range": {
                                "ActiveFrom": {
                                    "lte": 1584646746467
                                }
                            }
                        },
                        {
                            "range": {
                                "ActiveTo": {
                                    "gte": 1584646746467
                                }
                            }
                        },
                        {
                            "match": {
                                "Suppressed": false
                            }
                        }
                    ],
                    "should": [
                        {
                            "match": {
                                "Suppressed": true
                            }
                        },
                        {
                            "range": {
                                "SuppressedFrom": {
                                    "gte": 1584646746467
                                }
                            }
                        },
                        {
                            "range": {
                                "SuppressedTo": {
                                    "lte": 1584646746467
                                }
                            }
                        }
                    ]
                }
            }
        }
    }
}

Query 2:

{
    "_source": {
        "includes": [
            "ProductId",
            "Active",
            "ActiveFrom",
            "ActiveTo",
            "Suppressed",
            "SuppressedFrom",
            "SuppressedTo"
        ]
    },
    "size": 1000,
    "query": {
        "constant_score": {
            "filter": {
                "bool": {
                    "should": [
                        {
                            "bool": {
                                "must": [
                                    {
                                        "match": {
                                            "Active": true
                                        }
                                    },
                                    {
                                        "range": {
                                            "ActiveFrom": {
                                                "lte": 1584646746467
                                            }
                                        }
                                    },
                                    {
                                        "range": {
                                            "ActiveTo": {
                                                "gte": 1584646746467
                                            }
                                        }
                                    },
                                    {
                                        "match": {
                                            "Suppressed": false
                                        }
                                    }
                                ]
                            }
                        },
                        {
                            "bool": {
                                "must": [
                                    {
                                        "match": {
                                            "Active": true
                                        }
                                    },
                                    {
                                        "range": {
                                            "ActiveFrom": {
                                                "lte": 1584646746467
                                            }
                                        }
                                    },
                                    {
                                        "range": {
                                            "ActiveTo": {
                                                "gte": 1584646746467
                                            }
                                        }
                                    },
                                    {
                                        "match": {
                                            "Suppressed": true
                                        }
                                    },
                                    {
                                        "range": {
                                            "SuppressedFrom": {
                                                "gte": 1584646746467
                                            }
                                        }
                                    }
                                ]
                            }
                        },
                        {
                            "bool": {
                                "must": [
                                    {
                                        "match": {
                                            "Active": true
                                        }
                                    },
                                    {
                                        "range": {
                                            "ActiveFrom": {
                                                "lte": 1584646746467
                                            }
                                        }
                                    },
                                    {
                                        "range": {
                                            "ActiveTo": {
                                                "gte": 1584646746467
                                            }
                                        }
                                    },
                                    {
                                        "match": {
                                            "Suppressed": true
                                        }
                                    },
                                    {
                                        "range": {
                                            "SuppressedTo": {
                                                "lte": 1584646746467
                                            }
                                        }
                                    }
                                ]
                            }
                        }
                    ]
                }
            }
        }
    }
}

Query 3:

{
    "_source": {
        "includes": [
            "ProductId",
            "Active",
            "ActiveFrom",
            "ActiveTo",
            "Suppressed",
            "SuppressedFrom",
            "SuppressedTo"
        ]
    },
    "size": 1000,
    "query": {
        "constant_score": {
            "filter": {
                "bool": {
                    "should": [
                        {
                            "bool": {
                                "must": [
                                    {
                                        "match": {
                                            "Active": true
                                        }
                                    },
                                    {
                                        "range": {
                                            "ActiveFrom": {
                                                "lte": 1584646746467
                                            }
                                        }
                                    },
                                    {
                                        "range": {
                                            "ActiveTo": {
                                                "gte": 1584646746467
                                            }
                                        }
                                    },
                                    {
                                        "match": {
                                            "Suppressed": false
                                        }
                                    }
                                ]
                            }
                        },
                        {
                            "bool": {
                                "must": [
                                    {
                                        "match": {
                                            "Active": true
                                        }
                                    },
                                    {
                                        "range": {
                                            "ActiveFrom": {
                                                "lte": 1584646746467
                                            }
                                        }
                                    },
                                    {
                                        "range": {
                                            "ActiveTo": {
                                                "gte": 1584646746467
                                            }
                                        }
                                    },
                                    {
                                        "match": {
                                            "Suppressed": true
                                        }
                                    },
                                    {
                                        "range": {
                                            "SuppressedFrom": {
                                                "gte": 1584646746467
                                            }
                                        }
                                    },
                                    {
                                        "range": {
                                            "SuppressedTo": {
                                                "lte": 1584646746467
                                            }
                                        }
                                    }
                                ]
                            }
                        }
                    ]
                }
            }
        }
    }
}

It may be a bit stretched out discussion now, but will help me knowing how does Elastic works. Its a new world for me! :wink:

Thanks

Looks like your query 2 is the only one doing what you want: if query 3 returns the same, that may mean you don't have documents where only SuppressedFrom or SuppressedTo matches. Query 1 won't be returning documents that are Suppressed...

In query 1, try removing "Suppressed": false from must, replacing "Suppressed": true with "Suppressed": false in the should, and adding "minimum_should_match" : 1 to the bool query.

Hi Diego,

Thanks for the suggestion. After making the changes in query 1 as you suggested:

Revised Query 1:

{
    "_source": {
        "includes": [
            "ProductId",
            "Active",
            "ActiveFrom",
            "ActiveTo",
            "Suppressed",
            "SuppressedFrom",
            "SuppressedTo"
        ]
    },
    "size": 1000,
    "query": {
        "constant_score": {
            "filter": {
                "bool": {
                    "must": [
                        {
                            "match": {
                                "Active": true
                            }
                        },
                        {
                            "range": {
                                "ActiveFrom": {
                                    "lte": 1584646746467
                                }
                            }
                        },
                        {
                            "range": {
                                "ActiveTo": {
                                    "gte": 1584646746467
                                }
                            }
                        }
                    ],
                    "should": [
                        {
                            "match": {
                                "Suppressed": false
                            }
                        },
                        {
                            "range": {
                                "SuppressedFrom": {
                                    "gte": 1584646746467
                                }
                            }
                        },
                        {
                            "range": {
                                "SuppressedTo": {
                                    "lte": 1584646746467
                                }
                            }
                        }
                    ],
                    "minimum_should_match" : 1
                }
            }
        }
    }
}

The record count of revised query 1 and Query 2 matched and meets the criteria, seems like original query 1 did not pick up some records, query 1 retrieved 22878 and revised query retrieved 23206. Query 2 seems to be formed in logical order and makes straight forward sense to me, but revised query 1 is a lot smaller and less complicated, just need to get my head around it. Can you share some details to understand the changes you suggested for query 1?

Thanks

What it does now, essentially, is Active AND ActiveFrom<now AND ActiveTo>now AND (!Suppressed OR SuppressedFrom>now OR SuppressedTo<now).

Note the importance of "minimum_should_match" : 1 as explained in the docs, that makes the should clauses behave like they've been "ORed".

Thanks a lot Diego for the further clarification and help. I have marked Revised Query 1 as the preferred solution and considering Query 2 as another solution with many thanks to Nishant as well for the breakthrough pseudo code and ideas.

Cheers!

You're welcome.