Boolean query with two conditions on a field

Titan · March 19, 2020, 9:01pm

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

Opster_Community1 · March 20, 2020, 3:25am

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

Titan · March 20, 2020, 1:45pm

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?

itizir · March 20, 2020, 1:58pm

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...

Opster_Community1 · March 21, 2020, 5:15am

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.

Titan · March 21, 2020, 5:26am

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!

Titan · March 21, 2020, 5:27am

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

Opster_Community1 · March 21, 2020, 5:35am

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

Titan · March 22, 2020, 1:14am

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!

Thanks

itizir · March 23, 2020, 9:31am

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.

Titan · March 23, 2020, 3:46pm

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

itizir · March 23, 2020, 4:04pm

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

Titan · March 24, 2020, 4:32am

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!

itizir · March 24, 2020, 10:15am

You're welcome.

system · April 21, 2020, 10:15am

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.

Topic		Replies	Views
Elasticsearch - query with 2 conditions Elasticsearch	4	1215	July 24, 2020
[bool] query does not support [range] Elasticsearch	2	4677	March 22, 2019
Not able to Combine Interval Queries with Other Bool Filter Elasticsearch	3	600	October 24, 2019
Bool filter not matching on range query Elasticsearch	3	300	July 6, 2017
Replacing filtered and or filter query with bool (Elasticsearch 5.2) Elasticsearch	4	3603	April 12, 2017

Boolean query with two conditions on a field

Related topics