How to compare two fields of same name on different doc_type

[vimal]

Hi,
I have two oracle tables containing millions of rows in each table. I have index all records into elasticsearch using logstash jdbc plugin.

The Idea is to compare difference between two tables using Elasticsearch in fast manner. Faster than SQL.

I have index called myindex and two documents type table1 and table2.

I have to compare column1 of table1 document type with column1 of table2 document type.

How can it be achieved in elastic search?

[pastechecker (Charlie)]

You can do it on the logstash itself.

For example you will write a filter section:

filter {
if [table1] == [table2]{
mutate {
add_field => {"field_equality" => "field_is_the_same"}
}
}
else {
mutate {
add_field => {"field_equality" => "field_is_not_the_same"}
}
}
}

Test:

input { stdin { codec => json}}


filter {
        if [table1] == [table2]{
                mutate {
                        add_field => {"field_equality" => "field_is_the_same"}
                }
        }
        else {
                mutate {
                        add_field => {"field_equality" => "field_is_not_the_same"}
                }
        }
}

output { stdout { codec => rubydebug}}


TEST DATA: {"table1": "table1", "table2": "table2"}
OUTPUT:
{
        "@timestamp" => 2020-01-21T10:56:59.429Z,
              "host" => "srv-cdc0058.phenom.semicon.io",
    "field_equality" => "field_is_not_the_same",
          "@version" => "1",
            "table2" => "table2",
            "table1" => "table1"
}

TEST DATA: {"table1": "table1", "table2": "table1"}
OUTPUT: 
{
        "@timestamp" => 2020-01-21T10:57:14.431Z,
              "host" => "srv-cdc0058.phenom.semicon.io",
    "field_equality" => "field_is_the_same",
          "@version" => "1",
            "table2" => "table1",
            "table1" => "table1"
}

Is it what you wanted or i misunderstood your question?
If not, please share some data example.