How to count of fields with the same value in elas

Question

I have a list of communities. And I need to create aggregation query which will count all data which have the same title.

[
  {
    "_id": "56161cb3cbdad2e3b437fdc3",
    "_type": "Comunity",
    "name": "public",
    "data": [
      {
        "title": "sonder",
        "creationDate": "2015-08-22T03:43:28 -03:00",
        "quantity": 0
      },
      {
        "title": "vule",
        "creationDate": "2014-05-17T12:35:01 -03:00",
        "quantity": 0
      },
      {
        "title": "omer",
        "creationDate": "2015-01-31T04:54:19 -02:00",
        "quantity": 3
      },
      {
        "title": "sonder",
        "creationDate": "2014-05-22T05:09:36 -03:00",
        "quantity": 3
      }
    ]
  },
  {
    "_id": "56161cb3dae30517fc133cd9",
    "_type": "Comunity",
    "name": "static",
    "data": [
      {
        "title": "vule",
        "creationDate": "2014-07-01T06:32:06 -03:00",
        "quantity": 5
      },
      {
        "title": "vule",
        "creationDate": "2014-01-10T12:40:28 -02:00",
        "quantity": 1
      },
      {
        "title": "vule",
        "creationDate": "2014-01-09T09:33:11 -02:00",
        "quantity": 3
      }
    ]
  },
  {
    "_id": "56161cb32f62b522355ca3c8",
    "_type": "Comunity",
    "name": "public",
    "data": [
      {
        "title": "vule",
        "creationDate": "2014-02-03T09:55:28 -02:00",
        "quantity": 2
      },
      {
        "title": "vule",
        "creationDate": "2015-01-23T09:14:22 -02:00",
        "quantity": 0
      }
    ]
  }
]

So desire result should be

[
  {
    title: vule,
    total: 6
  },
  {
    title: omer,
    total: 1
  },
  {
    title: sonder,
    total: 1
  }
]

I wrote some aggregation queries but it still not work. How can I get desire result?

PS: I tried to create nested aggregation

ejs.Request().size(0).agg(
        ejs.NestedAggregation('comunities')
            .path('data')
            .agg(
                ejs.FilterAggregation('sonder')
                    .filter(
                    ejs.TermsFilter('data.title', 'sonder')
                ).agg(
                ejs.ValueCountAggregation('counts')
                      .field('data.title')
)
            )
    );

Answer 1

You need to use terms aggregations.

Now depending on your mapping there could be two ways of doing that:

1. Your data field is stored as an subdocument

You need to run a simple terms aggregation, which in RAW json looks like:

POST /test/test/_search
{
  "size": 0,
  "query": {
    "match_all": {}
  },
  "aggs": {
    "Grouping": {
      "terms": {
        "field": "data.title",
        "size": 0
      }
    }
  }
}

2. Your data field is stored as an nested document

You have to add a nested subaggregation before doing terms aggregation.

POST /test/test/_search
{
  "size": 0,
  "query": {
    "match_all": {}
  },
  "aggs": {
    "Nest": {
      "nested": {
        "path": "data"
      },
      "aggs": {
        "Grouping": {
          "terms": {
            "field": "data.title",
            "size": 0
          }
        }
      }
    }
  }
}

Both will output this:

{
   "took": 125,
   "timed_out": false,
   "_shards": {
      "total": 5,
      "successful": 5,
      "failed": 0
   },
   "hits": {
      "total": 3,
      "max_score": 0,
      "hits": []
   },
   "aggregations": {
      "Nest": {
         "doc_count": 9,
         "Grouping": {
            "doc_count_error_upper_bound": 0,
            "sum_other_doc_count": 0,
            "buckets": [
               {
                  "key": "vule",
                  "doc_count": 6            -- The Total count you're looking for
               },
               {
                  "key": "sonder",
                  "doc_count": 2
               },
               {
                  "key": "omer",
                  "doc_count": 1
               }
            ]
         }
      }
   }
}

This, unfortunately, is just a raw query, but I imagine that it can be translated into elastic.js quite easily.

On top of that. If you're going to do aggregations, don't forget to set your fields, that you're doing aggregations on, as not_analyzed, because it will start counting individual tokens as in documentation

I, myself, would store these documens as nested ones.

Example:

Mappings:

PUT /test
{
  "mappings": {
    "test": {
      "properties": {
        "name": {
          "type": "string"
        },
        "data": {
          "type": "nested",
          "properties": {
            "title": {
              "type": "string",
              "index": "not_analyzed",
              "fields": {
                "stemmed": {
                  "type": "string",
                  "analyzed": "standard"
                }
              }
            },
            "creationDate": {
              "type": "date",
              "format": "dateOptionalTime"
            },
            "quantity": {
              "type": "integer"
            }
          }
        }
      }
    }
  }
}

Test data:

PUT /test/test/56161cb3cbdad2e3b437fdc3
{
  "name": "public",
  "data": [
    {
      "title": "sonder",
      "creationDate": "2015-08-22T03:43:28",
      "quantity": 0
    },
    {
      "title": "vule",
      "creationDate": "2014-05-17T12:35:01",
      "quantity": 0
    },
    {
      "title": "omer",
      "creationDate": "2015-01-31T04:54:19",
      "quantity": 3
    },
    {
      "title": "sonder",
      "creationDate": "2014-05-22T05:09:36",
      "quantity": 3
    }
  ]
}

PUT /test/test/56161cb3dae30517fc133cd9
{
  "name": "static",
  "data": [
    {
      "title": "vule",
      "creationDate": "2014-07-01T06:32:06",
      "quantity": 5
    },
    {
      "title": "vule",
      "creationDate": "2014-01-10T12:40:28",
      "quantity": 1
    },
    {
      "title": "vule",
      "creationDate": "2014-01-09T09:33:11",
      "quantity": 3
    }
  ]
}

PUT /test/test/56161cb32f62b522355ca3c8
{
  "name": "public",
  "data": [
    {
      "title": "vule",
      "creationDate": "2014-02-03T09:55:28",
      "quantity": 2
    },
    {
      "title": "vule",
      "creationDate": "2015-01-23T09:14:22",
      "quantity": 0
    }
  ]
}

Actual query:

POST /test/test/_search
{
  "size": 0,
  "query": {
    "match_all": {}
  },
  "aggs": {
    "Nest": {
      "nested": {
        "path": "data"
      },
      "aggs": {
        "Grouping": {
          "terms": {
            "field": "data.title",
            "size": 0
          }
        }
      }
    }
  }
}

P.S. "size":0 means that I'm letting Elasticsearch output all possible terms and not limiting its output as described in documentation.

The size parameter can be set to define how many term buckets should be returned out of the overall terms list. By default, the node coordinating the search process will request each shard to provide its own top size term buckets and once all shards respond, it will reduce the results to the final list that will then be returned to the client. This means that if the number of unique terms is greater than size, the returned list is slightly off and not accurate (it could be that the term counts are slightly off and it could even be that a term that should have been in the top size buckets was not returned). If set to 0, the size will be set to Integer.MAX_VALUE.