Elasticsearch query for deeply nested field

Question

I am trying to find all records between two dates, but can't figure out the proper query.

The mapping looks like this

GET my-books-index-1/_mapping
{
  "my-books-index-1": {
    "mappings": {
      "properties": {
        "book": {
          "properties": {
            "bookInfo": {
              "properties": {
                "publisherInfo": {
                  "type": "nested",
                  "properties": {
                    "publication": {
                      "properties": {
                        "publishedOn": {
                          "type": "date"
                        }
                      }
                    }
                  }
                }
              }
            }
          }
        }
      }
    }
  }
}

Following is a sample record for the above mapping

"_source": {
  "book": {
    "name": "Harry Potter",
    "bookInfo": {
      "author": "J.K. Rowling",
      "publisherInfo": [
        {
          "price": "100",
          "publication": {
            "publishedOn": 1685268404000 // [Sunday, May 28, 2023 10:06:44 AM]
          }
        }
      ]
    }
  }
}

[NOTE]: Some additional properties are removed from the mapping sample to keep it short and precise.

I am trying to find all books published between 25th May to 31st May.

Any help is appreciated. Thanks.

Answer 1

You can use range query inside of nested path.

PUT test_my-books-index-1
{
    "mappings": {
      "properties": {
        "book": {
          "properties": {
            "bookInfo": {
              "properties": {
                "publisherInfo": {
                  "type": "nested",
                  "properties": {
                    "publication": {
                      "properties": {
                        "publishedOn": {
                          "type": "date"
                        }
                      }
                    }
                  }
                }
              }
            }
          }
        }
      }
    }
}

---

POST test_my-books-index-1/_bulk?refresh
{"index":{"_id":"1"}}
{"book":{"name":"Harry Potter","bookInfo":{"author":"J.K. Rowling","publisherInfo":[{"price":"100","publication":{"publishedOn":1685268404000}}]}}}

dynamic date bigger than 10 days ago

GET test_my-books-index-1/_search
{
  "query": {
    "bool": {
      "must": [
        {
          "nested": {
            "path": "book.bookInfo.publisherInfo",
            "query": {
              "range": {
                "book.bookInfo.publisherInfo.publication.publishedOn": {
                  "gte": "now-10d",
                  "lte": "now"
                }
              }
            }
          }
        }
      ]
    }
  }
}

to search with exact date

GET test_my-books-index-1/_search
{
  "query": {
    "bool": {
      "must": [
        {
          "nested": {
            "path": "book.bookInfo.publisherInfo",
            "query": {
              "range": {
                "book.bookInfo.publisherInfo.publication.publishedOn": {
                  "gte": "25/05/2023",
                  "lte": "31/05/2023",
                  "format": "dd/MM/yyyy||yyyy"
                }
              }
            }
          }
        }
      ]
    }
  }
}

another example here: elasticsearch nested range query