How to write Elastic Search query

Question

I’m currently facing an issue with constructing a proper Elasticsearch query. The goal is to implement a filtering mechanism with the following requirements

1. My own posts (userId matches the current user’s userId) should always be displayed, regardless of the hiddenUntil condition.
2. Other users’ posts (userId does not match the current user’s userId) should only be displayed if the hiddenUntil field is less than or equal to the current time.

In SQL, this would be straightforward using an OR condition, like this:

SELECT *
FROM posts
WHERE userId = 'my_user_id'
   OR (userId != 'my_user_id' AND hiddenUntil <= CURRENT_TIMESTAMP);

How can I wrote elastic search query that run same with the sql?

{
  "query": {
    "bool": {
      "filter": [
        {
          "term": { "userId": "my_user_id" } 
        },
        {
          "bool": {
            "must": [
              { "range": { "hiddenUntil": { "lte": 1728291700750 } } } 
            ],
            "must_not": [
              { "term": { "userId": "my_user_id" } }
            ]
          }
        }
      ]
    }
  }
}

Answer 1

You can use should query with must/must_not inside it and specify minimum_should_match to satisfy your criteria.

{
"query": {
    "bool": {
        "must": [],
        "filter": [],
        "should": [
            {
                "bool": {
                    "must": [
                        {
                            "term": {
                                "userId": "my_user_id"
                            }
                        }
                    ]
                }
            },
            {
                "bool": {
                    "must_not": [
                        {
                            "term": {
                                "userId": "my_user_id"
                            }
                        }
                    ],
                    "must": [
                        {
                            "range": {
                                "hiddenUntil": {
                                    "lte": 1728291700750
                                }
                            }
                        }
                    ]
                }
            }
        ],
        "minimum_should_match": 1,
        "must_not": []
    }
}

}