Elastic search query on two values - term and range?

Question

I am trying to do a query where I want documents based on two values - name (string) and percentage (number). Example would be - I want those documents which have "audience.location.countries.name" = "US" and "audience.location.countries.percentage" > 60. So I want a document which has an object {"name":"US", "percentage":"65"} Here, "audience.location.countries" is a an array of objects with two properties - {"name","percentage"}. Here is an example document:

"location": {
              "countries": [
                {
                  "name": "CA",
                  "percentage": 4
                },
                {
                  "name": "GB",
                  "percentage": 5
                },
                {
                  "name": "JP",
                  "percentage": 8
                },
                {
                  "name": "US",
                  "percentage": 60
                }
              ]}  

This a query that I tried but it throws an error: "[and] query malformed, no start_object after query name"

GET opensponsorship/_search
    {
      "query": {
        "bool": {
          "filter": [
            {
              "term": {
                "isPublic": true
              }
            },
            {
              "term": {
                "isDeleted": false
              }
            },
            {
              "and":[
                  {
                    "range": {
                      "audience.location.countries.name": "US"
                    }
                  },
                  {
                    "term":{
                      "audience.location.countries.percentage": {"gt": 59}
                    }
                  }
                ]
            }
          ]
        }
      },
      "size": "60",
      "from": 0,
      "sort": [
        {
          "followers": {
            "order": "desc"
          }
        }
      ]
    }

I am new to elastic search and have a very limited knowledge. Can someone please help?

Answer 1

From what I can tell the query is "broken" on several accounts:

1. You use the deprecated and query (BTW, what ES version are you using?)
2. The range and term filters over the name and percentage fields are mixed
3. The field audience.location.countries should be a "nested object"

Following is a query which resolves issues 1 & 2. I'll then explain issue 3:

GET opensponsorship/_search
{
  "query": {
    "bool": {
      "filter": [
        {
          "term": {
            "isPublic": true
          }
        },
        {
          "term": {
            "isDeleted": false
          }
        },
        {
          "term": {
            "audience.location.countries.name": "US"
          }
        },
        {
          "range": {
            "audience.location.countries.percentage": {
              "gt": 59
            }
          }
        }
      ]
    }
  },
  "size": "60",
  "from": 0,
  "sort": [
    {
      "followers": {
        "order": "desc"
      }
    }
  ]
}

Regarding issue 3, I suggest you read up on nested fields in elasticsearch. In short - If audience.location.countries is not a nested object, you will have "false positive" results due to how Elastic "flattens" objects. To fix this you'll need to 1) make audience.location.countries a nested object type in the mapping, and 2) wrap the contries term filters with a nested query in the following manner:

GET opensponsorship/_search
{
  "query": {
    "bool": {
      "filter": [
        {
          "term": {
            "isPublic": true
          }
        },
        {
          "term": {
            "isDeleted": false
          }
        },
        {
          "nested": {
            "path": "audience.location.countries",
            "query": {
              "bool": {
                "filter": [
                  {
                    "term": {
                      "audience.location.countries.name": "US"
                    }
                  },
                  {
                    "range": {
                      "audience.location.countries.percentage": {
                        "gt": 59
                      }
                    }
                  }
                ]
              }
            }
          }
        }
      ]
    }
  },
  "size": "60",
  "from": 0,
  "sort": [
    {
      "followers": {
        "order": "desc"
      }
    }
  ]
}

Hope this helps. Good luck!