How to aggregate nested key/value pairs ONLY IF they match a filter?

Elastic Stack Elasticsearch
1

Hi All,

I have a model similar to the following, where a user can have a set of attributes which are just key/value pairs. attributes are mapped as a nested field.

How can I get a list of all available countries? (Basically, all available values where key = "country"). I wasn't able to get the filter aggregation to work. It seems that filter aggregation operates on the parent doc.

{
  "id": 1234,
  "name": "John",
  "attributes": [
    {
      "key": "age",
      "value": 30
    },
    {
      "key": "address",
      "value": "123 Evergreen st"
    },
    {
      "key": "city",
      "value": "Los Angeles"
    },
    {
      "key": "country",
      "value": "USA"
    },
    {
      "key": "phone",
      "value": "310-234-2233"
    },
   ...
  ]
}

Thanks,

Drew

2

Perform a nested query on attributes.key: country, and do a nested terms agg on attributes.value.

3

Wouldn't that return all the values for ALL the attributes for users who have the key: country? I want to get only the countries.

  • Drew
4

Oh, right.

Yeah, not sure how to pull that off, except to have ordinary field/value pairs, or to have attributes as a child type.

Without diving into the other pros and cons of using parent/child, if they were parent/child, you would have a query something like:

POST people_props/attribute/_search
{
  "query": {
    "bool": {
      "filter": [
        {
          "has_parent": {
            "parent_type": "person",
            "query": {
              "term": {
                "name": "John"
              }
            }
          }
        },
        {
          "has_parent": {
            "parent_type": "person",
            "query": {
              "term": {
                "id": "1234"
              }
            }
          }
        },
        {
          "term": {
            "key": "country"
          }
        }
      ]
    }
  },
  "aggs": {
    "countries": {
      "terms": {
        "field": "value"
      }
    }
  }
}
5

Unfortunately I can't use parent/child for this.

6

I guess you're stuck with reverting your key values to actual properties then. Or doing some client-side processing.

7

No fortunately I figured it out.

For anyone stumbling on this problem in future, you can use the following aggregation:

{
  "size": 0,
  "aggs": {
    "attributes": {
      "nested": {
        "path": "attributes"
      },
      "aggs": {
        "key": {
          "filter": {
            "term": {
              "attributes.key": "country"
            }
          },
          "aggs": {
            "values": {
              "terms": {
                "field": "attributes.value"
              }
            }
          }
        }
      }
    }
  }
}
1 Like
8

Nice!