Searching for matching fields

[parosio]

Hello,
I'm trying to figure out if there's a way to query an index to retrieve the documents where field_A = field_B...

Using data from https://demo.elastic.co (kibana_sample_data_flights)

Sort of (in SQL):

select OriginWeather, count(*) from kibana_sample_data_flights where OriginWeather = DestWeather group by OriginWeather

My closest hypothesis so far is to build an aggregation like the following
(which comes from a kibana heat map).
But I don't know how to complete the following step to only get the values on the diagonal.

GET kibana_sample_data_flights/_search
{
  "size": 0,
  "_source": {
    "excludes": []
  },
  "aggs": {
    "2": {
      "terms": {
        "field": "DestWeather",
        "size": 5,
        "order": {
          "_count": "desc"
        }
      },
      "aggs": {
        "3": {
          "terms": {
            "field": "OriginWeather",
            "size": 5,
            "order": {
              "_count": "desc"
            }
          }
        }
      }
    }
  },
  "stored_fields": [
    "*"
  ],
  "script_fields": {
    "hour_of_day": {
      "script": {
        "inline": "doc['timestamp'].value.hourOfDay",
        "lang": "painless"
      }
    }
  },
  "docvalue_fields": [
    "timestamp"
  ],
  "query": {
    "match_all": {}
  }
}

[parosio]

I did not realize I could experiment with SQL within Kibana itself.

I just tried
POST _xpack/sql
{
"query":"select OriginWeather, count(*) from flights where OriginWeather = DestWeather group by 1"
}

but found a sql_illegal_argument_exception : "Comparisons against variables are not (currently) supported; offender [DestWeather] in [=]"

[Andrei_Stefan]

Indeed, SQL doesn't support this yet.
How about this type of query/aggregations? It will give you aggregations based on a script and all the documents that don't have destination==origin will be grouped in a "other" type of bucket, which you won't be interested into.

GET flights/_search
{
  "size": 0,
  "_source": {
    "excludes": []
  },
  "aggs": {
    "2": {
      "terms": {
        "size": 5,
        "script": {
          "source": "if (doc['DestWeather.keyword'] == doc['OriginWeather.keyword']) return doc['DestWeather.keyword']; else return 'other';",
          "lang": "painless"
        }
      }
    }
  },
  "query": {
    "match_all": {}
  }
}

[parosio]

Thank you Andrei,
this is a good solution for my problem!

[Andrei_Stefan]

No problem @parosio. Thank you for raising this question.

The solution would fit well with SQL and in the future you could run a SELECT that would use two fields from the same document in comparison. For the reference, this is the feature request I created to track this effort.