Filter condition for custom _id in MongoDB

Question

I am creating a custom id in mongo whose structure is (industry-id)~(start-date)~(end-date). In my request I have (industry id) and (request date). I want all the records from mongo where (industry id) matches and the request date is between (start-date) & (end-date), using springboot

sample id : 1~2024-01-01~2024-02-01

request : industry id - 1 request-date - 2024-01-02

the above record should be picked up

my work

import com.mongodb.client.MongoClients;
import com.mongodb.client.MongoClient;
import com.mongodb.client.MongoCollection;
import com.mongodb.client.MongoDatabase;
import org.bson.Document;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;

public class MongoQueryExample {

    public static void main(String[] args) {
        String industryId = "your-industry-id"; // replace with your industry id
        String requestDateStr = "2024-01-16"; // replace with your request date

        // Parse request date to Date object
        Date requestDate = parseDate(requestDateStr);

        // Create MongoDB client
        try (MongoClient mongoClient = MongoClients.create("your-mongodb-connection-string")) {
            // Access the database
            MongoDatabase database = mongoClient.getDatabase("your-database-name");
           
            // Access the collection
            MongoCollection<Document> collection = database.getCollection("your-collection-name");

            // Construct the query
            Document query = new Document("industry-id", industryId)
                    .append("start-date", new Document("$lte", requestDate))
                    .append("end-date", new Document("$gte", requestDate));

            // Execute the query
            for (Document document : collection.find(query)) {
                System.out.println(document.toJson());
            }
        }
    }

    private static Date parseDate(String dateStr) {
        try {
            return new SimpleDateFormat("yyyy-MM-dd").parse(dateStr);
        } catch (ParseException e) {
            e.printStackTrace();
            return null;
        }
    }
}

Answer 1

Option 1

Custom id looks odd, there are better ways doing that. I suggest to change that to 3 separate fields.

1. Update documents so you custom id is looking like that:

   {
      "_id": {
         "industry-id": "id1",
         "start-date": {"$date":"2024-01-01T00:00:00Z"},
         "end-date": {"$date":"2024-02-01T00:00:00Z"}
      },
      ...
   }
   

2. The correct query for that custom id will be:

   Document query = new Document("_id.industry-id", industryId)
      .append("_id.start-date", new Document("$lte", requestDate))
      .append("_id.end-date", new Document("$gte", requestDate));
   

Just replace your Document query = ... with provided example. Also I used String industryId = "id1"; for this example.

Option 2

You want to keep that custom id of yours! That's ok, is up to you.
Here is how you can do it with aggregate and filter inside it:

collection.aggregate(Arrays.asList(
        new Document("$addFields", new Document()
                .append("industry-id", new Document("$arrayElemAt", Arrays.asList(new Document("$split", Arrays.asList("$_id", "~")), 0)))
                .append("start-date", new Document("$dateFromString", new Document("dateString", new Document("$arrayElemAt", Arrays.asList(new Document("$split", Arrays.asList("$_id", "~")), 1)))))
                .append("end-date", new Document("$dateFromString", new Document("dateString", new Document("$arrayElemAt", Arrays.asList(new Document("$split", Arrays.asList("$_id", "~")), 2)))))
        ),
        new Document("$match", new Document()
                .append("industry-id", industryId)
                .append("start-date", new Document("$lte", requestDate))
                .append("end-date", new Document("$gte", requestDate))
        )
)).forEach(document -> System.out.println(document.toJson()));

MongoDB for reference:

{
   "_id": "1~2024-01-01~2024-02-01",
   ...
}