"Join" documents from different types (warning: long post)

We are using ES to provide faceted search and pivot aggregation - users love it :slightly_smiling: Until now, we have ~12 separate indexes with ~60 different types filled, updated and used by various applications.

Now our users are asking us about ways to create aggregations across those indexes. Like: Show the number of Tasks (one doc type in one index) pivoted by the asset class (a field "assetClass" of doc type "FinancialInstrument" in another index) and the invested amount (a numeric field "value" in yet another doc type and index, which would need to be summed up).

I'm aware of the "well known" ways of "joining" in ES

  • parent/child: doesn't work in my use case, because the document types are "loosely" coupled. One Task refers to a FinancialInstrument, while another on refers to some other doc type
  • denormalization: got that halfway going in a POC, but it is kind of painful because I need to keep track of it in many places in many applications, to get good results
  • application side join: I can see how this would work in filtering, but I don't really understand how I would do this for aggregations?

I found https://github.com/sutoiku/elasticsearch-native-script-joiner which actually looks very promising, but before trying it and porting to ES 2.x, I thought I'd ask if anybody has some ideas or pointers to share?

CU, Joe