Elasticsearch aggregation to retrieve array of values associated with another value

Question

I am working with an Elasticsearch index with data like this:

"_source": {
    "article_number": "123456",
    "title": "Example item #1",
    "attributes": [
        {
            "key": "Type",
            "value": "Bag"
        },
        {
            "key": "Color",
            "value": "Grey"
        }
    ]
},

"_source": {
    "article_number": "654321",
    "title": "Example item #2",
    "attributes": [
        {
            "key": "Type",
            "value": "Bag"
        },
        {
            "key": "Color",
            "value": "Red"
        }
    ]
}

The goal is to dynamically generate search inputs in a page where there is one search input for each unique value of attributes.key and within that input one value for each corresponding value of attributes.value. So in this case I would want to render a "Type" input offering only the value "Bag" and a "Color" input offering the values "Grey" and "Red."

I am trying to accomplish this with an aggregation that will give me a unique set of all values of attributes.key along with an array of all the values of attributes.value that are associated with each key. An example of a result that would fit what I am hoping for would be this:

{
    [
        {
            "key": "Type",
            "values": [{
                "name": "Bag",
                "doc_count": 2
            }]
        },
        {
            "key": "Color",
            "values": [{
                "name": "Grey",
                "doc_count": 1
            },
            {
                "name": "Red",
                "doc_count": 1
            }]
        }
}

I have tried nested and reverse nested aggregations, as well as composite aggregations, but so far without success.

Jozef - Spatialized.io · Accepted Answer · 2022-11-19 19:20:02Z

2

Assuming your index mapping looks like this:

PUT attrs
{
  "mappings": {
    "properties": {
      "attributes": {
        "type": "nested",
        "properties": {
          "key": {
            "type": "keyword"
          },
          "value": {
            "type": "keyword"
          }
        }
      }
    }
  }
}

you can achieve the desired results with the following combination of a nested terms aggregation and its sub-aggregation:

POST attrs/_search
{
  "size": 0,
  "aggs": {
    "nested_context": {
      "nested": {
        "path": "attributes"
      },
      "aggs": {
        "by_keys": {
          "terms": {
            "field": "attributes.key",
            "size": 10
          },
          "aggs": {
            "by_values": {
              "terms": {
                "field": "attributes.value",
                "size": 10
              }
            }
          }
        }
      }
    }
  }
}

edited Nov 19, 2022 at 19:20

answered Mar 1, 2021 at 16:21

Jozef - Spatialized.io

17k4 gold badges29 silver badges79 bronze badges

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

2 Comments

AmericanUmlaut Over a year ago

Thank you! I really appreciate this.

Jozef - Spatialized.io Over a year ago

You're welcome! Hey btw I talk about practical aggregations use cases at length in my elasticsearch handbook. You can let me know which (other) topics interest you and I'll let you know as soon as the handbook comes out!

Collectives™ on Stack Overflow

Elasticsearch aggregation to retrieve array of values associated with another value

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related