OpenSearch Flattened Field Range Queries in Java: ISO8601 Dates

Are you trying to perform OpenSearch flattened range queries on date fields, only to discover that your dates aren’t matching correctly? Perhaps you’ve set up what looks like a perfectly reasonable date range query, but it’s returning zero results—or worse, matching dates that shouldn’t be in range at all. Flattened fields (OS flat object, ES flattened) store all sub-properties as keywords (unanalyzed strings), which means OpenSearch compares your dates lexicographically rather than chronologically. This creates unique challenges for range queries that can lead to some head-scratching moments if you’re not prepared.

This blog post demonstrates date range query patterns using code examples from the Code Sloth Code Samples repository. You can find the complete samples in GitHub via our Code Sloth Code Samples page.

If you’re new to flattened fields or want to understand how they work in general (including indexing, searching, and array limitations), be sure to check out our OpenSearch Flattened Field Type in Java blog post for more detailed information.

Note: This post focuses specifically on date range queries. If you’re working with numeric range queries on flattened fields, be sure to check out our companion post on OpenSearch Flattened Field Range Queries in Java: Zero-Padded Numbers. Other than some small syntax differences, the OpenSearch and Elasticsearch implementations are very similar—this article will refer to OpenSearch but link to both implementations, so feel free to adapt to Elasticsearch if that is your Engine of choice!

Let’s dive in! 🦥

What’s to Come

In this post, we’ll explore:

• Understanding Flattened Field Range Query Challenges – Why range queries on flattened fields behave differently
• Date Range Queries: The ISO8601 Requirement – Why dates must be ISO8601 formatted and how to work with them
• Single Point Date Range Queries – Working with gte, lte, gt, and lt operators
• Sloth Summary – Key takeaways and recommendations

Understanding Flattened Field Range Query Challenges

Before we dive into the specific challenges, let’s quickly recap what makes flattened fields special. Flattened fields (OS flat object, ES flattened) in OpenSearch treat an entire object as a single field, with all sub-properties indexed as keywords (unanalyzed strings). This means that when you perform range queries on flattened field properties, OpenSearch compares them lexicographically (ASCII order), not as the native data types they represent.

This fundamental difference creates a critical challenge for date range queries:

Date Range Queries: Dates must be formatted in a way that ensures lexicographic ordering matches chronological ordering. ISO 8601 format (like 2024-01-15T10:30:00Z) does exactly this, but non-ISO formats (like 01/15/2024 or January 15, 2024) will fail because they don’t sort correctly as strings.

Let’s explore this challenge in detail with actual test code examples.

Date Range Queries: The ISO8601 Requirement

Before we dive into date range queries, let’s take a quick look at the document classes used in these tests. They follow the same pattern as other flattened field examples, using strongly-typed records for nested objects:

public class ProductWithDateAttribute implements IDocumentWithId {
    private String id;
    private String name;
    private DateAttribute attribute; // Strongly-typed record that will be serialized as JSON object

    // Constructors, getters, setters, equals, hashCode, toString...
}

public record DateAttribute(
        String createdDate
) {
}

The ProductWithDateAttribute class contains a DateAttribute attribute, which will be mapped as a flattened field. The DateAttribute record stores the date as a String rather than using a native date type (like LocalDate or Date) to provide maximum flexibility over the format of the date values. This allows testing both ISO 8601 and non-ISO formatted dates, and ensures that the exact string representation is preserved when serialized to JSON.

Why ISO8601 Format is Required

ISO 8601 format ensures that lexicographic ordering matches chronological ordering by structuring dates from most significant to least significant components: YYYY-MM-DDTHH:mm:ssZ.

For example:

• 2024-01-15T00:00:00Z < 2024-01-20T00:00:00Z < 2024-01-25T00:00:00Z (both lexicographically and chronologically correct)

Let’s see this in action with our test code from FlattenedDateRangeTests.java. First, we’ll demonstrate what happens when non-ISO formats are used, then show how ISO 8601 format solves the problem:

Non-ISO Formats Fail Range Queries

Now let’s see what happens when we try to use non-ISO formatted dates:

@Test
public void flattenedDateRange_NonISOFormat_FailsRangeQueries() throws Exception {
    try (OpenSearchTestIndex testIndex = fixture.createTestIndex(mapping ->
            mapping.properties("attribute", Property.of(p -> p.flatObject(f -> f))))) {

        // Create documents with non-ISO formatted dates (MM/DD/YYYY format)
        // This includes a cross-year scenario that will fail in ASCII comparison
        ProductWithDateAttribute[] products = new ProductWithDateAttribute[]{
                new ProductWithDateAttribute("1", "Product1", new DateAttribute("12/30/2023")),
                new ProductWithDateAttribute("2", "Product2", new DateAttribute("12/31/2023")),
                new ProductWithDateAttribute("3", "Product3", new DateAttribute("01/01/2024")),
                new ProductWithDateAttribute("4", "Product4", new DateAttribute("01/02/2024"))
        };
        testIndex.indexDocuments(products);

        // Try to query for dates between 12/31/2023 and 01/01/2024 (inclusive)
        // Chronologically, this should match Product2 (12/31/2023) and Product3 (01/01/2024)
        // However, in ASCII comparison: "12/31/2023" > "01/01/2024" (because "1" > "0")
        // This means no strings can satisfy both gte("12/31/2023") and lte("01/01/2024")
        // simultaneously in ASCII order, so the query returns 0 results
        SearchResponse<ProductWithDateAttribute> result = loggingOpenSearchClient.search(s -> s
                        .index(testIndex.getName())
                        .query(q -> q
                                .range(r -> r
                                        .field("attribute.createdDate")
                                        .gte(JsonData.of("12/31/2023"))
                                        .lte(JsonData.of("01/01/2024"))
                                )
                        ),
                ProductWithDateAttribute.class
        );

        // The query fails because "12/31/2023" > "01/01/2024" in ASCII comparison
        // Since the lower bound is greater than the upper bound in ASCII order,
        // no documents can satisfy both conditions, resulting in 0 results
        // This demonstrates why ISO8601 format is required for reliable range queries
        assertThat(result.hits().total().value()).isEqualTo(0);
    }
}

This test demonstrates the failure case with a cross-year scenario. When dates are stored in non-ISO formats like MM/DD/YYYY, the lexicographic comparison doesn’t match chronological ordering.

ASCII comparison works left-to-right, character by character. When comparing these date strings, they align on the left side:

12/30/2023
12/31/2023
01/01/2024
01/02/2024

When comparing "12/31/2023" to "01/01/2024", we compare the first character: '1' vs '0'. Since '1' > '0', the comparison stops there—we never even look at the remaining characters. This is why "12/31/2023" > "01/01/2024" in ASCII order, even though chronologically December 31, 2023 comes before January 1, 2024. This breaks range queries entirely.

When querying for dates between 12/31/2023 and 01/01/2024, OpenSearch looks for strings that are:

• >= "12/31/2023" in ASCII order
• <= "01/01/2024" in ASCII order

Since "12/31/2023" > "01/01/2024" in ASCII, no string can satisfy both conditions simultaneously, resulting in zero results.

HTTP Commands for OpenSearch Dashboards

Index Creation Request:

PUT /range_query_date_non_iso
{
  "mappings": {
    "properties": {
      "attribute": {
        "type": "flat_object"
      }
    }
  },
  "settings": {
    "number_of_replicas": 0,
    "number_of_shards": 1
  }
}

Index Documents Request:

POST /range_query_date_non_iso/_bulk
{ "index" : { "_index" : "range_query_date_non_iso", "_id" : "1" } }
{ "id" : "1", "name" : "Product1", "attribute" : { "createdDate" : "12/30/2023" } }
{ "index" : { "_index" : "range_query_date_non_iso", "_id" : "2" } }
{ "id" : "2", "name" : "Product2", "attribute" : { "createdDate" : "12/31/2023" } }
{ "index" : { "_index" : "range_query_date_non_iso", "_id" : "3" } }
{ "id" : "3", "name" : "Product3", "attribute" : { "createdDate" : "01/01/2024" } }
{ "index" : { "_index" : "range_query_date_non_iso", "_id" : "4" } }
{ "id" : "4", "name" : "Product4", "attribute" : { "createdDate" : "01/02/2024" } }

Search Request: Range Query for Dates Between 12/31/2023 and 01/01/2024 (Fails Due to Non-ISO Format)

POST /range_query_date_non_iso/_search
{
  "query": {
    "range": {
      "attribute.createdDate": {
        "gte": "12/31/2023",
        "lte": "01/01/2024"
      }
    }
  }
}

Search Response:

{
  "took": 20,
  "timed_out": false,
  "_shards": {
    "failed": 0,
    "skipped": 0,
    "successful": 1,
    "total": 1
  },
  "hits": {
    "total": {
      "value": 0,
      "relation": "eq"
    },
    "max_score": null,
    "hits": []
  }
}

The query returns zero results because "12/31/2023" > "01/01/2024" in ASCII comparison (since "1" > "0" in the first character). This means the lower bound of the range query is actually greater than the upper bound in ASCII order, so no documents can satisfy both gte("12/31/2023") and lte("01/01/2024") simultaneously. The non-ISO date format (MM/DD/YYYY) is unreliable for range queries, which is why ISO 8601 format is required.

ISO8601 Format Works With Range Queries

Now let’s see how ISO 8601 format solves this problem:

@Test
public void flattenedDateRange_ISO8601Format_WorksWithRangeQueries() throws Exception {
    try (OpenSearchTestIndex testIndex = fixture.createTestIndex(mapping ->
            mapping.properties("attribute", Property.of(p -> p.flatObject(f -> f))))) {

        // Create documents with ISO8601 formatted dates (same dates as the failure test, but in ISO8601 format)
        // Using the same days of the year: 12/30, 12/31, 01/01, 01/02
        ProductWithDateAttribute[] products = new ProductWithDateAttribute[]{
                new ProductWithDateAttribute("1", "Product1", new DateAttribute("2023-12-30")),
                new ProductWithDateAttribute("2", "Product2", new DateAttribute("2023-12-31")),
                new ProductWithDateAttribute("3", "Product3", new DateAttribute("2024-01-01")),
                new ProductWithDateAttribute("4", "Product4", new DateAttribute("2024-01-02"))
        };
        testIndex.indexDocuments(products);

        // Query for dates between 2023-12-31 and 2024-01-01 (inclusive)
        // This is the same range as the failure test, but with ISO8601 format
        SearchResponse<ProductWithDateAttribute> result = loggingOpenSearchClient.search(s -> s
                        .index(testIndex.getName())
                        .query(q -> q
                                .range(r -> r
                                        .field("attribute.createdDate")
                                        .gte(JsonData.of("2023-12-31"))
                                        .lte(JsonData.of("2024-01-01"))
                                )
                        ),
                ProductWithDateAttribute.class
        );

        // Should match Product2 (2023-12-31) and Product3 (2024-01-01)
        // This demonstrates that ISO8601 format correctly handles the cross-year scenario
        assertThat(result.hits().total().value()).isEqualTo(2);
        assertThat(result.hits().hits().stream()
                .map(h -> h.source().getId())
                .sorted())
                .containsExactly("2", "3");
    }
}

This test demonstrates that ISO 8601 formatted dates work correctly with range queries, even in the same cross-year scenario that fails with non-ISO formats. The query successfully matches products with dates between December 31, 2023 and January 1, 2024, inclusive, because the ISO 8601 format ensures proper lexicographic ordering that matches chronological ordering.

Key points from this test:

1. Dates are stored as ISO 8601 formatted strings (e.g., "2023-12-30")
2. The same dates and range are used as the failure test (12/31/2023 to 01/01/2024), but formatted in ISO 8601
3. The range query correctly matches both boundary dates (Product2 and Product3)
4. ISO 8601 format handles the cross-year scenario correctly, unlike non-ISO formats

HTTP Commands for OpenSearch Dashboards

Index Creation Request:

PUT /range_query_date_iso8601
{
  "mappings": {
    "properties": {
      "attribute": {
        "type": "flat_object"
      }
    }
  },
  "settings": {
    "number_of_replicas": 0,
    "number_of_shards": 1
  }
}

Index Documents Request:

POST /range_query_date_iso8601/_bulk
{ "index" : { "_index" : "range_query_date_iso8601", "_id" : "1" } }
{ "id" : "1", "name" : "Product1", "attribute" : { "createdDate" : "2023-12-30" } }
{ "index" : { "_index" : "range_query_date_iso8601", "_id" : "2" } }
{ "id" : "2", "name" : "Product2", "attribute" : { "createdDate" : "2023-12-31" } }
{ "index" : { "_index" : "range_query_date_iso8601", "_id" : "3" } }
{ "id" : "3", "name" : "Product3", "attribute" : { "createdDate" : "2024-01-01" } }
{ "index" : { "_index" : "range_query_date_iso8601", "_id" : "4" } }
{ "id" : "4", "name" : "Product4", "attribute" : { "createdDate" : "2024-01-02" } }

Search Request: Range Query for Dates Between 2023-12-31 and 2024-01-01 (Works with ISO8601 Format)

POST /range_query_date_iso8601/_search
{
  "query": {
    "range": {
      "attribute.createdDate": {
        "gte": "2023-12-31",
        "lte": "2024-01-01"
      }
    }
  }
}

Search Response:

{
  "took": 36,
  "timed_out": false,
  "_shards": {
    "failed": 0,
    "skipped": 0,
    "successful": 1,
    "total": 1
  },
  "hits": {
    "total": {
      "value": 2,
      "relation": "eq"
    },
    "max_score": 1.0,
    "hits": [
      {
        "_id": "2",
        "_index": "range_query_date_iso8601",
        "_score": 1.0,
        "_source": {
          "id": "2",
          "name": "Product2",
          "attribute": {
            "createdDate": "2023-12-31"
          }
        }
      },
      {
        "_id": "3",
        "_index": "range_query_date_iso8601",
        "_score": 1.0,
        "_source": {
          "id": "3",
          "name": "Product3",
          "attribute": {
            "createdDate": "2024-01-01"
          }
        }
      }
    ]
  }
}

The key takeaway: Always use ISO 8601 format for dates in flattened fields if you need to perform range queries.

Single Point Date Range Queries

⚠️ Note: In OpenSearch versions prior to 2.19.3, a bug required specifying both parts (gte/lte or gt/lt) of a range query when querying flattened fields, otherwise a null pointer exception would occur: “failed to create query: Cannot invoke "Object.toString()" because "inputValue" is null”. This bug has been fixed in OpenSearch 2.19.3 or higher. See GitHub issue #19890 for more details.

Flattened fields support all the standard range query operators: gte (greater than or equal), lte (less than or equal), gt (greater than), and lt (less than). Let’s see one example below:

@Test
public void flattenedDateRange_ISO8601Format_GreaterThanOrEqualSinglePoint() throws Exception {
    try (OpenSearchTestIndex testIndex = fixture.createTestIndex(mapping ->
            mapping.properties("attribute", Property.of(p -> p.flatObject(f -> f))))) {

        // Use the same dates as the main range query test for consistency
        ProductWithDateAttribute[] products = new ProductWithDateAttribute[]{
                new ProductWithDateAttribute("1", "Product1", new DateAttribute("2023-12-30")),
                new ProductWithDateAttribute("2", "Product2", new DateAttribute("2023-12-31")),
                new ProductWithDateAttribute("3", "Product3", new DateAttribute("2024-01-01")),
                new ProductWithDateAttribute("4", "Product4", new DateAttribute("2024-01-02"))
        };
        testIndex.indexDocuments(products);

        // Query for dates >= 2023-12-31
        SearchResponse<ProductWithDateAttribute> result = loggingOpenSearchClient.search(s -> s
                        .index(testIndex.getName())
                        .query(q -> q
                                .range(r -> r
                                        .field("attribute.createdDate")
                                        .gte(JsonData.of("2023-12-31"))
                                )
                        ),
                ProductWithDateAttribute.class
        );

        // Should match Product2 (2023-12-31), Product3 (2024-01-01), and Product4 (2024-01-02)
        assertThat(result.hits().total().value()).isEqualTo(3);
        assertThat(result.hits().hits().stream()
                .map(h -> h.source().getId())
                .sorted())
                .containsExactly("2", "3", "4");
    }
}

This test shows that gte (greater than or equal) correctly matches documents on or after the specified date. The lte (less than or equal), gt (greater than), and lt (less than) operators work similarly, with gte and lte being inclusive (matching the boundary value) and gt and lt being exclusive (not matching the boundary value). The corresponding test methods are flattenedDateRange_ISO8601Format_LessThanOrEqualSinglePoint(), flattenedDateRange_ISO8601Format_GreaterThanSinglePoint(), and flattenedDateRange_ISO8601Format_LessThanSinglePoint().

HTTP Commands for OpenSearch Dashboards (gte Single Point)

Index Creation Request:

PUT /range_query_date_iso8601_single_point
{
  "mappings": {
    "properties": {
      "attribute": {
        "type": "flat_object"
      }
    }
  },
  "settings": {
    "number_of_replicas": 0,
    "number_of_shards": 1
  }
}

Index Documents Request:

POST /range_query_date_iso8601_single_point/_bulk
{ "index" : { "_index" : "range_query_date_iso8601_single_point", "_id" : "1" } }
{ "id" : "1", "name" : "Product1", "attribute" : { "createdDate" : "2023-12-30" } }
{ "index" : { "_index" : "range_query_date_iso8601_single_point", "_id" : "2" } }
{ "id" : "2", "name" : "Product2", "attribute" : { "createdDate" : "2023-12-31" } }
{ "index" : { "_index" : "range_query_date_iso8601_single_point", "_id" : "3" } }
{ "id" : "3", "name" : "Product3", "attribute" : { "createdDate" : "2024-01-01" } }
{ "index" : { "_index" : "range_query_date_iso8601_single_point", "_id" : "4" } }
{ "id" : "4", "name" : "Product4", "attribute" : { "createdDate" : "2024-01-02" } }

Search Request: Range Query for Dates >= 2023-12-31 (gte Single Point)

POST /range_query_date_iso8601_single_point/_search
{
  "query": {
    "range": {
      "attribute.createdDate": {
        "gte": "2023-12-31"
      }
    }
  }
}

Search Response:

{
  "took": 38,
  "timed_out": false,
  "_shards": {
    "failed": 0,
    "skipped": 0,
    "successful": 1,
    "total": 1
  },
  "hits": {
    "total": {
      "value": 3,
      "relation": "eq"
    },
    "max_score": 1.0,
    "hits": [
      {
        "_id": "2",
        "_index": "range_query_date_iso8601_single_point",
        "_score": 1.0,
        "_source": {
          "id": "2",
          "name": "Product2",
          "attribute": {
            "createdDate": "2023-12-31"
          }
        }
      },
      {
        "_id": "3",
        "_index": "range_query_date_iso8601_single_point",
        "_score": 1.0,
        "_source": {
          "id": "3",
          "name": "Product3",
          "attribute": {
            "createdDate": "2024-01-01"
          }
        }
      },
      {
        "_id": "4",
        "_index": "range_query_date_iso8601_single_point",
        "_score": 1.0,
        "_source": {
          "id": "4",
          "name": "Product4",
          "attribute": {
            "createdDate": "2024-01-02"
          }
        }
      }
    ]
  }
}

HTTP Commands for OpenSearch Dashboards (lte Single Point)

Index Creation Request:

PUT /range_query_date_iso8601_single_point_lte
{
  "mappings": {
    "properties": {
      "attribute": {
        "type": "flat_object"
      }
    }
  },
  "settings": {
    "number_of_replicas": 0,
    "number_of_shards": 1
  }
}

Index Documents Request:

POST /range_query_date_iso8601_single_point_lte/_bulk
{ "index" : { "_index" : "range_query_date_iso8601_single_point_lte", "_id" : "1" } }
{ "id" : "1", "name" : "Product1", "attribute" : { "createdDate" : "2023-12-30" } }
{ "index" : { "_index" : "range_query_date_iso8601_single_point_lte", "_id" : "2" } }
{ "id" : "2", "name" : "Product2", "attribute" : { "createdDate" : "2023-12-31" } }
{ "index" : { "_index" : "range_query_date_iso8601_single_point_lte", "_id" : "3" } }
{ "id" : "3", "name" : "Product3", "attribute" : { "createdDate" : "2024-01-01" } }
{ "index" : { "_index" : "range_query_date_iso8601_single_point_lte", "_id" : "4" } }
{ "id" : "4", "name" : "Product4", "attribute" : { "createdDate" : "2024-01-02" } }

Search Request: Range Query for Dates <= 2024-01-01 (lte Single Point)

POST /range_query_date_iso8601_single_point_lte/_search
{
  "query": {
    "range": {
      "attribute.createdDate": {
        "lte": "2024-01-01"
      }
    }
  }
}

Search Response:

{
  "took": 36,
  "timed_out": false,
  "_shards": {
    "failed": 0,
    "skipped": 0,
    "successful": 1,
    "total": 1
  },
  "hits": {
    "total": {
      "value": 3,
      "relation": "eq"
    },
    "max_score": 1.0,
    "hits": [
      {
        "_id": "1",
        "_index": "range_query_date_iso8601_single_point_lte",
        "_score": 1.0,
        "_source": {
          "id": "1",
          "name": "Product1",
          "attribute": {
            "createdDate": "2023-12-30"
          }
        }
      },
      {
        "_id": "2",
        "_index": "range_query_date_iso8601_single_point_lte",
        "_score": 1.0,
        "_source": {
          "id": "2",
          "name": "Product2",
          "attribute": {
            "createdDate": "2023-12-31"
          }
        }
      },
      {
        "_id": "3",
        "_index": "range_query_date_iso8601_single_point_lte",
        "_score": 1.0,
        "_source": {
          "id": "3",
          "name": "Product3",
          "attribute": {
            "createdDate": "2024-01-01"
          }
        }
      }
    ]
  }
}

HTTP Commands for OpenSearch Dashboards (gt Single Point)

Index Creation Request:

PUT /range_query_date_iso8601_single_point_gt
{
  "mappings": {
    "properties": {
      "attribute": {
        "type": "flat_object"
      }
    }
  },
  "settings": {
    "number_of_replicas": 0,
    "number_of_shards": 1
  }
}

Index Documents Request:

POST /range_query_date_iso8601_single_point_gt/_bulk
{ "index" : { "_index" : "range_query_date_iso8601_single_point_gt", "_id" : "1" } }
{ "id" : "1", "name" : "Product1", "attribute" : { "createdDate" : "2023-12-30" } }
{ "index" : { "_index" : "range_query_date_iso8601_single_point_gt", "_id" : "2" } }
{ "id" : "2", "name" : "Product2", "attribute" : { "createdDate" : "2023-12-31" } }
{ "index" : { "_index" : "range_query_date_iso8601_single_point_gt", "_id" : "3" } }
{ "id" : "3", "name" : "Product3", "attribute" : { "createdDate" : "2024-01-01" } }
{ "index" : { "_index" : "range_query_date_iso8601_single_point_gt", "_id" : "4" } }
{ "id" : "4", "name" : "Product4", "attribute" : { "createdDate" : "2024-01-02" } }

Search Request: Range Query for Dates > 2023-12-31 (gt Single Point)

POST /range_query_date_iso8601_single_point_gt/_search
{
  "query": {
    "range": {
      "attribute.createdDate": {
        "gt": "2023-12-31"
      }
    }
  }
}

Search Response:

{
  "took": 36,
  "timed_out": false,
  "_shards": {
    "failed": 0,
    "skipped": 0,
    "successful": 1,
    "total": 1
  },
  "hits": {
    "total": {
      "value": 2,
      "relation": "eq"
    },
    "max_score": 1.0,
    "hits": [
      {
        "_id": "3",
        "_index": "range_query_date_iso8601_single_point_gt",
        "_score": 1.0,
        "_source": {
          "id": "3",
          "name": "Product3",
          "attribute": {
            "createdDate": "2024-01-01"
          }
        }
      },
      {
        "_id": "4",
        "_index": "range_query_date_iso8601_single_point_gt",
        "_score": 1.0,
        "_source": {
          "id": "4",
          "name": "Product4",
          "attribute": {
            "createdDate": "2024-01-02"
          }
        }
      }
    ]
  }
}

HTTP Commands for OpenSearch Dashboards (lt Single Point)

Index Creation Request:

PUT /range_query_date_iso8601_single_point_lt
{
  "mappings": {
    "properties": {
      "attribute": {
        "type": "flat_object"
      }
    }
  },
  "settings": {
    "number_of_replicas": 0,
    "number_of_shards": 1
  }
}

Index Documents Request:

POST /range_query_date_iso8601_single_point_lt/_bulk
{ "index" : { "_index" : "range_query_date_iso8601_single_point_lt", "_id" : "1" } }
{ "id" : "1", "name" : "Product1", "attribute" : { "createdDate" : "2023-12-30" } }
{ "index" : { "_index" : "range_query_date_iso8601_single_point_lt", "_id" : "2" } }
{ "id" : "2", "name" : "Product2", "attribute" : { "createdDate" : "2023-12-31" } }
{ "index" : { "_index" : "range_query_date_iso8601_single_point_lt", "_id" : "3" } }
{ "id" : "3", "name" : "Product3", "attribute" : { "createdDate" : "2024-01-01" } }
{ "index" : { "_index" : "range_query_date_iso8601_single_point_lt", "_id" : "4" } }
{ "id" : "4", "name" : "Product4", "attribute" : { "createdDate" : "2024-01-02" } }

Search Request: Range Query for Dates < 2024-01-01 (lt Single Point)

POST /range_query_date_iso8601_single_point_lt/_search
{
  "query": {
    "range": {
      "attribute.createdDate": {
        "lt": "2024-01-01"
      }
    }
  }
}

Search Response:

{
  "took": 36,
  "timed_out": false,
  "_shards": {
    "failed": 0,
    "skipped": 0,
    "successful": 1,
    "total": 1
  },
  "hits": {
    "total": {
      "value": 2,
      "relation": "eq"
    },
    "max_score": 1.0,
    "hits": [
      {
        "_id": "1",
        "_index": "range_query_date_iso8601_single_point_lt",
        "_score": 1.0,
        "_source": {
          "id": "1",
          "name": "Product1",
          "attribute": {
            "createdDate": "2023-12-30"
          }
        }
      },
      {
        "_id": "2",
        "_index": "range_query_date_iso8601_single_point_lt",
        "_score": 1.0,
        "_source": {
          "id": "2",
          "name": "Product2",
          "attribute": {
            "createdDate": "2023-12-31"
          }
        }
      }
    ]
  }
}

HTTP Commands for OpenSearch Dashboards (Range with Boundaries)

Index Creation Request:

PUT /range_query_date_iso8601_boundaries
{
  "mappings": {
    "properties": {
      "attribute": {
        "type": "flat_object"
      }
    }
  },
  "settings": {
    "number_of_replicas": 0,
    "number_of_shards": 1
  }
}

Index Documents Request:

POST /range_query_date_iso8601_boundaries/_bulk
{ "index" : { "_index" : "range_query_date_iso8601_boundaries", "_id" : "1" } }
{ "id" : "1", "name" : "Product1", "attribute" : { "createdDate" : "2024-01-15" } }
{ "index" : { "_index" : "range_query_date_iso8601_boundaries", "_id" : "2" } }
{ "id" : "2", "name" : "Product2", "attribute" : { "createdDate" : "2024-01-20" } }
{ "index" : { "_index" : "range_query_date_iso8601_boundaries", "_id" : "3" } }
{ "id" : "3", "name" : "Product3", "attribute" : { "createdDate" : "2024-01-25" } }
{ "index" : { "_index" : "range_query_date_iso8601_boundaries", "_id" : "4" } }
{ "id" : "4", "name" : "Product4", "attribute" : { "createdDate" : "2024-02-01" } }

Search Request: Range Query for Dates Between 2024-01-20 and 2024-01-25 (Inclusive)

POST /range_query_date_iso8601_boundaries/_search
{
  "query": {
    "range": {
      "attribute.createdDate": {
        "gte": "2024-01-20",
        "lte": "2024-01-25"
      }
    }
  }
}

Search Response:

{
  "took": 36,
  "timed_out": false,
  "_shards": {
    "failed": 0,
    "skipped": 0,
    "successful": 1,
    "total": 1
  },
  "hits": {
    "total": {
      "value": 2,
      "relation": "eq"
    },
    "max_score": 1.0,
    "hits": [
      {
        "_id": "2",
        "_index": "range_query_date_iso8601_boundaries",
        "_score": 1.0,
        "_source": {
          "id": "2",
          "name": "Product2",
          "attribute": {
            "createdDate": "2024-01-20"
          }
        }
      },
      {
        "_id": "3",
        "_index": "range_query_date_iso8601_boundaries",
        "_score": 1.0,
        "_source": {
          "id": "3",
          "name": "Product3",
          "attribute": {
            "createdDate": "2024-01-25"
          }
        }
      }
    ]
  }
}

Sloth Summary

This post explored the unique challenges and solutions for performing date range queries on flattened fields. If you’re looking for more detailed information about flattened fields in general—including how they work, when to use them, searching patterns, and array limitations—be sure to check out our OpenSearch Flattened Field Type in Java blog post.

If you’re working with numeric range queries on flattened fields, be sure to check out our companion post on OpenSearch Flattened Field Range Queries in Java: Zero-Padded Numbers.

Let’s recap the key takeaways from this post:

Date Range Queries on Flattened Fields

• ISO 8601 format is required: Dates must be formatted in ISO 8601 format (e.g., "2024-01-15T10:30:00Z" or "2024-01-15") for range queries to work correctly on flattened fields
• Non-ISO formats fail: Formats like "01/15/2024" or "January 15, 2024" will fail range queries because lexicographic ordering doesn’t match chronological ordering
• All range operators work: gte, lte, gt, and lt all work correctly with ISO 8601 formatted dates
• Boundary values are handled correctly: Inclusive operators (gte, lte) include boundary dates, while exclusive operators (gt, lt) exclude them

Key Recommendations

1. For dates: Always use ISO 8601 format when storing dates in flattened fields if you need to perform range queries
2. Test your queries: Always test range queries with your actual data to ensure they work as expected

If you’re working with flattened fields and need date range query functionality, these patterns will ensure your queries work correctly. Remember: flattened fields store everything as keywords, so lexicographic ordering is what matters, not the native data type ordering!

Happy Code Slothing! 🦥