# ElasticSearch: How to make an aggregation pipeline?

**URL:** <https://discuss.elastic.co/t/elasticsearch-how-to-make-an-aggregation-pipeline/227118>\
**Category:** Elasticsearch\
**Created:** [April 8, 2020, 11:52am UTC](https://discuss.elastic.co/t/elasticsearch-how-to-make-an-aggregation-pipeline/227118 "2020-04-08T11:52:51Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![ayodev](https://avatars.discourse-cdn.com/v4/letter/a/7ab992/32.png) [@ayodev](https://discuss.elastic.co/u/ayodev)\
**Post date:** [April 8, 2020, 11:52am UTC](https://discuss.elastic.co/t/elasticsearch-how-to-make-an-aggregation-pipeline/227118/1 "2020-04-08T11:52:51Z")

</div>

Imagine the following use case:

We work at Stark Airlines and our marketing team wants to segment our passengers in order to give them discounts or gift cards. They decide that they want two sets of passengers:

1. Passengers that fly at least 3 times per week
2. Passenger who have flown at least once but who have not flown for two weeks

With this they can make different marketing campaigns for our passengers!

So, in elastic search we have a `trip` index that represents a ticket bought by a passenger:

```auto
{
   "_index" : "trip",
   "_type" : "_doc",
   "_id" : "1",
   "_score" : 1.0,
   "_source" : {
       "total_amount" : 300,
       "trip_date" : "2020/03/24 13:30:00",
       "status" : "completed",
       "passenger" : {
          "id" : 11,
          "name" : "Thiago nunes"
       }
     }
}

```

The `trip` index contains a `status` field that may have other values like: `pending` or `open` or `canceled`

This means that we can only take into account trips that has the `completed` status (Meaning the passenger did travel).

So, with all this in mind...How would I get those two sets of passengers with elastic search?

I have been trying for a while but with no success.

**What I have done until now:**

1. I have built a query that gets all valid trip (trips with status `completed`)

```auto
GET /trip/_search
{
  "query": {
    "bool": {
      "must": [
        {
          "term": {
            "status": {
              "value": "completed"
            }
          }
        }
      ]
    }
  },
  "aggs": {
    "status_viagem": {
      "terms": {
        "field": "status.keyword"
      }
    }
  }
}

```

1. This query returns the following:

```auto
{
  "took" : 3,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 200,
      "relation" : "eq"
    },
    "max_score" : 0.18232156,
    "hits" : [...]
  },
  "aggregations" : {
    "status_viagem" : {
      "doc_count_error_upper_bound" : 0,
      "sum_other_doc_count" : 0,
      "buckets" : [
        {
          "key" : "completed",
          "doc_count" : 200
        }
      ]
    }
  }
}

```

But I am stuck and can't figure out the next step. I know that the next thing to do should create buckets with passengers and then filter them in two buckets representing our desired data sets. But I don't know how.

Can someone help?

PS.:

1. I don't exactly need this to be one single query, just a hint about how to build a query like this would be very helpful

2. **THE OUPUT SHOULD BE AN ARRAY** of passenger id's

3. Note: I have shortened the `trip` index for the sake of simplicity

---

<div class="post-metadata">

**Author:** ![Mark\_Harwood](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mark_harwood/32/10538_2.png) [@Mark\_Harwood](https://discuss.elastic.co/u/Mark_Harwood)\
**Post date:** [April 8, 2020, 11:59am UTC](https://discuss.elastic.co/t/elasticsearch-how-to-make-an-aggregation-pipeline/227118/2 "2020-04-08T11:59:24Z")

</div>

Sounds like a classic case for [transforms](https://www.elastic.co/guide/en/elasticsearch/reference/current/transforms.html) to create an entity-centric index

---

<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:** [May 6, 2020, 11:59am UTC](https://discuss.elastic.co/t/elasticsearch-how-to-make-an-aggregation-pipeline/227118/3 "2020-05-06T11:59:36Z")

</div>

Closed on May 6, 2020, 11:59am UTC

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