How to set multiple filter conditions in a Nested Object

Hi,

I have an object with multiple nested objects. Like this:

{ "_source" : {
    'title': 'blabla',
    "languages":[
        {"id":2,"name":"English","slug":"English","level":"0","mandatory":"false"},

        {"id":4,"name":"German","slug":"German","level":"1","mandatory":"true"}
    ]

I need to get documents which have the English language at level gte 0 AND the German language at level gte 1.

I'm here but without success:

[{"nested":{
  "path":"languages",
   "query":{
     "bool":{
      "must":[
       
       {"bool":{"must":[{"match":{"languages.id":2}},{"range":{"languages.level":{"gte":0}}}]}},
       
       {"bool":{"must":[{"match":{"languages.id":4}},{"range":{"languages.level":{"gte":1}}}]}}
]}}}}]

Any help would be appreciated.

What is the result you are getting?

Could you provide a full recreation script as described in About the Elasticsearch category. It will help to better understand what you are doing. Please, try to keep the example as simple as possible.

A full reproduction script will help readers to understand, reproduce and if needed fix your problem. It will also most likely help to get a faster answer.

Thank you for your answer.

Put some documents:

PUT testing/_doc/1
{
  "title": "my first document",

  "languages": [

    {
      "id": 1,
      "name": "English",
      "level": 2
    },

    {
      "id": 2,
      "name": "German",
      "level": 1
    }   
  ]
}

PUT testing/_doc/2
{
  "title": "my second document",

  "languages": [

    {
      "id": 1,
      "name": "English",
      "level": 2
    },

    {
      "id": 3,
      "name": "Spanish",
      "level": 2
    }   
  ]
}

Where languages field is defined as a nested object.

Then I need documents for example with English (id 1) level gt 0 and German (id 2) level gt 1. The documents need to accomplish both conditions.

The query:

{
"body": {
  "query": {
    "bool": {
      "filter": [
        {
          "nested": {
            "path": "languages",
            "query": {
              "bool": {
                "must": [
                  {
                    "bool": {
                      "must": [
                        {
                          "match": {
                            "languages.id": 1
                          }
                        },
                        {
                          "range": {
                            "languages.level": {
                              "gte": 0
                            }
                          }
                        }
                      ]
                    }
                  },
                  {
                    "bool": {
                      "must": [
                        {
                          "match": {
                            "languages.id": 2
                          }
                        },
                        {
                          "range": {
                            "languages.level": {
                              "gte": 0
                            }
                          }
                        }
                      ]
                    }
                  }
                ]
              }
            }
          }
        }
      ]
    }
  }
}
}

The result is doc 1 and doc 2. But doc 2 has not German at level gt 0.

I hope now it is more clear.

Great! It would help even more if you share the creation of the index with the associated mapping. That way we can just copy and paste your example in Kibana to reproduce and help.

Sure:

PUT testing
{
  "mappings": {
    "doc": {
      "properties": {
        "title": {
          "type": "text"
        },
        "languages": {
          "type": "nested",
          "properties": {
            "id": {
              "type": "integer"
            },
            "name": {
              "type": "text"
            },
            "level": {
              "type": "integer"
            }
          }
        }
      }
    }
  }
}

I think you need to define a nested query anytime you enter a bool clause.

Here we go:

DELETE testing
PUT testing
{
  "mappings": {
    "_doc": {
      "properties": {
        "title": {
          "type": "text"
        },
        "languages": {
          "type": "nested",
          "properties": {
            "id": {
              "type": "integer"
            },
            "name": {
              "type": "text"
            },
            "level": {
              "type": "integer"
            }
          }
        }
      }
    }
  }
}
PUT testing/_doc/1
{
  "title": "my first document",
  "languages": [

    {
      "id": 1,
      "name": "English",
      "level": 2
    },

    {
      "id": 2,
      "name": "German",
      "level": 1
    }   
  ]
}

PUT testing/_doc/2
{
  "title": "my second document",
  "languages": [

    {
      "id": 1,
      "name": "English",
      "level": 2
    },

    {
      "id": 3,
      "name": "Spanish",
      "level": 2
    }   
  ]
}
GET testing/_search
{
  "query": {
    "bool": {
      "filter": [
        {
          "bool": {
            "must": [
              {
                "nested": {
                  "path": "languages",
                  "query": {
                    "bool": {
                      "must": [
                        {
                          "match": {
                            "languages.id": 1
                          }
                        },
                        {
                          "range": {
                            "languages.level": {
                              "gte": 0
                            }
                          }
                        }
                      ]
                    }
                  }
                }
              },
              {
                "nested": {
                  "path": "languages",
                  "query": {
                    "bool": {
                      "must": [
                        {
                          "match": {
                            "languages.id": 2
                          }
                        },
                        {
                          "range": {
                            "languages.level": {
                              "gte": 0
                            }
                          }
                        }
                      ]
                    }
                  }
                }
              }
            ]
          }
        }
      ]
    }
  }
}

BTW you index creation is incorrect as the type is _doc and not doc.

3 Likes

oh wth!?

i've tried before to post the question but didn't work.

I've tried again and now it works. Thanks :slight_smile:

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.