How to count number of values per group?

Question

I have an index with the following mapping:

"my_index":{
     "mapping": {
         "properties": {
            "rec_values": {
                "type": "nested",
                "properties": {
                         "name": {
                           "type:" "keyword"
                          },  
                         "schm_p": {
                           "type:" "keyword"
                          },  
                         "tbl_p": {
                           "type:" "keyword"
                         },  

I want to count number values for each schm_p

something like:

select count(*)
from my_index
group by rec_values.schm_p

How can I do it ?

Answer 1

you need to use the aggregation for this query something like this:

GET my_index/_search
{
   "query": {
   "match_all": {}
},
"size": 0,
"aggs": {
  "count_schm_p": {
    "terms": {
      "field": "rec_values.schm_p.keyword",
      "size": 100
    }
   }
  }
}

this query would return a response like this

{
  "took": 561,
  "timed_out": false,
  "_shards": {
    "total": 1,
    "successful": 1,
    "skipped": 0,
    "failed": 0
  },
  "hits": {
    "total": {
      "value": 10000,
      "relation": "gte"
    },
    "max_score": null,
    "hits": []
  },
  "aggregations": {
    "count_schm_p": {
      "doc_count_error_upper_bound": 0,
      "sum_other_doc_count": 829099,
      "buckets": [
        {
          "key": "type_a",
          "doc_count": 1704640
        },
        {
          "key": "type_b",
          "doc_count": 1454079
        },
        {
          "key": "type_c",
          "doc_count": 894678
        },
        {
          "key": "type_d",
          "doc_count": 208489
        }
      ]
    }
  }
}

the count of each schm_p is inside your aggregation key

note: the size inside your query need to match with how many schm_p types do you have.

Answer 2

You need to do a Composite Aggregation, like this:

{
    "size": 0,
    "aggs": {
        "parameters": {
            "nested": {
                "path": "rec_values"
            },
            "aggs": {
                "group": {
                    "composite": {
                        "size": 100, // your size 
                        "sources": [{
                            "count_schm_p": {
                                "terms": {
                                    "field": "rec_values.schm_p"
                                }
                            }
                        }]
                    }
                }
            }
        }
    }
}