# Can I use SQL EXCEPT to compare fields present in two indexes?

**URL:** <https://discuss.elastic.co/t/can-i-use-sql-except-to-compare-fields-present-in-two-indexes/265245>\
**Category:** Kibana\
**Tags:** elastic-stack-sql\
**Created:** [February 23, 2021, 7:14pm UTC](https://discuss.elastic.co/t/can-i-use-sql-except-to-compare-fields-present-in-two-indexes/265245 "2021-02-23T19:14:16Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![CoffeeRecon](https://avatars.discourse-cdn.com/v4/letter/c/c5a1d2/32.png) [@CoffeeRecon](https://discuss.elastic.co/u/CoffeeRecon)\
**Post date:** [February 23, 2021, 7:14pm UTC](https://discuss.elastic.co/t/can-i-use-sql-except-to-compare-fields-present-in-two-indexes/265245/1 "2021-02-23T19:14:17Z")

</div>

Hello! I have two indexes that I'd like to compare:

- winlogbeat-\*
- winlogbeat\_rollup

winlogbeat\_rollup is the rollup of winlogbeat-\* and contains its historical data. What I'm looking to do is to compare these two indexes using the common field, "host.name". I'd like this comparison query to return the host.name values that are present in winlogbeat\_rollup (historical data) but are not present in winlogbeat-\*. The idea is to eventually use a time constraint on winlogbeat-\* so that the query returns host names that I'm no longer receiving records for.  
  
  
I figured the easiest way to do this was with the following SQL query:

```auto
POST _sql?format=txt
{
    "query" : """
    SELECT host.name.terms.value as Server FROM "winlogbeat_rollup"
    EXCEPT
    SELECT host.name as Server FROM "winlogbeat-*"
    """
}

```

  
However this returns the following parsing error:

```auto
line 4:3: mismatched input 'SELECT' expecting {<EOF>, ',', 'FULL', 'GROUP', 'HAVING', 'INNER', 'JOIN', 'LEFT', 'LIMIT', 'NATURAL', 'ORDER', 'PIVOT', 'RIGHT', 'WHERE', LIMIT_ESC}

```

  
Is the SQL Except supported in Kibana? If not, is there another way I can form a query that will provide me with the results I'm looking for?

---

<div class="post-metadata">

**Author:** ![jportner](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jportner/32/75692_2.png) [@jportner](https://discuss.elastic.co/u/jportner)\
**Post date:** [February 23, 2021, 8:06pm UTC](https://discuss.elastic.co/t/can-i-use-sql-except-to-compare-fields-present-in-two-indexes/265245/2 "2021-02-23T20:06:28Z")

</div>

It appears that `EXCEPT` is not supported. There is a [limitations](https://www.elastic.co/guide/en/elasticsearch/reference/current/sql-limitations.html) page in the ES SQL docs, but that doesn't call `EXCEPT` out specifically, but I did check the supported terms in the [lexer code](https://github.com/elastic/elasticsearch/blob/a92a647b9f17d1bddf5c707490a19482c273eda3/x-pack/plugin/sql/src/main/java/org/elasticsearch/xpack/sql/parser/SqlBaseLexer.java) and EXCEPT is nowhere to be found.

I was going to suggest trying `NOT EXISTS`, `LEFT JOIN`, or `NOT IN` instead, but it appears those are not supported yet either. For example I attempted to run a query with `NOT IN` and got this error:

```auto
{
  "type" : "parsing_exception",
  "reason" : "line 3:10: IN query not supported yet"
}

```

I think we've discovered two problems:

1. If `EXCEPT` is not supported, it should have a more descriptive error message like the one above
2. The aforementioned "limitations" docs should have more info on what terms aren't supported

I've reached out to the team in charge of the ES SQL engine, I'll reply here when I hear back.

---

<div class="post-metadata">

**Author:** ![jportner](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jportner/32/75692_2.png) [@jportner](https://discuss.elastic.co/u/jportner)\
**Post date:** [February 24, 2021, 4:08pm UTC](https://discuss.elastic.co/t/can-i-use-sql-except-to-compare-fields-present-in-two-indexes/265245/3 "2021-02-24T16:08:47Z")

</div>

Here is a high-level example of a workaround to achieve an EXCEPT-like result using rollups:

```auto
PUT rollup1/_doc/1
{
  "rollup": true,
  "host": {
    "name": "host1"
  }
}
PUT rollup2/_doc/1
{
  "rollup": true,
  "host": {
    "name": "host2"
  }
}
PUT raw1/_doc/1
{
  "host": {
    "name": "host1"
  }
}
PUT raw2/_doc/2
{
  "host": {
    "name": "host3"
  }
}
POST /_sql?format=txt&pretty
{
  "query": """
  SELECT host.name FROM (
    SELECT
      host.name,
      MAX(CASE WHEN rollup THEN 1 ELSE 0 END) in_rollup,
      MAX(CASE WHEN NOT NVL(rollup, false) THEN 1 ELSE 0 END) in_original
    FROM "rollup*,raw*"
    GROUP BY host.name
  )
  WHERE in_rollup = 1 AND in_original != 1
  """
}

```

It results in the following:

```auto
   host.name   
---------------
host2      

```

Notice the `"rollup": true` field in the rollup indexes that makes it possible to figure out in the query where is the hostname coming from (the `_index` meta field is not supported).

* * *

One extra gotcha for your particular use case: You have `host.name.terms.value` (rollup index) and `host.name` (logbeat index). The index mapping in this case won’t be compatible ( `host.name` is an object in one vs a keyword/text in the other). One way to solve this would be with multiple queries and re-alias, but since we cannot do join or any set operations between subqueries the only alternative I see is [runtime fields](https://www.elastic.co/guide/en/elasticsearch/reference/master/runtime.html). Remapping `host.name.terms.value` and `host.name` to the `host_name` runtime field in both indexes.

I hope that helps!

---

<div class="post-metadata">

**Author:** ![CoffeeRecon](https://avatars.discourse-cdn.com/v4/letter/c/c5a1d2/32.png) [@CoffeeRecon](https://discuss.elastic.co/u/CoffeeRecon)\
**Post date:** [February 26, 2021, 2:51pm UTC](https://discuss.elastic.co/t/can-i-use-sql-except-to-compare-fields-present-in-two-indexes/265245/4 "2021-02-26T14:51:49Z")

</div>

That makes sense. I've had some issues though when I've attempted mapping the runtime values. I followed the examples found [here](https://www.elastic.co/guide/en/elasticsearch/reference/master/runtime-mapping-fields.html). These were my first attempts:

```auto
PUT temptest-serverlist
{
  "mappings": {
    "dynamic": "runtime",
    "properties": {
      "host_name": {
        "type": "keyword",
        "script": {
            "source": "doc['host.name'].value"
        }
      }
    }
  }
}

PUT temptest-serverlist
{
  "mappings": {
    "runtime": {
      "host_name": {
        "type": "keyword",
        "script": {
            "source": "doc['host.name'].value"
        }
      }
    }
  }
}

```

These both gives "index already exists" error as I'm trying to map a runtime field in an existing index and this structure seems to only be for only new indexes. I wasn't able to find a concrete answer within the documentation to map a runtime field within an existing index but this is what I tried:

```auto
PUT /temptest-serverlist/_mappings
{
  "properties": {
    "host_name": {
      "type": "keyword",
      "script":{
        "source": "doc['host.name'].value"
      }
    }
  }
}

```

This returns an error as "script" is not a supported parameter of "properties." I tried "runtime" in place of it but that's not a supported parameter of \_mappings. Do you know how to add a runtime field to an existing index?

Thanks for the help you've already provided!

---

<div class="post-metadata">

**Author:** ![jportner](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jportner/32/75692_2.png) [@jportner](https://discuss.elastic.co/u/jportner)\
**Post date:** [March 1, 2021, 9:31pm UTC](https://discuss.elastic.co/t/can-i-use-sql-except-to-compare-fields-present-in-two-indexes/265245/5 "2021-03-01T21:31:42Z")

</div>

> [@CoffeeRecon](#):
>
> Do you know how to add a runtime field to an existing index?

Sure, you were really close, you'll want to do this:

```auto
PUT /temptest-serverlist/_mapping
{
  "runtime": {
    "host_name": {
      "type": "keyword",
      "script":{
        "source": "emit(doc['host.name'].value)"
      }
    }
  }
}

```

1. You're not changing the mapping's `properties` field, you're changing the `runtime` field
2. You need to use an `emit` in your painless script [(source)](https://www.elastic.co/guide/en/elasticsearch/reference/7.11/runtime-mapping-fields.html#runtime-mapping-fields)
3. You don't need to use dynamic mapping, but you can if you want to

> [@CoffeeRecon](#):
>
> Thanks for the help you've already provided!

You're welcome!

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [March 29, 2021, 9:31pm UTC](https://discuss.elastic.co/t/can-i-use-sql-except-to-compare-fields-present-in-two-indexes/265245/6 "2021-03-29T21:31:59Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.