AWS DocumentDB与Java：日期范围查询无结果问题排查

首次接触MongoDB，用Java查询AWS DocumentDB集合时，能正常获取全量数据总数，但按日期范围过滤的查询始终返回0条结果。尝试了多种过滤写法（包括BasicDBObject和Document构建条件），传入起始日期、结束日期或两者组合均无效。

观察到DataGrid显示的日期格式为2023-10-01，而非带时间戳的2023-10-01 05:00:00，怀疑是日期格式/类型不匹配导致的问题。

以下是相关代码：

package com.example.mongodb.service;

import com.mongodb.BasicDBObject;
import com.mongodb.client.MongoClient;
import com.mongodb.client.MongoClients;
import com.mongodb.client.MongoCursor;
import com.mongodb.client.MongoDatabase;
import jakarta.annotation.PostConstruct;
import org.bson.Document;
import org.bson.conversions.Bson;
import org.springframework.stereotype.Service;

import java.util.Date;

/**
 * Do Things with DocumentDB.
 */
public class DocDBService {
    private String collectionName = "myCollection";

    private MongoClient mongoClient;
    private MongoDatabase database;

    private String username;
    private String password;
    private String server;
    private String dbName;

    /**
     * Construct with our username, password, and server.
     */
    public DocDBService(String usernameIn, String passwordIn, String serverIn, String dbNameIn) {
        username = usernameIn;
        password = passwordIn;
        server = serverIn;
        dbName = dbNameIn;

        String template = "mongodb://%s:%s@%s";
        String connectionString = String.format(template, username, password, server);
        mongoClient = MongoClients.create(connectionString);
        database = mongoClient.getDatabase(dbName);
    }

    /** This is working. */
    public long getLength() {
        BasicDBObject obj;

        return database.getCollection(collectionName).countDocuments();
    }

    /* I've tried a few things. None are working. */
    public long getLength(Date earliest, Date latest) {
        /*
        BasicDBObject query = new BasicDBObject();
        if (earliest != null) {
            query.put("date", new BasicDBObject("$gt", earliest));
        }
        if (latest != null) {
            query.put("date", new BasicDBObject("$lt", latest));
        }

        System.out.println(query.toJson());

        MongoCursor<?> cursor = database.getCollection(collectionName).find(query).iterator();
        long rv = 0;
        while (cursor.hasNext()) {
            ++rv;
            cursor.next();
        }
         */
        long rv = 0;
        Document filter = new Document();
        if (earliest != null) {
            filter.append("$gte", earliest);
        }
        if (latest != null) {
            filter.append("$lte", latest);
        }
        System.out.println(filter.toJson());
        rv = database.getCollection(collectionName).countDocuments(new Document("date", filter));
        return rv;
    }
}

当传入两个日期时，打印的过滤条件为：

{"$gte": {"$date": "2023-10-01T05:00:00Z"}, "$lte": {"$date": "2023-11-02T05:00:00Z"}}

1. 先确认字段实际类型 #

DataGrid显示的2023-10-01大概率是格式化后的结果，要先明确集合里date字段的真实类型：是BSON Date类型，还是字符串类型？

可以加一段代码验证：

// 取一条样本数据查看date字段类型
Document sample = database.getCollection(collectionName).find().first();
if (sample != null) {
    Object dateVal = sample.get("date");
    System.out.println("date字段类型：" + dateVal.getClass().getName());
    System.out.println("date字段原始值：" + dateVal);
}

2. 针对不同类型的修复 #

情况A：字段是字符串类型（如"2023-10-01"） #

如果字段存的是字符串，用Java的Date对象查询会因为类型不匹配返回空结果。需要把查询的日期转为和存储格式一致的字符串（YYYY-MM-DD）再做范围匹配：

import java.text.SimpleDateFormat;

public long getLength(Date earliest, Date latest) {
    SimpleDateFormat dateFormatter = new SimpleDateFormat("yyyy-MM-dd");
    Document dateFilter = new Document();

    if (earliest != null) {
        dateFilter.append("$gte", dateFormatter.format(earliest));
    }
    if (latest != null) {
        dateFilter.append("$lte", dateFormatter.format(latest));
    }

    // 无过滤条件时返回全量
    if (dateFilter.isEmpty()) {
        return getLength();
    }

    return database.getCollection(collectionName).countDocuments(new Document("date", dateFilter));
}

情况B：字段是BSON Date类型 #

如果字段是Date类型，之前的代码逻辑没问题，但旧代码里的BasicDBObject写法有条件覆盖问题：两次query.put("date", ...)会把前一个条件覆盖，导致最终只有一个范围条件。现在用Document的写法是对的，但可以优化一下空判断：

public long getLength(Date earliest, Date latest) {
    Document dateFilter = new Document();

    if (earliest != null) {
        dateFilter.append("$gte", earliest);
    }
    if (latest != null) {
        dateFilter.append("$lte", latest);
    }

    if (dateFilter.isEmpty()) {
        return getLength();
    }

    return database.getCollection(collectionName).countDocuments(new Document("date", dateFilter));
}

内容的提问来源于stack exchange，提问作者Joseph Larson