es

es.md

Aggregates

Let's say I have a funds index with 2 documents like this

{
  "fundId": 123,
  "fundManager": {
    "id": 123434,
    "name": "Chris Inc"
  },
  "sectors": ["a", "b"]
}

{
  "fundId": 456,
  "fundManager": {
    "id": 123434,
    "name": "Chris Inc"
  },
  "sectors": ["c"]
}

Notice they are two different "funds" with the same fund manager.

What we want to do is retrieve all fund managers with the sectors they manage (e.g bring all the sectors together)

So in this case I'd want for the lack of a better word a "GROUP BY" and get something returned like

{
  "fundManager": {
    "id": 123434,
    "name": "Chris Inc"
  },
  "sectors": ["a", "b", "c"]
}

Is this possible? How?

Let's put these documents into Elasticsearch and allowing ES to infer the types i.e. not setting up our own mapping.

PUT deals/doc/1
{
  "fundId": 123,
  "fundManager": {
    "id": 123434,
    "name": "Chris Inc"
  },
  "sectors": ["a", "b"]
}

PUT deals/doc/1
{
  "fundId": 123,
  "fundManager": {
    "id": 123434,
    "name": "Chris Inc"
  },
  "sectors": ["a", "b"]
}

There are 2 ways you can get all the sectors for a give fund manger:

1. The following will return all the documents (limit the fields to "sectors") and in your code you can marshal the data to your requirements.

GET deals/doc/_search
{
  "_source": ["sectors"], 
  "query": {
    "terms": {
      "fundManager.id": [123434 ]
    }
  }
}

2. The following query will use aggregation to give you the unique sectors and count of documents for each sector, I personally prefer this method.

GET deals/doc/_search
{
  "size": 0, 
  "query": {
    "terms": {
      "fundManager.id": [123434 ]
    }
  },
  "aggs": {
    "sectors": {
      "terms": {
        "field": "sectors.keyword", //We can only aggregate on string keyword type, here default mapping creates a field called keyword automaticatlly
        "size": 1000
      }
    }
  }
}

Here is the query to get all the fund managers and their related sectors:

GET deals/doc/_search
{
  "size": 0,
  "aggs": {
    "fund_managers": {
      "terms": {
        "field": "fundManager.name.keyword",
        "size": 10,
        "order": {
          "_key": "asc"
        }
      },
      "aggs": {
        "sectors": {
          "terms": {
            "field": "sectors.keyword",
            "size": 1000,
            "order": {
              "_count": "desc"
            }
          }
        }
      }
    }
  }
}

Aggregation and returning some fieleds

GET deals/doc/_search
{
  "size": 0,
  "aggs": {
    "fund_managers": {
      "terms": {
        "field": "fundManager.name.keyword",
        "size": 100
      },
      "aggs": {
        "top_sectors": {
          "top_hits": {
            "_source": {
              "includes": [
                "fundId",
                "fundManager.id",
                "fundManager.name",
                "sectors"
              ]
            },
            "size": 10
          }
        }
      }
    }
  }
}