Nested Aggregation, terms -> terms -> count Elasticsearch

Question

Example of my documents:

[
  {
    username: 'userA',
    action: 'click',
    page: 'home'
  },
  {
    username: 'userA',
    action: 'click',
    page: 'home'
  },
  {
    username: 'userA',
    action: 'scroll',
    page: 'home'
  },
  {
    username: 'userA',
    action: 'click',
    page: 'productA'
  },
  {
    username: 'userB',
    action: 'scroll',
    page: 'productA'
  },
  ...
]

Example of the nested aggregation I need:

{
  userA: {
    home: {
      click: 2,
      scroll: 1
    },
    productA: {
      click: 1
    },
  },
  userB: {
    productA: {
      scroll: 1
    }
  }
  ...
}

I have this code working so far but I don't understand how to nest:

POST /index/_search?size=0
{
  "aggs" : {
    "usernames" : { 
      "terms": { 
        "field" : "username.keyword",
        "size": 10000
      }
    }
  }
}

This gives me all usernames which is a good start but how to I get the second nested aggregation per username?

@baitmbarek all are text with additional .keyword to use terms aggregations — user12341234
– user12341234, Commented Dec 12, 2019 at 21:49

baitmbarek · Accepted Answer · 2019-12-13 08:18:53Z

Here's an example to retrieve the data you need.

Elasticsearch has a boxed format to represent this kind of aggregations with nested buckets.

You'll have to parse the response to retrieve precisely the format you described in your question :)

POST myindex/_search
{
    "size": 0,
    "aggs": {
      "by_name": {
        "terms": {
          "field": "username.keyword",
          "size": 10
        },
        "aggs": {
          "by_action": {
            "terms": {
              "field": "action.keyword",
              "size": 10
            },
            "aggs": {
              "by_page": {
                "terms": {
                  "field": "page.keyword",
                  "size": 10
                }
              }
            }
          }
        }
      }
    }
  }

Response (aggregation part) :

"aggregations": {
    "by_name": {
      "doc_count_error_upper_bound": 0,
      "sum_other_doc_count": 0,
      "buckets": [
        {
          "key": "userA",
          "doc_count": 3,
          "by_action": {
            "doc_count_error_upper_bound": 0,
            "sum_other_doc_count": 0,
            "buckets": [
              {
                "key": "click",
                "doc_count": 3,
                "by_page": {
                  "doc_count_error_upper_bound": 0,
                  "sum_other_doc_count": 0,
                  "buckets": [
                    {
                      "key": "home",
                      "doc_count": 2
                    },
                    {
                      "key": "productA",
                      "doc_count": 1
                    }
                  ]
                }
              }
            ]
          }
        },
        {
          "key": "userB",
          "doc_count": 1,
          "by_action": {
            "doc_count_error_upper_bound": 0,
            "sum_other_doc_count": 0,
            "buckets": [
              {
                "key": "scroll",
                "doc_count": 1,
                "by_page": {
                  "doc_count_error_upper_bound": 0,
                  "sum_other_doc_count": 0,
                  "buckets": [
                    {
                      "key": "productA",
                      "doc_count": 1
                    }
                  ]
                }
              }
            ]
          }
        }
      ]
    }
  }

Collectives™ on Stack Overflow

Nested Aggregation, terms -> terms -> count Elasticsearch

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related