Elasticsearch aggregation on values in nested list (array)

Question

I have stored some values in Elasticsearch nested data type (an array) but without using key/value pair. An example record would be:

{
  "categories": [
  "Category1",
  "Category2"
  ],
  "product_name": "productx"
}

Now I want to run aggregation query to find out unique list of categories available. But all the examples I've seen pointed to mapping that has key/value. Is there any way I can use above schema as is or do I need to change my schema to something like this to run aggregation query

{
  "categories": [
     {"name": "Category1"},
     {"name": "Category2"}
  ],
  "product_name": "productx"
}

Kamal Kunjapur · Accepted Answer · 2018-11-16 11:41:16Z

2

Well regarding JSON structure, you need to take a step back and figure out if you'd want list or key-value pairs.

Looking at your example, I don't think you need key-value pairs but again its something you may want to clarify by understanding your domain if there'd be some more properties for categories.

Regarding aggregation, as far as I know, aggregations would work on any valid JSON structure.

For the data you've mentioned, you can make use of the below aggregation query. Also I'm assuming the fields are of type keyword.

Aggregation Query

POST <your_index_name>/_search
{
  "size": 0,
  "aggs": {
    "myaggs": {
      "terms": {
        "size": 100,
        "script": {
          "inline": """
            def myString = "";
            def list = new ArrayList();
            for(int i=0; i<doc['categories'].length; i++){
              myString = doc['categories'][i] + ", " + doc['product'].value;
              list.add(myString);
            }
            return list;
            """
        }
      }
    }
  } 
}

Aggregation Response

{
  "took": 1,
  "timed_out": false,
  "_shards": {
    "total": 5,
    "successful": 5,
    "failed": 0
  },
  "hits": {
    "total": 1,
    "max_score": 0,
    "hits": []
  },
  "aggregations": {
    "myaggs": {
      "doc_count_error_upper_bound": 0,
      "sum_other_doc_count": 0,
      "buckets": [
        {
          "key": "category1, productx",
          "doc_count": 1
        },
        {
          "key": "category2, productx",
          "doc_count": 1
        }
      ]
    }
  }
}

Hope it helps!

edited Nov 16, 2018 at 11:41

answered Nov 16, 2018 at 7:03

Kamal Kunjapur

8,9182 gold badges26 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Sameera Godakanda Over a year ago

I read more about array data type. If I use nested type, I cannot add array type field in to nested field and my example wouldn't work. In my original mapping (since I've created categories field dynamically) it was set to a text field and keyword is the first element of the array so aggression wouldn't work. I guess I will settle with key/val pair and nested data type.

Kamal Kunjapur Over a year ago

The aggregation I've provided works if you replace categories with categories.keyword. However yes, I'd suggest you to play around with nested and non-nested fields, queries to understand how they work and try to see how they'd match your requirements and use-cases and make changes accordingly. Also note that for nested type, you would required to make use of nested aggregations.

Sameera Godakanda Over a year ago

Yes. I used the keyword before. however your query above missing size: parameter inside "terms": { so it was only showing 10 results. Apparently you cannot use size:0 and had to use some large value there.

Kamal Kunjapur Over a year ago

That's good observation. Yes. Aggregation for terms only shows top 10 buckets. You need to add size manually to get larger list. Thanks for bringing this up, I've updated my answer accordingly. Added 100 to the size. :|

Collectives™ on Stack Overflow

Elasticsearch aggregation on values in nested list (array)

1 Answer 1

Aggregation Query

Aggregation Response

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Aggregation Query

Aggregation Response

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related