elastic search: use terms in nested field

Question

I have a lot of documents with data that looks like this:

   "paymentMethods": [
         {
            "id": 194,
            "name": "Wire",
            "logo": "wire.gif"
        }, {
            "id": 399,
            "name": "Paper Check",
            "logo": "papercheck.gif"
        }

Mapping:

 "paymentMethods": {
    "type": "nested",
    "properties": {
        "id": {
            "type": "long"
        },
        "logo": {
            "type": "string",
            "index": "not_analyzed"
        },
        "name": {
            "type": "string",
            "index": "not_analyzed"
        }
    }
 }

I try to get all the documents that have paymentMethos.id 399 & 194. this query is works for me:

    "query": {
        "filtered": {
            "filter": {
                "bool": {
                    "must": [{
                        "nested": {
                            "path": "paymentMethods",
                            "query": {
                                "bool": {
                                    "must": [

                                        {
                                            "term": {
                                                "paymentMethods.id": 399
                                            }
                                        }

                                    ]
                                }
                            }
                        }
                    }]
                }
            },
            "query": {
                "match_all": {}
            }
        }
    }

the problem is that I need all the documents with id 399 & 194 so I tried it:

"must" : [
   { "terms":{"paymentMethods.id" : [399,194]} }
]

but the result is kind of OR I want it as AND. I also tried this one but it don't work at all

"must" : [{
    "term": {
        "paymentMethods.id": 399
    }
}, {
    "term": {
        "paymentMethods.id": 194
    }
}]

any suggestions how can I get paymentMethods.id 399 & 194?

thanks.

Answer 1

well, after some digging around i found the problem, each bool clause (must,should,must_not) should have it's own nested query i.e.

{"query": {
            "bool": {
                "must": [
                    {
                    "nested": {
                        "path": "paymentMethods",
                        "query": {
                            "term" : { "paymentMethods.id":399 }
                        }
                    }
                },
                {
                    "nested": {
                        "path": "paymentMethods",
                        "query": {
                            "term" : { "paymentMethods.id":187 }
                        }
                    },

etc..

before i tried to search with "terms" which returns document with ANY of the match terms so i got documents with 187 or 399

the code above queries the nested hidden documents twice once for 187 and once for 399 and returns the intersection of both queries => all the documents with 187 & 399

(of-course the second query does not run on all the documents again but runs on the results of the previous filter result)