Elasticsearch Terms Aggregation on array -- filter to buckets that match your query?

Viewed 32

I'm using a elasticsearch terms aggregation to bucket based on an array property on each document. I'm running into an issue where I get back buckets that are not in my query, and I'd like to those filter out.

Let's say each document is a Post, and has an array property media which specifies which social media website the post is on (and may be empty):

{
   id: 1
   media: ["facebook", "twitter", "instagram"]
}
{
   id: 2
   media: ["twitter", "instagram", "tiktok"]
}
{
   id: 3
   media: ["instagram"]
}
{
   id: 4
   media: []
}

And, let's say there's another index of Users, which stores a favorite_media property of the same type.

{
  id: 42
  favorite_media: ["twitter", "instagram"]
}

I have a query uses a terms lookup to filter, then does a terms aggregation.

{
    "query": {
        "filter": {
            "terms": {
                "index": "user_index",
                "id": 42,
                "path": "favorite_media"
            }
        }
    },
    "aggs": {
        "Posts_by_media": {
            "terms": {
                "field": "media",
                "size": 1000
            }
        }
    }
}

This will result in:

{
  ...
  "aggregations": {
    "Posts_by_media": {
      "doc_count_error_upper_bound": 0,   
      "sum_other_doc_count": 0,           
      "buckets": [                        
        {
          "key": "instagram",
          "doc_count": 3
        },
        {
          "key": "twitter",
          "doc_count": 2
        },
        {
          "key": "facebook",
          "doc_count": 1
        },
        {
          "key": "tiktok",
          "doc_count": 1
        }
      ]
    }
  }
}

Because media is an array property, any document that matches the filter will be used to create buckets, and I'll have buckets that don't match my filter. Here I want to only get back buckets facebook and instagram, since those are the two that I'm filtering to (via the terms-lookup).

I know terms aggregations offer a includes ability, but that doesn't work for me here since I'm using a terms-lookup, and don't know the data in favorite_media at query time.

How can I limit my buckets to be only those that match the filters in my query?

Thank you for your help!

0 Answers
Related