Combine has_parent with AND & OR


(Fredrik Hörte) #1

Hi, I have a parent-child document setup that looks like this:

{
    "mappings": {
        "campaigns":{
            "properties":{
                "name": {
                    "type": "string"
                },
                "paused": {
                    "type":"boolean"
                }
            }
        },
        "ads":{
            "_parent": {
                "type": "campaigns"
            },
            "properties": {
                "id": { "type": "string"  },
                "categories":{ "type": "string","index":"not_analyzed" }
            }
        }
    }
}

I want to do a query that returns all ads that have certain categories and where the campaign is not paused.
Is it possible to query on both has_parent and the child in one query?
In plain SQLish syntax it would be like this select ad where campaign.paused = false and (ad.categories = sports or ad.categories = health).
I have tried various queries but can't get it to work as i expect, e.g. this query below.

{
    "custom_filters_score" : {
        "query" : {
            "match_all" : {}
        },
        "filter": {
            "bool": {              
                "must": {
                    "has_parent": {
                        "type": "campaigns",
                        "filter": {
                            "term": {
                                "paused": false
                            }
                        }
                    }
                },
                "should": [
                        {
                            "term" : { "categories" : "finance" }
                        },
                        {
                            "term" : { "categories" : "sports" }
                        }
                ]                                
            }
        }       
    }
}

Any suggestion of how I can solve this in one query?


(Mark Harwood) #2

Right now your "should" criteria is just that - a nice to have rather than mandatory.
Turn your "must" clause into an arrray and shift the ad-category criteria in there as a {bool->should} clause alongside the has_parent clause


(Fredrik Hörte) #3

Thank you for the pointers. I got it to work like this:

{
    "query": {
        "function_score" : {
            "query" : {
                "match_all" : {}
            },
            "filter": {
                "bool": {              
                    "must": {
                        "has_parent": {
                            "type": "campaigns",
                            "filter": {
                                "term": {
                                    "paused": false
                                }
                            }
                        }
                    },
                    "should": [
                        {
                            "term" : { "categories" : "finance" }
                        },
                        {
                            "term" : { "categories" : "sports" }
                        }    
                    ]
                }
            }       
        }
    }    
}

(system) #4