how to match an array value by it's key in a key value pair elasticsearch array?

Create empty index "twitter"

$ curl -XDELETE 'http://localhost:9200/twitter/' $ curl -XPUT 'http://localhost:9200/twitter/'

create geo_point mapping for the actual "tweet"

$ curl -XPUT 'http://localhost:9200/twitter/tweet/_mapping' -d ' { "tweet": { "properties": { "db": { "type": "object", "properties": { "@type": { "type": "string" }, "oracle_props": { "type": "nested", "properties": { "@name": { "type": "string" }, "@value": { "type": "string" } } } } } } } }'

Let's check if the mapping was set

$ curl -XGET 'http://localhost:9200/twitter/tweet/_mapping?pretty=true'

Post some tweets, with nested data

$ curl -XPUT 'http://localhost:9200/twitter/tweet/1' -d '{ "name": "Athena", "version": 1, "db": { "@type": "Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 64bit", "oracle_props": [ { "@name": "open_cursors", "@value": 4000 }, { "@name": "USER_ROLE_PRIVS_COUNT", "@value": 1 }, { "@name": "CREATE_PERMISSION", "@value": "Y" } ] } }'

Query nested only

$ curl -XGET localhost:9200/twitter/tweet/_search -d '{ "query": { "nested" : { "path" : "db.oracle_props", "score_mode" : "avg", "query" : { "bool" : { "must" : [ { "term": { "db.oracle_props.@name": "open_cursors" } }, { "range": { "db.oracle_props.@value": { "lte": 4000 } } } ] } } } } }';

Query "Athena" and "Oracle"

$ curl -XGET localhost:9200/twitter/tweet/_search -d '{ "query" : { "bool" : { "must" : [ { "match" : {"tweet.name" : "Athena"} }, { "match" : {"tweet.db.@type" : "Oracle"} } ] } } }'

Combine the former two queries

$ curl -XGET localhost:9200/twitter/tweet/_search -d '{ "query" : { "bool" : { "must" : [ { "match" : {"tweet.name" : "Athena"} }, { "match" : {"tweet.db.@type" : "Oracle"} }, { "nested" : { "path" : "db.oracle_props", "score_mode" : "avg", "query" : { "bool" : { "must" : [ { "term": { "db.oracle_props.@name": "open_cursors" } }, { "range": { "db.oracle_props.@value": { "lte": 4000 } } } ] } } } } ] } } }'

Results as

{ "took": 2, "timed_out": false, "_shards": { "total": 5, "successful": 5, "failed": 0 }, "hits": { "total": 1, "max_score": 2.462332, "hits": [ { "_index": "twitter", "_type": "tweet", "_id": "1", "_score": 2.462332, "_source": { "name": "Athena", "version": 1, "db": { "@type": "Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 64bit", "oracle_props": [ { "@name": "open_cursors", "@value": 4000 }, { "@name": "USER_ROLE_PRIVS_COUNT", "@value": 1 }, { "@name": "CREATE_PERMISSION", "@value": "Y" } ] } } } ] } }