# Searching for matching fields
**URL:** https://discuss.elastic.co/t/searching-for-matching-fields/148572
**Category:** Elasticsearch
**Created:** [September 14, 2018, 7:42am UTC](https://discuss.elastic.co/t/searching-for-matching-fields/148572 "2018-09-14T07:42:12Z")
**Posts on this page:** 6
**Page:** 1
### Author:  [@parosio](https://discuss.elastic.co/u/parosio)
#### Post date: [September 14, 2018, 7:42am UTC](https://discuss.elastic.co/t/searching-for-matching-fields/148572/1 "2018-09-14T07:42:12Z")
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](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": {}
}
}
```
---
### Author:  [@parosio](https://discuss.elastic.co/u/parosio)
#### Post date: [October 10, 2018, 9:17am UTC](https://discuss.elastic.co/t/searching-for-matching-fields/148572/2 "2018-10-10T09:17:36Z")
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 [=]_"
😞
---
### Author:  [@Andrei\_Stefan](https://discuss.elastic.co/u/Andrei_Stefan)
#### Post date: [October 11, 2018, 10:36am UTC](https://discuss.elastic.co/t/searching-for-matching-fields/148572/3 "2018-10-11T10:36:46Z")
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.
```auto
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": {}
}
}
```
---
### Author:  [@parosio](https://discuss.elastic.co/u/parosio)
#### Post date: [October 11, 2018, 10:45am UTC](https://discuss.elastic.co/t/searching-for-matching-fields/148572/4 "2018-10-11T10:45:39Z")
Thank you Andrei,
this is a good solution for my problem!
---
### Author:  [@Andrei\_Stefan](https://discuss.elastic.co/u/Andrei_Stefan)
#### Post date: [October 11, 2018, 11:48am UTC](https://discuss.elastic.co/t/searching-for-matching-fields/148572/5 "2018-10-11T11:48:03Z")
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](https://github.com/elastic/elasticsearch/issues/34400) is the feature request I created to track this effort.
---
### Author:  [@system](https://discuss.elastic.co/u/system)
#### Post date: [November 8, 2018, 11:48am UTC](https://discuss.elastic.co/t/searching-for-matching-fields/148572/6 "2018-11-08T11:48:06Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.