Elastic Search: filter query results by entry its field into another query results

Question

I found the question about the IN equivalent operator:

ElasticSearch : IN equivalent operator in ElasticSearch

But I would to find equivalent to the another more complicated request:

 SELECT * FROM table WHERE id IN (SELECT id FROM anotherTable WHERE something > 0);

Mapping:

First index:

{
  "mappings": {
    "products": { 
      "properties": { 
        "id":      { "type": "integer" },  
        "name":    { "type": "text"  }, 
      }
    }
  }
}

Second index:

{
  "mappings": {
    "reserved": { 
      "properties": { 
        "id":      { "type": "integer" },  
        "type":    { "type": "text"  }, 
      }
    }
  }
}

I want to get products which ids are contained in reserved index and have the specific type of a reserve.

Please provide your mapping and some document example – Eli Dec 16 '17 at 19:36 — Eli, Dec 16 '17 at 19:36

score 0 · Accepted Answer · answered Dec 18 '17 at 13:41

First step - get all relevant ids from reserved index:

{
  "size": 0,
  "query": {
    "bool": {
      "must": [
        {
          "term": {
            "type": "TYPE_HERE"
          }
        }
      ]
    }
  },
  "aggregations": {
    "ids": {
      "terms": {
        "field": "id"
      }
    }
  }
}

--> see: Terms Aggregations, Bool Query and Term Query.

--> _source will retrieve only relevant field id.

Second step - get all relevant documents from products index:

{
  "query": {
    "bool": {
      "must": [
        {
          "terms": {
            "id": [
              "ID_1",
              "ID_2",
              "AND_SO_ON..."
            ]
          }
        }
      ]
    }
  }
}

--> take all the ids from first step and put them as a list under terms:id[...]

--> see Terms Query.

Alas, the body of the second request will be enormous. Anyway, thank you for your answer! — Joyerhat, Dec 19 '17 at 14:33
You can split the list and use several queries OR query one by one — Eli, Dec 22 '17 at 10:08

Elastic Search: filter query results by entry its field into another query results

1 Answers1