title: ElasticSearch Join Filter: Using subquery results as filter input possible?
body: <p>I have a Use Case where I want to use ElasticSearch for realtime analytics. Within that, I want to be able to calculate some simple affinity scores.</p>
  
  <p>Those are currently defined using the number of transactions a filtered-by-criteria user base performs, compared with the complete user base.</p>
  
  <p>From my understanding, I'd need to do the following:</p>
  
  <ol>
  <li>Get the distinct transactions of my filtered user base</li>
  <li>Query for these transaction (types) in the complete user base</li>
  <li>Do the calculation (norming etc.)</li>
  </ol>
  
  <p>To get the "distinct transactions" for the filtered user base, I currently use a Terms Filter Query with faceting which returns all terms (transaction types). As far as I understand, I's need to use this result as input of a Terms Filter Query for the second step to be able to receive the result I want.</p>
  
  <p>I read that there's a pull request on GitHub which seems to implement this (<a href="https://github.com/elasticsearch/elasticsearch/pull/3278" rel="noreferrer">https://github.com/elasticsearch/elasticsearch/pull/3278</a>), but it's not really obvious to me whether this is already usable in a current release or not.</p>
  
  <p>If not, are there some workarounds how I could implement this?</p>
  
  <p>As additional info, here is my sample mapping:</p>
  
  <pre><code>curl -XPUT 'http://localhost:9200/store/user/_mapping' -d '
  {
    "user": {
      "properties": {
        "user_id": { "type": "integer" },
        "gender": { "type": "string", "index" : "not_analyzed" },
        "age": { "type": "integer" },
        "age_bracket": { "type": "string", "index" : "not_analyzed" },
        "current_city": { "type": "string", "index" : "not_analyzed" },
        "relationship_status": { "type": "string", "index" : "not_analyzed" },
        "transactions" : {
          "type": "nested",
          "properties" : {
            "t_id": { "type": "integer" },
            "t_oid": { "type": "string", "index" : "not_analyzed" },
            "t_name": { "type": "string", "index" : "not_analyzed" },
            "tt_id": { "type": "integer" },
            "tt_name": { "type": "string", "index" : "not_analyzed" },
          }
        }
      }
    }
  }'
  </code></pre>
  
  <p>So, for my actual desired result for my example Use Case, I'd have the following:</p>
  
  <ol>
  <li>My filtered user base would have this example filter: "gender": "male" &amp; "relationship_status": "single". For these, I want to get the distinct transaction types (field "tt_name" of the nested document) and count the number of distinct user_ids.</li>
  <li>Next, I want to query my complete user base (no filter other than the list of transaction types from 1.) and count the number of distinct user_ids</li>
  <li>Do the "affinity" calculations</li>
  </ol>