# Queries with nested fields and "having" clauses

**URL:** <https://discuss.elastic.co/t/queries-with-nested-fields-and-having-clauses/366643>\
**Category:** Elasticsearch\
**Created:** [September 16, 2024, 5:21pm UTC](https://discuss.elastic.co/t/queries-with-nested-fields-and-having-clauses/366643 "2024-09-16T17:21:32Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![Ludmilla1963](https://avatars.discourse-cdn.com/v4/letter/l/a3d4f5/32.png) [@Ludmilla1963](https://discuss.elastic.co/u/Ludmilla1963)\
**Post date:** [September 16, 2024, 5:21pm UTC](https://discuss.elastic.co/t/queries-with-nested-fields-and-having-clauses/366643/1 "2024-09-16T17:21:32Z")

</div>

I have this index with a nested field:

```auto
{
  "test-nested": {
    "mappings": {
      "properties": {
        "indicator": {
          "type": "nested",
          "properties": {
            "id": {
              "type": "keyword"
            },
            "value": {
              "type": "keyword"
            }
          }
        }
      }
    }
  }
}

```

where I inserted some docs like this:

```auto
POST test-nested/_doc
{
  "indicator": [
    { "id": "topic", "value": "plants" },
    { "id": "topic", "value": "animals" },
    { "id": "topic", "value": "rivers" },
    { "id": "country", "value": "FR" }
  ]
}

```

Now, I want to get all the docs having at least two indicators whose "id" is "topic".  
Is it possible?

---

<div class="post-metadata">

**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\
**Post date:** [September 16, 2024, 9:42pm UTC](https://discuss.elastic.co/t/queries-with-nested-fields-and-having-clauses/366643/2 "2024-09-16T21:42:45Z")

</div>

Welcome!

This might be doable but I think that the best/fastest approach is to compute that at index time, i.e, indexing something like:

```auto
{
  "indicator": {
    "topic": {
      "values: ["plants", "animals" , "rivers"],
      "size": 3
    },
    "country": {
      "values: ["FR"],
      "size": 1
    }
  }
}

```

Then finding a topic with more than `2` in `indicator.topic.size` is easier.

---

<div class="post-metadata">

**Author:** ![Ludmilla1963](https://avatars.discourse-cdn.com/v4/letter/l/a3d4f5/32.png) [@Ludmilla1963](https://discuss.elastic.co/u/Ludmilla1963)\
**Post date:** [September 19, 2024, 9:16am UTC](https://discuss.elastic.co/t/queries-with-nested-fields-and-having-clauses/366643/3 "2024-09-19T09:16:27Z")

</div>

Thank you for your answer.  
Unfortunately this wouldn't work in my case, as I have no control on the indexing. I'm dealing with already existing indexes controlled by third parties.  
You said that my approach "is doable". Do you have any idea how this can be done?  
Thank you.

---

<div class="post-metadata">

**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\
**Post date:** [September 19, 2024, 10:09am UTC](https://discuss.elastic.co/t/queries-with-nested-fields-and-having-clauses/366643/4 "2024-09-19T10:09:22Z")

</div>

Here is a trick:

```auto
DELETE nested
PUT nested
{
  "mappings": {
    "properties": {
      "indicator": {
        "type": "nested",
        "properties": {
          "id": {
            "type": "keyword"
          },
          "value": {
            "type": "keyword"
          }
        }
      }
    }
  }
}
POST nested/_doc
{
  "indicator": [
    { "id": "topic", "value": "plants" },
    { "id": "topic", "value": "animals" },
    { "id": "topic", "value": "rivers" },
    { "id": "country", "value": "FR" }
  ]
}
POST nested/_doc
{
  "indicator": [
    { "id": "topic", "value": "plants" },
    { "id": "topic", "value": "rivers" },
    { "id": "country", "value": "FR" }
  ]
}
POST nested/_doc
{
  "indicator": [
    { "id": "topic", "value": "rivers" },
    { "id": "country", "value": "FR" }
  ]
}
GET nested/_search
{
  "query": {
    "nested": {
      "path": "indicator",
      "score_mode": "sum",
      "query": {
        "constant_score": {
          "filter": {
            "term": {
              "indicator.id": {
                "value": "topic"
              }
            }
          },
          "boost": "1"
        }
      }
    }
  },
  "min_score": 2
}

```

Basically, set the `_score` to `1` for each matching nested doc, use `sum` to combine the `_score` of the nested docs and ask Elasticsearch to retrieve only documents where `_score` is \>= to `2`.

---

<div class="post-metadata">

**Author:** ![Ludmilla1963](https://avatars.discourse-cdn.com/v4/letter/l/a3d4f5/32.png) [@Ludmilla1963](https://discuss.elastic.co/u/Ludmilla1963)\
**Post date:** [September 19, 2024, 12:14pm UTC](https://discuss.elastic.co/t/queries-with-nested-fields-and-having-clauses/366643/5 "2024-09-19T12:14:51Z")

</div>

Great!  
It works like a charm.  
Thank you.