Is there a way to write a query where x must be true AND y must be a + b, OR c + d?

[ccsisko (Courtney)]

Hello! We're writing a query where a couple fields must be true, while another field must equal something OR something else.

More specifically, we're querying for a document that has a status of X, and the amount value unit must be USD and over 10, or the amount value must be AUD and over 20. I'm having some trouble figuring out how to format the amount part of the query.

If I search for each amount value and unit separately, the queries work fine. For example, this query returns the expected results:

POST /index_name/_search
{
  "query": {
    "bool": {
      "must": [
        {
          "match": {
            "status": "X"
          }
        },
        {
          "nested": {
            "path": "amount",
            "query": {
              "bool": {
                "must": [
                  {
                    "match": {
                      "amount.unit": "USD"
                    }
                  },
                  {
                    "range": {
                      "amount.value": {
                        "gte": 10
                      }
                    }
                  }
                ]
              }
            }
          }
        }
      ]
    }
  }
}

But if I try running this query, I get incorrect results:

POST /index_name/_search
{
  "query": {
    "bool": {
      "must": [
        {
          "match": {
            "status": "X"
          }
        }
      ],
      "should": [
        {
          "nested": {
            "path": "amount",
            "query": {
              "bool": {
                "must": [
                  {
                    "match": {
                      "amount.unit": "USD"
                    }
                  },
                  {
                    "range": {
                      "amount.value": {
                        "gte": 10
                      }
                    }
                  }
                ]
              }
            }
          }
        },
        {
          "nested": {
            "path": "amount",
            "query": {
              "bool": {
                "must": [
                  {
                    "match": {
                      "amount.unit": "AUD"
                    }
                  },
                  {
                    "range": {
                      "amount.value": {
                        "gte": 20
                      }
                    }
                  }
                ]
              }
            }
          }
        }
      ]
    }
  }
}

I thought that "should" was equivalent to "or", but it's not working in the way that I would expect- it returns documents with a value that is less than what I've specified. Is there a different way this kind of query should be formatted? Or do I just have to write a separate query for each amount value + unit clause?

[RabBit_BR (andre.coelho)]

Hi @ccsisko

Try this query:

{
  "query": {
    "bool": {
      "filter": [
        {
          "term": {
            "status.keyword": "X"
          }
        }
      ],
      "must": [
        {
          "bool": {
            "should": [
              {
                "nested": {
                  "path": "amount",
                  "query": {
                    "bool": {
                      "must": [
                        {
                          "match": {
                            "amount.unit": "USD"
                          }
                        },
                        {
                          "range": {
                            "amount.value": {
                              "gte": 10
                            }
                          }
                        }
                      ]
                    }
                  }
                }
              },
              {
                "nested": {
                  "path": "amount",
                  "query": {
                    "bool": {
                      "must": [
                        {
                          "match": {
                            "amount.unit": "AUD"
                          }
                        },
                        {
                          "range": {
                            "amount.value": {
                              "gte": 20
                            }
                          }
                        }
                      ]
                    }
                  }
                }
              }
            ]
          }
        }
      ]
    }
  }
}

[ccsisko (Courtney)]

Ahh thank you so much! This appears to be working as expected. If you have a minute, do you mind explaining why this one works over what I originally had? I just want to make sure I understand. It seems like we can't have two "must" statements in one query, so filtering on the status and then using the "must" statement for the amounts and values will allow us to check for everything that we're wanting to?

Regardless of whether you have time to respond, I really appreciate the help!