Create a query with OR & And condition in elasticsearch


I am trying to convert this sql query to the DSL query to query in Python

select count(*) from dean_acc_p_alm_asset
where (substring(cast(sys_updated_on as varchar),1,10) >= '2021-11-01' and substring(cast(sys_updated_on as varchar),1,10) < '2021-11-02')
or (substring(cast(u_state_date as varchar),1,10) >= '2021-11-01' and substring(cast(u_state_date as varchar),1,10) < '2021-11-02')

I tried multiple ways to do that but could not get success. One of the example I tried is

alm_asset_result =,size = 10000,body={"query": {"bool": {"should": [{"range" : {"sys_updated_on" : {"gte" : tempStart, "lte": tempEnd}}},{"range" : {"u_state_date" : {"gte" : tempStart, "lte": tempEnd}}}]}}})

Can you please correct where I am doing mistakes? Thank you

What was the problem of the result?

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