Elasticsearch nested query with available values in range

Question

Trying to build a query for documents like:

{
    "name" : "Bob",
    "grades" : [
        {
            "year" : 2010,
            "grade": 8,  
        },
        {
            "year" : 2018,
            "grade": 7,  
        },
        {
            "year" : 2019,
            "grade": 4,  
        }
    ]
},
{
    "name" : "Alice",
    "grades" : [
        { 
            "year" : 2005,
            "grade": 3,  
        },
        {
            "year" : 2016,
            "grade": 8,  
        },
        {
            "year" : 2018,
            "grade": 7,  
        },
        {
            "year" : 2019,
            "grade": 6,  
        }
    ]
}

I have a range of years e.g. from 2010 till 2020. Query should return a document if for all available years (in document) in range (from 2010 till 2020) grades are > 5. If there are grades <= 5 outside the interested range of years, we do not care and those should not affect the final result in any way. So basically only document with Alice should be returned. After looking into the Elasticsearch documentation, can't find the solution (most likely I just miss something). I'm able to construct a query if there are no gaps in years, but in my case there are gaps. So my query just drops such documents. Current query:

{
    "query": {
        "bool": {
            "must": [
                {
                    "nested": {
                        "query": {
                            "bool": {
                                "must": [
                                    {
                                        "term": {
                                            "grades.year": {"value": 2010}
                                        }
                                    },
                                    {
                                        "range": {
                                            "grades.grade": {"from": 5}
                                        }
                                    }
                                ]
                            }
                        },
                        "path": "grades"
                    }
                },
                {
                    "nested": {
                        "query": {
                            "bool": {
                                "must": [
                                    {
                                        "term": {
                                            "grades.year": {"value": 2011}
                                        }
                                    },
                                    {
                                        "range": {
                                            "grades.grade": {"from": 5}
                                        }
                                    }
                                ]
                            }
                        },
                        "path": "grades"
                    }
                },
                ....
            ]
        }
    }
}

Probably I definitely miss something. Is it possible?

UPDATE

I've added year 2005 with grade 3 to Alice` grades. So now Alice still should be matched as year 2005 is outside the interested range.

Thanks!

Bhavya · Accepted Answer · 2020-11-18 01:04:24Z

2

You can use a must_not clause, that will exclude all the documents that contain grade less than or equal to 5

Search Query:

{
  "query": {
    "bool": {
      "must": [
        {
          "nested": {
            "path": "grades",
            "query": {
              "bool": {
                "must": [
                  {
                    "range": {
                      "grades.year": {
                        "gte": 2010,
                        "lte": 2020
                      }
                    }
                  }
                ]
              }
            }
          }
        }
      ],
      "must_not": {
        "nested": {
          "path": "grades",
          "query": {
            "bool": {
              "must": [
                {
                  "range": {
                    "grades.grade": {
                      "lte": 5
                    }
                  }
                }
              ]
            }
          }
        }
      }
    }
  }
}

Search Result:

"hits": [
      {
        "_index": "64882747",
        "_type": "_doc",
        "_id": "2",
        "_score": 1.0,
        "_source": {
          "name": "Alice",
          "grades": [
            {
              "year": 2016,
              "grade": 8
            },
            {
              "year": 2018,
              "grade": 7
            },
            {
              "year": 2019,
              "grade": 6
            }
          ]
        }
      }
    ]

answered Nov 18, 2020 at 1:04

Bhavya

16.2k3 gold badges23 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Zanas_x3 Over a year ago

Thanks! That is really helpful and almost does what I need. But apparently it drops document if at least one year have grade lte 5. If there is a year that is outside the interested range, we should not care if the grade is lte 5. I've updated my question. Thank you!

Bhavya Over a year ago

@Zanas_x3 but year 2005 is outside the range and the grade is also less than 5, still, you want that Alice document should match ?

Zanas_x3 Over a year ago

That's exactly it. We are interested only in grades that are in range [2010, 2020]. If the grade was less than 5 long time ago we really don't care. I'll edit my question to be more clear, sorry for confusion. Thanks!

Bhavya Over a year ago

@Zanas_x3 thanks for your reply :) Let me see if I can find a way to achieve your use case

Zanas_x3 Over a year ago

Thanks Bhavya! You helped me by directing into the correct path. Take a look to my solution, looks like it does what it should. Hopefully...

|

Zanas_x3 · Accepted Answer · 2020-11-18 08:39:55Z

Looks like I've found the solution! Thanks to @Bhavya for directing me. Basically some additional boolean logic in place. I've end up with the following query:

{
    "query": {
        "bool": {
            "must": [
                {
                    "nested": {
                        "path": "grades",
                        "query": {
                            "bool": {
                                "must": [
                                    {
                                        "range": {
                                            "grades.year": {
                                                "gte": 2010,
                                                "lte": 2020
                                            }
                                        }
                                    }
                                ]
                            }
                        }
                    }
                }
            ],
            "must_not":   {
                "nested": {
                    "path": "grades",
                    "query": {
                        "bool": {
                            "should": [
                                {
                                    "bool": {
                                        "must": [
                                            {
                                                "term": {
                                                    "grades.year": {
                                                        "value": 2010
                                                    }
                                                }
                                            },
                                            {
                                                "range": {
                                                    "grades.grade": {
                                                        "lte": 5
                                                    }
                                                }
                                            }
                                        ]
                                    }
                                },
                                ...
                                ...
                                ...
                                ...
                                ...
                            ]
                        }
                    }
                }
            }
        }
    }
}

Collectives™ on Stack Overflow

Elasticsearch nested query with available values in range

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related