# Filtering across indices: Return IDs which are in one index but not in another

**URL:** <https://forum.opensearch.org/t/filtering-across-indices-return-ids-which-are-in-one-index-but-not-in-another/5237>\
**Category:** SQL\
**Created:** [March 5, 2021, 1:02pm UTC](https://forum.opensearch.org/t/filtering-across-indices-return-ids-which-are-in-one-index-but-not-in-another/5237 "2021-03-05T13:02:58Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![JanK](https://avatars.discourse-cdn.com/v4/letter/j/e8c25b/32.png) [@JanK](https://forum.opensearch.org/u/JanK)\
**Post date:** [March 5, 2021, 1:02pm UTC](https://forum.opensearch.org/t/filtering-across-indices-return-ids-which-are-in-one-index-but-not-in-another/5237/1 "2021-03-05T13:02:58Z")

</div>

Hello community,

I am struggling with filtering across two indices.  
The example is as follows: In the first index (index\_1), there are data with values. I take the data values, multiply them by 10 and store them in index\_2 with the same ID as they have in index\_1. Then new data arrives in index\_1. I do not want to recalculate all values for index\_2 again but I only want to do the calculation for the new arrived data. Therefore, I need a query that only returns the IDs of index\_1 which are not in index\_2.

The first index looks like  
index\_1:  
ID = 11, value\_1 = 2.3  
ID = 32, value\_1 = 3.4  
ID = 43, value\_1 = 2.2  
ID = 50, value\_1 = 2.0

The second index looks like  
index\_2  
ID = 11, value\_2 = 23  
ID = 23, value\_2 = 34  
ID = 43, value\_2 = 22

The query should return ID = 50, value\_1 = 2.0 from index\_1 because ID50 is not in index\_2.

I already tried a left join and a postfiltering, but as discussed in [this topic](https://forum.opensearch.org/t/sql-left-join-with-where-issue/4415/2), the postfiltering is not properly working.

Does anyone have an idea how to query this? It seems like a basic query but so far I didnt find any solution.

Best,  
Jan

---

<div class="post-metadata">

**Author:** ![daichen](https://avatars.discourse-cdn.com/v4/letter/d/c4cdca/32.png) [@daichen](https://forum.opensearch.org/u/daichen)\
**Post date:** [March 19, 2021, 4:47pm UTC](https://forum.opensearch.org/t/filtering-across-indices-return-ids-which-are-in-one-index-but-not-in-another/5237/2 "2021-03-19T16:47:06Z")

</div>

@JanK If your indices share a pattern, probably you can try out index pattern, ex. `SELECT ID FROM index_*`. Thanks!

---

<div class="post-metadata">

**Author:** ![JanK](https://avatars.discourse-cdn.com/v4/letter/j/e8c25b/32.png) [@JanK](https://forum.opensearch.org/u/JanK)\
**Post date:** [March 22, 2021, 9:03am UTC](https://forum.opensearch.org/t/filtering-across-indices-return-ids-which-are-in-one-index-but-not-in-another/5237/3 "2021-03-22T09:03:08Z")

</div>

@daichen Thanks for our reply!  
I tried your suggestion. However, SELECT ID FROM index\_\* returns the union of the IDs from index\_1 and index\_2. I am looking for something like: index\_1.ID not in index\_2.ID. Do you have another idea how to query this?

---

<div class="post-metadata">

**Author:** ![daichen](https://avatars.discourse-cdn.com/v4/letter/d/c4cdca/32.png) [@daichen](https://forum.opensearch.org/u/daichen)\
**Post date:** [March 24, 2021, 5:20pm UTC](https://forum.opensearch.org/t/filtering-across-indices-return-ids-which-are-in-one-index-but-not-in-another/5237/4 "2021-03-24T17:20:27Z")

</div>

Got it. I think what you need is set intersect operator. Unfortunately, only union and minus (difference) are supported in old engine and JSON format only. Not sure if this is useful to you or not. We will add full support to the new engine in future. You can open an issue for tracking if this is what you need. Thanks!

```auto
POST index1/_doc
{
  "id": 11
}

POST index1/_doc
{
  "id": 50
}

POST index2/_doc
{
  "id": 11
}

POST _opendistro/_sql?format=json
{
  "query": """
    SELECT id FROM index1
    MINUS
    SELECT id FROM index2
  """
}
{
  "took" : 0,
  "timed_out" : false,
  "_shards" : {
    "total" : 0,
    "successful" : 0,
    "failed" : 0
  },
  "hits" : {
    "hits" : [
      {
        "_type" : "_doc",
        "_source" : {
          "id" : 50
        },
        "_id" : "eJU8ZXgBL9gQF7tvnJj8",
        "_score" : "NaN"
      }
    ],
    "total" : {
      "value" : 1,
      "relation" : "EQUAL_TO"
    },
    "max_score" : 1.0
  }
}

```

---

<div class="post-metadata">

**Author:** ![JanK](https://avatars.discourse-cdn.com/v4/letter/j/e8c25b/32.png) [@JanK](https://forum.opensearch.org/u/JanK)\
**Post date:** [April 15, 2021, 9:01am UTC](https://forum.opensearch.org/t/filtering-across-indices-return-ids-which-are-in-one-index-but-not-in-another/5237/5 "2021-04-15T09:01:42Z")

</div>

@daichen thanks for your answer!  
The minus operator is exactly what I am looking for. However, it did not work with my data as expected even though it worked with the example you provided. It took me some time to figure out what’s wrong. The problem is that in the indexes there are more than 10,000 entries.  
For example  
index\_1:  
ID = 11, value\_1 = 2.3  
ID = 32, value\_1 = 3.4  
ID = 43, value\_1 = 2.2  
ID = 50, value\_1 = 2.0

index\_2:  
ID = 11, value\_1 = 23  
ID = 32, value\_1 = 34  
ID = 32, value\_2 = 33  
ID = 32, value\_3 = 35  
…  
ID = 32, value\_10000 = 38  
ID = 43, value\_1 = 22

ID is here just a placeholder for any identifier, each entry still has an uuid.  
The Query

```
POST _opendistro/_sql?format=jso
 {
      "query": """
        SELECT id FROM index1
        MINUS
        SELECT id FROM index2
      """
}

```

only takes the first 10,000 results of index\_2 into account: ID 11 and ID 32. The thus query returns ID 43 and ID 50. Although ID 43 is in index 2.

I tried using distinct but this also did not result in the correct output.

```
POST _opendistro/_sql?format=jso
 {
      "query": """
        SELECT id FROM index1
        MINUS
        SELECT DISTINCT id FROM index2
      """
}

```

Is there a solution how to circumvent the limitation of 10,000 elements?

Best,  
Jan