SQL like Group by in Elasticsearch

[Islam_Elshobokshy]

I want to, when filtering my results, at the end, group everything by name for example. If multiple results contain the same name, they are grouped, as well as all of their other fields being summed together if they are numbers.

So I don't just want it to group them by name, I want to get all the results of all the other fields grouped as well depending on that name criteria.

Another example is if I want them grouped by name and country. If they have the same name AND are in the same country, they are grouped.

So how to group them and show all results, and how to group them using more than 1 criteria?

[dadoonet (David Pilato)]

Why not a first terms agg on name then a terms agg on country?

May be that would work. If not, please share an example of what you have and what ou want to get back.

[Islam_Elshobokshy]

I am using Elastica. To make it simple let's try with only 1 agg...

I have a lot of filters like this, to filter the search of all my documents :

$bool_sub = new \Elastica\Query\Bool();
$bool->addMust($bool_sub);
$query->setFilter($bool); 

I am adding an aggregation :

$manufacturers = new Elastica\Aggregation\Terms('name'); 
$manufacturers->setField('name_id');
$query->addAggregation($manufacturers);

And then looping over the bucket :

$bucket = $index->search($query)->getAggregation('name');

The bucket only gives me a list of all ids with the doc count of each id but I want all the results in the bucket that matches my filters, grouped by the agg I specified. So I want to show all the results for each grouped item by name, and not only the doc count that shows me how many were grouped... Thus my question.

[dadoonet (David Pilato)]

Can't you add a sub agg on $manufacturers?

[Islam_Elshobokshy]

A sub agg won't give me all of the results, just the result of the agg and the sub aggs. Also it's going to order everything by the aggs and sub aggs which is not what I want. I want to show all of the results of the agg.

[dadoonet (David Pilato)]

May be I don't understand so an example would help.
Could you provide a full recreation script as described in About the Elasticsearch category. It will help to better understand what you are doing. Please, try to keep the example as simple as possible.

But may be others have an idea.