(ES 6.5.4) filtering by nested date field

Hi I'm relatively new ES and I've created an index with the following mapping:

{
  "_doc": {
    "date_detection": "false",
    "properties": {
      "@fields": {
        "properties": {
          "client": {
            "type": "text",
            "fields": {
              "raw": {
                "type": "keyword",
                "ignore_above": 8191
              }
            }
          },
          "bar": {
            "type": "integer"
          },
          "foo": {
            "type": "text",
            "fields": {
              "raw": {
                "type": "keyword",
                "ignore_above": 8191
              }
            }
          },
          "fooIn": {
            "type": "integer"
          },
          "missDoc": {
            "type": "nested",
            "properties": {
              "formula": {
                "type": "keyword",
                "ignore_above": 8191
              },
              "key": {
                "type": "text",
                "fields": {
                  "raw": {
                    "type": "keyword",
                    "ignore_above": 8191
                  }
                }
              },
              "isNa": {
                "type": "keyword",
                "ignore_above": 8191
              },
              "missTime": {
                "type": "date",
                "format": "yyyymmdd HH:mm:ss"
              },
              "ns": {
                "type": "keyword",
                "ignore_above": 8191
              }
            }
          },
          "fooplat": {
            "type": "text",
            "fields": {
              "raw": {
                "type": "keyword",
                "ignore_above": 8191
              }
            }
          },
          "foosess": {
            "type": "text",
            "fields": {
              "raw": {
                "type": "keyword",
                "ignore_above": 8191
              }
            }
          },
          "sn": {
            "type": "text",
            "fields": {
              "raw": {
                "type": "keyword",
                "ignore_above": 8191
              }
            }
          }
        }
      }
    }
  }
}

I've written a very simple query in which the data is filtered by the @fields.missDoc.missTime which is a field in a nested type.

  {
  "from": 0,
  "size": 1,
  "query": {
    "bool": {
      "must": [{
        "nested": {
          "path": "@fields.missDoc",
          "query": {
            "bool": {
              "filter": [{
                "range": {
                  "@fields.missDoc.missTime": {
                    "gte": "20191109 23:58:09",
                    "lte": "20191130 23:59:59"
                  }
                }
              }]
            }
          }
        }
      }]
    }
  }
}

when I execute the query the data returned contains docs that are not within the specified date range. I also tried querying for a date range that is not in the index yet the query still returns data. I'd like to get some suggestions on how to correctly write this query.

Found my issue. The date format for the missTime field was the culprit. I changed it to "yyyyMMdd HH:mm:ss" and the query worked as expected.

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