0

i'm trying to solve a problem here, I'm not even sure it can be done how I want it to be done. Basically I need to query my index on the type Company. This type does have a field named Company_Name. Now I have another type which is Employee that does have a field named Company_Name as well, which basically "links him" to the Company.

What I need to do is to make an aggregation in my query of companies, that gets me the number of employees that are within the company (for each result).

Any way to do this ?

EDIT :

The mapping :

"company" : {
  "properties" : {
    "company_name" : {
      "type" : "text"
     }
  }
},
"employee" : {
  "properties" : {
    "employee_name" : {
      "type" : "text"
    },
    "company_name" : {
      "type" : "text"
    }
  }
}
Christophe Schutz
  • 605
  • 4
  • 19

1 Answers1

0

Not sure if I fully understood what you want but you mentioned:

Now I have another type which is Employee that does have a field named Company_Name

And then you said you want, number of employees that are withing the company.

So for me it looks like all you need is an aggregation on Employees doc type by company.

If you really need to query both indexes then you can apply a multi-index search and the aggregation to the result of the multi index search.

This or something close to this should work: 

http://localhost:9200/$index/employee/_search?search_type=count

{
  "aggs" : {
   "company" : {
    "terms" : {
      "field" : "company_name"
      }
    }
  }
}

One think you should notice is that if your field company_name is analyzed it will be tokenized and you can't aggregate on that. You should then add a raw field with company name to use for your aggregation.

Some useful references:

https://www.elastic.co/guide/en/elasticsearch/guide/current/relations.html

https://www.elastic.co/guide/en/elasticsearch/guide/current/_closing_thoughts.html and

groo
  • 4,213
  • 6
  • 45
  • 69
  • Thx for your quick answer. Just to clarify, both types are in the same index. The way I understand it though is that aggregations are only "subbuckets" of the data retrieved by the top level query. Problem being, in my top level query I only query the companies, not the employees; which is why I have no clue how to make an aggregation over the employees. – Christophe Schutz Feb 21 '17 at 14:01
  • Hi, please post your mapping it will be easier to give a proper answer. – groo Feb 21 '17 at 14:23
  • Hi Marcos, I updated the original post with the mapping. It's a pretty basic relation really, the only problem I have is because of the fact that I filter my initial query on companies only. – Christophe Schutz Feb 21 '17 at 14:29