# Elastic Query Bool Logic/Subquery Structure Help

**URL:** <https://discuss.elastic.co/t/elastic-query-bool-logic-subquery-structure-help/299491>\
**Category:** Elasticsearch\
**Tags:** vega\
**Created:** [March 11, 2022, 7:20pm UTC](https://discuss.elastic.co/t/elastic-query-bool-logic-subquery-structure-help/299491 "2022-03-11T19:20:49Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![bsmith](https://avatars.discourse-cdn.com/v4/letter/b/7feea3/32.png) [@bsmith](https://discuss.elastic.co/u/bsmith)\
**Post date:** [March 11, 2022, 7:20pm UTC](https://discuss.elastic.co/t/elastic-query-bool-logic-subquery-structure-help/299491/1 "2022-03-11T19:20:50Z")

</div>

Need help converting some SQL into elastic and stuck on some of the bool logic and probably isn't structured properly. Goal is to get a count of users that are active at a specified start date and are still active at the specified end date.

Sample data. Result should return a count of 2 as only A and B meet the criteria

```auto
DATE FIELD USERNAME
2020-10-01 START A
2020-10-01 START B
2020-10-01 START C
2021-09-31 END A
2021-09-31 END B
2021-12-31 END C

```

```auto
select count(*) from TABLE
where FIELD = 'START' and DATE = '2020-10-01'
and USERNAME IN (select USERNAME from TABLE where FIELD = 'END' and DATE = '2021-09-31'

```

The following is what I have so far and it's definitely wrong. I initially tried to mimic the subquery by nesting the second bool inside the first, but that had way too many rows. This is getting closer, but I'm still missing something.  
Created with the intent of using Vega with Context on, so no query block.

```auto
{
  "size": 0,
  "aggs": {
    "countingusers": {
      "filter": {
        "bool": {
          "should": [
            {
              "bool": {
                "must": [
                  {
                    "range": {
                      "DATE": {
                        "gte": "2020-10-01",
                        "lte": "2020-10-31"
                      }
                    }
                  },
                  {
                    "match": {
                      "FIELD": "START"
                    }
                  }
                ]
              }
            },
            {
              "bool": {
                "must": [
                  {
                    "range": {
                      "DATE": {
                        "gte": "2021-09-01",
                        "lte": "2021-09-31"
                      }
                    }
                  },
                  {
                    "match": {
                      "FIELD": "END"
                    }
                  }
                ]
              }
            }
          ]
        }
      },
      "aggs": {
        "countusers": {
          "value_count": {
            "field": "USERNAME"
          }
        }
      }
    }
  }
}

```

---

<div class="post-metadata">

**Author:** ![ylasri](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ylasri/32/86120_2.png) [@ylasri](https://discuss.elastic.co/u/ylasri)\
**Post date:** [March 12, 2022, 5:09pm UTC](https://discuss.elastic.co/t/elastic-query-bool-logic-subquery-structure-help/299491/2 "2022-03-12T17:09:51Z")

</div>

I suggest you explore transforms in Elasticsearch that will help to compute first start date and last end date per user(your entity) and store summary in a new index that you can query

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [April 9, 2022, 5:10pm UTC](https://discuss.elastic.co/t/elastic-query-bool-logic-subquery-structure-help/299491/3 "2022-04-09T17:10:24Z")

</div>

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