# Composite query and filter agregation on a .ml-anomalies index

**URL:** <https://discuss.elastic.co/t/composite-query-and-filter-agregation-on-a-ml-anomalies-index/248201>\
**Category:** Elasticsearch\
**Created:** [September 10, 2020, 4:14pm UTC](https://discuss.elastic.co/t/composite-query-and-filter-agregation-on-a-ml-anomalies-index/248201 "2020-09-10T16:14:00Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![Incauto](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/incauto/32/60149_2.png) [@Incauto](https://discuss.elastic.co/u/Incauto)\
**Post date:** [September 10, 2020, 4:14pm UTC](https://discuss.elastic.co/t/composite-query-and-filter-agregation-on-a-ml-anomalies-index/248201/1 "2020-09-10T16:14:00Z")

</div>

HI, Im trying to query the ML anomalies index with a composite query and a filter aggregation, but the filter aggs is not working, I want to get a specific job but it gets me others jobs in the index.

Any ideas what is wrong with my query?

```auto
GET .ml-anomalies-custom-myindex*/_search
{
  "size": 0,
  "aggs": {
    "table": {
      "composite": {
        "size": 10000,
        "sources": [
          {
            "job": {
              "terms": {
                "field": "job_id"
              }
            }
          },
          {
            "score": {
              "terms": {
                "field": "record_score"
              }
            }
          },
          {
            "date": {
              "date_histogram": {
                "field": "timestamp",
                "fixed_interval": "1d"
              }
            }
          }
        ]
      },
      "aggs": {
        "filtro": {
          "filter": {
            "term": {
              "job_id": "cpu-utilization"
            }
          }
        }
      }
    }
  }
}

```

---

<div class="post-metadata">

**Author:** ![richcollier](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/richcollier/32/115035_2.png) [@richcollier](https://discuss.elastic.co/u/richcollier)\
**Post date:** [September 10, 2020, 7:28pm UTC](https://discuss.elastic.co/t/composite-query-and-filter-agregation-on-a-ml-anomalies-index/248201/2 "2020-09-10T19:28:37Z")

</div>

Can you describe what it is that you want as the end result?

---

<div class="post-metadata">

**Author:** ![Incauto](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/incauto/32/60149_2.png) [@Incauto](https://discuss.elastic.co/u/Incauto)\
**Post date:** [September 10, 2020, 7:33pm UTC](https://discuss.elastic.co/t/composite-query-and-filter-agregation-on-a-ml-anomalies-index/248201/3 "2020-09-10T19:33:14Z")

</div>

Hi, that all the fields in the composite query: "job","score","date". belong to the job "cpu-utilization"

---

<div class="post-metadata">

**Author:** ![richcollier](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/richcollier/32/115035_2.png) [@richcollier](https://discuss.elastic.co/u/richcollier)\
**Post date:** [September 10, 2020, 7:57pm UTC](https://discuss.elastic.co/t/composite-query-and-filter-agregation-on-a-ml-anomalies-index/248201/4 "2020-09-10T19:57:16Z")

</div>

Are you just trying to get a summary of anomaly scores per day per job?

I personally would do this with Transforms:

i.e.

```auto
POST _transform/_preview
{
  "source": {
    "index": [
      ".ml-anomalies-*"
    ],
    "query": {
            "bool": {
              "filter": [
                  { "term" : { "job_id": "farequote_resp_by_airline" } }
              ]
            }
    }
  },
  "pivot": {
    "group_by": {
      "job_id": {
        "terms": {
          "field": "job_id"
        }
      },
      "timestamp": {
        "date_histogram": {
          "field": "timestamp",
          "calendar_interval": "1d"
        }
      }
    },
    "aggregations": {
      "record_score_max": {
        "max": {
          "field": "record_score"
        }
      }
    }
  }
}

```

which would yield:

```auto
{
  "preview" : [
    {
      "job_id" : "farequote_resp_by_airline",
      "record_score_max" : 1.891242,
      "timestamp" : 1486425600000
    },
    {
      "job_id" : "farequote_resp_by_airline",
      "record_score_max" : 6.176466,
      "timestamp" : 1486512000000
    },
    {
      "job_id" : "farequote_resp_by_airline",
      "record_score_max" : 98.5606570845504,
      "timestamp" : 1486598400000
    },
    {
      "job_id" : "farequote_resp_by_airline",
      "record_score_max" : 3.085973176835846,
      "timestamp" : 1486684800000
    },
    {
      "job_id" : "farequote_resp_by_airline",
      "record_score_max" : 10.01659340509018,
      "timestamp" : 1486771200000
    },
    ...

```

and you could remove the filter to see all jobs, if you wanted.

---

<div class="post-metadata">

**Author:** ![Incauto](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/incauto/32/60149_2.png) [@Incauto](https://discuss.elastic.co/u/Incauto)\
**Post date:** [September 11, 2020, 12:47am UTC](https://discuss.elastic.co/t/composite-query-and-filter-agregation-on-a-ml-anomalies-index/248201/5 "2020-09-11T00:47:04Z")

</div>

Thanks Rich, I didn't know about transforms, but sadly I need the query to be used in Vega Visualizations, I could use the filter on the gui, but the programmer, who gets the visualizations via kibana API, needs that filter on the query.

---

<div class="post-metadata">

**Author:** ![richcollier](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/richcollier/32/115035_2.png) [@richcollier](https://discuss.elastic.co/u/richcollier)\
**Post date:** [September 11, 2020, 1:27pm UTC](https://discuss.elastic.co/t/composite-query-and-filter-agregation-on-a-ml-anomalies-index/248201/6 "2020-09-11T13:27:58Z")

</div>

Well, just so you know - Transforms allows you to create a new index. Then you can have your Vega visualization point to this new, summary index that the transform creates.

---

<div class="post-metadata">

**Author:** ![Incauto](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/incauto/32/60149_2.png) [@Incauto](https://discuss.elastic.co/u/Incauto)\
**Post date:** [September 11, 2020, 1:56pm UTC](https://discuss.elastic.co/t/composite-query-and-filter-agregation-on-a-ml-anomalies-index/248201/7 "2020-09-11T13:56:17Z")

</div>

😲

---

<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:** [October 9, 2020, 1:56pm UTC](https://discuss.elastic.co/t/composite-query-and-filter-agregation-on-a-ml-anomalies-index/248201/8 "2020-10-09T13:56:18Z")

</div>

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