How to query this data with elasticseach

Question

I am trying to find the oldest male person in each family. Each person in the results must be at least 18.

Here is the data: Data as csv

id     FamilyId     LastName         FirstName         Age     Gender
1      1            Smith            John              20      M
2      1            Smith            Joan              20      F
3.     1            Smith            Harry             1       M
4      2            Ross             Pie               33      F
5      2            Ross             Norman            30      M
6      2            Ross             Devan             13      M
7      2            Ross             Debra             9       F
8      2            Ross             Terry             9       F
9      3            Johnson          Mary              25      F
10     4            King             Bob               5       M

Data as json

[
  {
    "id":1,
    "FamilyId":1,
    "LastName":"Smith",
    "FirstName":"John",
    "Age":20,
    "Gender":"M"
  },
  {
    "id":2,
    "FamilyId":1,
    "LastName":"Smith",
    "FirstName":"Joan",
    "Age":20,
    "Gender":"F"
  },
  {
    "id":3,
    "FamilyId":1,
    "LastName":"Smith",
    "FirstName":"Harry",
    "Age":1,
    "Gender":"M"
  },
  {
    "id":4,
    "FamilyId":2,
    "LastName":"Ross",
    "FirstName":"Pie",
    "Age":33,
    "Gender":"F"
  },
  {
    "id":5,
    "FamilyId":2,
    "LastName":"Ross",
    "FirstName":"Norman",
    "Age":30,
    "Gender":"M"
  },
  {
    "id":6,
    "FamilyId":2,
    "LastName":"Ross",
    "FirstName":"Devan",
    "Age":13,
    "Gender":"M"
  },
  {
    "id":7,
    "FamilyId":2,
    "LastName":"Ross",
    "FirstName":"Debra",
    "Age":9,
    "Gender":"F"
  },
  {
    "id":8,
    "FamilyId":2,
    "LastName":"Ross",
    "FirstName":"Terry",
    "Age":9,
    "Gender":"F"
  },
  {
    "id":9,
    "FamilyId":3,
    "LastName":"Johnson",
    "FirstName":"Mary",
    "Age":25,
    "Gender":"F"
  },
  {
    "id":10,
    "FamilyId":4,
    "LastName":"King",
    "FirstName":"Bob",
    "Age":5,
    "Gender":"M"
  }
]

Here is the data I am expecting:

id     FamilyId     LastName         FirstName         Age     Gender
1      1            Smith            John              20      M
5      2            Ross             Norman            30      M

Data as json

[
  {
    "id":1,
    "FamilyId":1,
    "LastName":"Smith",
    "FirstName":"John",
    "Age":20,
    "Gender":"M"
  },
  {
    "id":5,
    "FamilyId":2,
    "LastName":"Ross",
    "FirstName":"Norman",
    "Age":30,
    "Gender":"M"
  }
]

I do not need the id field in the results if its too difficult to get. A query like this is possible with elasticsearch?

Isn't this a typical rdbms-query? Why are you using a freetext search engine for this? — sisve
– sisve, Commented Mar 11, 2015 at 6:33

Sloan Ahrens · Accepted Answer · 2015-03-11 06:10:35Z

This seems to do it; combination of filter agg and top hits agg (gotta love that new branding, huh?):

POST /test_index/_search?search_type=count
{
   "aggs": {
      "males_18_and_over": {
         "filter": {
            "and": [
               { "term": { "Gender": "M" } },
               { "range": { "Age": { "gte": 18 } } } 
            ]
         },
         "aggs": {
            "last_names": {
               "terms": {
                  "field": "LastName"
               },
               "aggs": {
                  "max_age": {
                     "top_hits": {
                        "sort": [
                           {
                              "Age": {
                                 "order": "desc"
                              }
                           }
                        ],
                        "size": 1
                     }
                  }
               }
            }
         }
      }
   }
}

returns:

{
   "took": 4,
   "timed_out": false,
   "_shards": {
      "total": 1,
      "successful": 1,
      "failed": 0
   },
   "hits": {
      "total": 10,
      "max_score": 0,
      "hits": []
   },
   "aggregations": {
      "males_18_and_over": {
         "doc_count": 2,
         "last_names": {
            "buckets": [
               {
                  "key": "Ross",
                  "doc_count": 1,
                  "max_age": {
                     "hits": {
                        "total": 1,
                        "max_score": null,
                        "hits": [
                           {
                              "_index": "test_index",
                              "_type": "doc",
                              "_id": "5",
                              "_score": null,
                              "_source": {
                                 "id": 5,
                                 "FamilyId": 2,
                                 "LastName": "Ross",
                                 "FirstName": "Norman",
                                 "Age": 30,
                                 "Gender": "M"
                              },
                              "sort": [
                                 30
                              ]
                           }
                        ]
                     }
                  }
               },
               {
                  "key": "Smith",
                  "doc_count": 1,
                  "max_age": {
                     "hits": {
                        "total": 1,
                        "max_score": null,
                        "hits": [
                           {
                              "_index": "test_index",
                              "_type": "doc",
                              "_id": "1",
                              "_score": null,
                              "_source": {
                                 "id": 1,
                                 "FamilyId": 1,
                                 "LastName": "Smith",
                                 "FirstName": "John",
                                 "Age": 20,
                                 "Gender": "M"
                              },
                              "sort": [
                                 20
                              ]
                           }
                        ]
                     }
                  }
               }
            ]
         }
      }
   }
}

Here's the code I used to set it up:

http://sense.qbox.io/gist/04742b9a9ce5b2b25a3829f0ffc719992ef20ad3

Collectives™ on Stack Overflow

How to query this data with elasticseach

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related