Elastic search rows to column

Question

I have documents in ElasticSearch like this.I want make search and get result like sql server pivot.But i dont know how can i do this.

Name | Year | Gear
C30    2012    A
C30    2011    M
C30    2014    M
C30    2015    A
C30    2013    A
V40    2012    A
V40    2013    M
V40    2015    A
S60    2012    M
S60    2011    A

When i search 'C30 A' i want show data like this.

Name | Years
C30    2012,2015,2013

How can i do this ? is it possible in ES?

Could you please clarify if your data are already indexed int the format that you show above? That means that each document has a Name,Year and Gear field? — Manolis, Feb 10 '15 at 09:03
You should be able to use aggregations for this. In this case a filter aggregation with a filter on the name and gear and a nested term aggregation on the year should do the trick. That will give you a list of the most frequent years for a given name and gear. — Jilles van Gurp, Feb 10 '15 at 11:15

score 1 · Answer 1 · answered Feb 10 '15 at 10:59

If you already have your data indexed as shown (that means: each document has a Name, Year and Gear field) then a correct query will return you a list of documents of this type.

An example of such query is the below (more options are available - more info on query dsl check here - http://www.elasticsearch.org/guide/en/elasticsearch/reference/current/query-dsl.html).

"query": {
    "bool": {
        "must": [
           {"match": { "Name": "C30" }},
           {"match": { "Gear": "A" }}               
        ]
    }
}

If you want a result as the one you describe you should apply some post processing on your data after they are received from the search result.

However, if your data are not already indexed, or you don't care changing your policy then I would propose to denormalize your data and index them in a form that best suits you. I propose to index a document like the one below:

public class ObjectToIndex{
    public string Name;
    public string Gear;
    public List<string> Years;
}

Your data will now look like

Name | Gear | Years
C30     A   2012,2015,2013
C30     M   2011,2014
V40     A   2012,2015
V40     M   2013
S60     A   2011
S60     M   2012

In this case,a query like the above would return you a document like:

C30, A,  2012,2015,2013

Elastic search rows to column

1 Answers1