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"
                            }
                        ]
                    }
                }
            }
        ]
    }
}