elasticsearch aggregation on aggregation

Question

I have some indexed records like these:

{
 song_id: 123,
 user_id: 1000,
 date: "2018-06-04T16:01:20"
},{
 song_id: 123,
 user_id: 1000,
 date: "2018-05-03T14:21:17"
},{
 song_id: 123,
 user_id: 1001,
 date: "2018-05-25T22:54:37"
},{
 song_id: 124,
 user_id: 1001,
 date: "2018-04-25T22:54:37"
}

Now, i wanna to make an aggregation query with two user_id's as input and aggregation should say which songs was downloaded by both users. I expect result show me the song_id 123, If my input was user_id's 1000 and 1001.

Notice

• Some songs maybe downloaded twice or more by a user.

@andrei-stefan

Answer 1

{
  "from": 0,
  "size": 0,
  "query": {
    "bool": {
      "must": [
        {
          "bool": {
            "should": [
              {
                "match": {
                  "user_id": 1000
                }
              },
              {
                "match": {
                  "user_id": 1001
                }
              }
            ]
          }
        }
      ]
    }
  },
  "aggs": {
    "songs": {
      "terms": {
        "size": 1000, 
        "field": "song_id",
        "order": {
          "_count": "desc"
        }
      },
      "aggs": {
        "should_we_consider": {
          "bucket_selector": {
            "buckets_path": {
              "hits": "users._bucket_count"
            },
            "script": "params.hits == 2"
          }
        },
        "users": {
          "terms": {
            "field": "user_id"
          }
        }
      }
    }
  }
}