Perform multi-field / multi-dimensional aggregations with nested fields in Elastic Search

user_10878195 0
Viewed 134

I am tracking attendance of few students. I am storing their details in the index like the below.

Each doc in "entries" have few other fields. The following data shows that a student has attended 6 classes on "Monday".

"entries" is of type "nested"

     {
      reg_id: 1111,
      "entires" : [
        {
          id: "123"
          day: 'Monday'
        },
        {
          id: "1234",
          attendance: true
        },
        {
          id: "12345",
          classes_attended: 6
        }
      ],
    }

I want the count of each classes_attended of students for each day.

For Example "72 entries of students found for "Monday", who has attended 6 classes"

Sample desired output - This is just a sample I am completely fine if the output schema is changed.

    [
      {
        "day" : "monday", 
        "classes_attended": 6,
        count: 4
      },
      {
        "day" : "monday", 
        "classes_attended": 1,
        count: 5
      },
      {
        "day" : "tuesday", 
        "classes_attended": 5,
        count: 2
      },
      {
        "day" : "tuesday", 
        "classes_attended": 6,
        count: 1
      }
    ]

Not sure How to start with the aggregations query:

I tried with the following query but I know its not the correct solution

"aggs": {
    "attendance_aggs": {
      "nested": {
        "path": "entries"
      },
      "aggs": {
        "days": {
          "terms": {
            "field": "entries.day"
          },
          "aggs": {
          "attended": {
            "reverse_nested": {},
            "aggs":{
              "class_attended_day": {
                "terms": {
                  "field": "entries.classes_attended"
                },
                "aggs": {
                  "class_attended_days_count": {
                    "reverse_nested": {},
                    "aggs": {
                      "classes_attended_final": {
                        "cardinality": {
                          "field": "entries.class_attended"
                        }
                      }
                    }
                  }
                }
              }
            }
          }
        }
        }
      }
    }
  }
kibana elasticsearch-aggregation elasticsearch aggregation
1 Answers

It's unclear what the top-level object is so I'm going to assume it's a "student attendance entry per day". I'm also unsure what the entries.ids represent but I'll assume you'll be needing them at some later point so I'll keep them untouched.

Now, since all that your entries objects have in common is the id, they can be decoupled. Meaning that you should be using nested if any only if you share some attributes across all objects which need their attribute connections preserved. Since I don't see entries.id anywhere in your aggs, I'd recommend the following adjustments to your mapping:

PUT students
{
  "mappings": {
    "properties": {
      "day": {                ------------
        "type": "keyword"                |
      },                                 |
      "attendance": {                    |                                
        "type": "boolean"                | <--
      },                                 |
      "classes_attended": {              |
        "type": "integer"                |
      },                     ------------
      "entries": {
        "type": "nested",
        "properties": {
          "day": {
            "type": "keyword",
            "copy_to": "day"               <--
          },
          "attendance": {
            "type": "boolean",
            "copy_to": "attendance"        <--
          },
          "classes_attended": {
            "type": "integer",
            "copy_to": "classes_attended"  <--
          }
        }
      }
    }
  }
}

and here's your query:

GET students/_search
{
  "size": 0,
  "aggs": {
    "days": {
      "terms": {
        "field": "day"
      },
      "aggs": {
        "classes_attended": {
          "terms": {
            "field": "classes_attended"
          },
          "aggs": {
            "student_count": {
              "cardinality": {
                "field": "_id"
              }
            }
          }
        }
      }
    }
  }
}

The response can then be post-processed into whatever you prefer.

EDIT

You could hijack reverse_nested but will need to come back to it as you're referencing other nested entries:

GET students/_search
{
  "size": 0,
  "aggs": {
    "attendance_aggs": {
      "nested": {
        "path": "entries"
      },
      "aggs": {
        "days": {
          "terms": {
            "field": "entries.day"
          },
          "aggs": {
            "attended": {
              "reverse_nested": {},
              "aggs": {
                "class_attended_day": {
                  "nested": {
                    "path": "entries"
                  },
                  "aggs": {
                    "class_attended_day": {
                      "terms": {
                        "field": "entries.classes_attended"
                      },
                      "aggs": {
                        "classes_attended_final": {
                          "cardinality": {
                            "field": "entries.classes_attended"
                          }
                        }
                      }
                    }
                  }
                }
              }
            }
          }
        }
      }
    }
  }
}
user_81603180
Related