To query documents - based on a property of an object which is part of a list within the document

Question

I am trying to fetch all documents which match the below criteria but the result seems incorrect.

Criteria : Movies which have release date matching the given range and this release timeline is active

isActive=true and releaseDate >= 2020-04-20 and releaseDate <= 2020-04-21

Each document is of the type Movie, which contains a list of movieTimeline objects, each movieTimeline has an isActive and releaseDate property. At any point a single movieTimeline can be active in a document.

Structure of the document -

@Document
public class Movie extends BaseDocument
{
    private String name;
    private List<MovieTimeline> movieTimelines;
...

public class MovieTimeline
{
    private Boolean isActive;
    private LocalDateTime releaseDate;
    private String description;
...

The release date property is of type LocalDateTime in Java and is stored in 2020-04-20T05:00:00.000+00:00 format in DB.

This is the code I have tried to retrieve the data-

@Repository
public interface MovieRepository extends MongoRepository<Movie,String>
{
    List<Movie> findByMovieTimelines_ReleaseDateBetweenAndMovieTimelines_IsActive(LocalDateTime from, LocalDateTime to, boolean isActive);
}

Sample data -

{
"id": "abc",
"name": "Rango",
"movieTimelines": [{
        "isActive": false,
        "releaseDate": "2020-02-10T11:30:00",
        "description": "ORIGINAL TIMELINE"
    }, {
        "isActive": true,
        "releaseDate": "2020-06-15T10:30:00",
        "description": "New Timeline created on 03/10/2020"
    }
]
}, {
    "id": "abcv",
    "name": "Fight Club",
    "movieTimelines": [{
            "isActive": false,
            "releaseDate": "2020-02-10T11:30:00",
            "description": "ORIGINAL TIMELINE"
        }, {
            "isActive": true,
            "releaseDate": "2020-05-18T10:30:00",
            "description": "New Timeline created on 02/05/2020"
        }
    ]
}, {
    "id": "asd",
    "name": "Death Note",
    "movieTimelines": [{
            "isActive": false,
            "releaseDate": "2020-03-09T10:30:00",
            "description": "ORIGINAL TIMELINE"
        }, {
            "isActive": false,
            "releaseDate": "2020-03-16T10:30:00",
            "description": "New Timeline created on 02/05/2020"
        }, {
            "isActive": false,
            "releaseDate": "2020-04-20T10:30:00",
            "description": "New Timeline created on 03/06/2020"
        }, {
            "isActive": true,
            "releaseDate": "2020-04-20T10:30:00",
            "description": "New Timeline created on 03/06/2020"
        }
    ]
}, {
    "id": "gfj",
    "name": "Andhadhun",
    "movieTimelines": [{
            "isActive": false,
            "releaseDate": "2020-04-13T10:30:00",
            "description": "ORIGINAL TIMELINE"
        }, {
            "isActive": true,
            "releaseDate": "2020-07-20T10:30:00",
            "description": "New Timeline created on 03/09/2020"
        }
    ]
}, {
    "id": "nojh",
    "name": "Evan Almighty",
    "movieTimelines": [{
            "isActive": true,
            "releaseDate": "2020-04-20T10:30:00",
            "description": "ORIGINAL TIMELINE"
        }
    ]
}

Expected-

{
    "id": "asd",
    "name": "Death Note",
    "movieTimelines": [{
            "isActive": false,
            "releaseDate": "2020-03-09T10:30:00",
            "description": "ORIGINAL TIMELINE"
        }, {
            "isActive": false,
            "releaseDate": "2020-03-16T10:30:00",
            "description": "New Timeline created on 02/05/2020"
        }, {
            "isActive": false,
            "releaseDate": "2020-04-20T10:30:00",
            "description": "New Timeline created on 03/06/2020"
        }, {
            "isActive": true,
            "releaseDate": "2020-04-20T10:30:00",
            "description": "New Timeline created on 03/06/2020"
        }
    ]
},{
    "id": "nojh",
    "name": "Evan Almighty",
    "movieTimelines": [{
            "isActive": true,
            "releaseDate": "2020-04-20T10:30:00",
            "description": "ORIGINAL TIMELINE"
        }
    ]
}

Does MongoDB have any filters or operators which can be used to fetch these documents in a single query?

Nihal Rao Nihal Rao · Accepted Answer · 2020-04-06T12:47:39

MongoDB provides $elemMatch projection operator that should be able to satisfy your conditions.

Particularly, your document is made up of an array of documents. So here is another reference you can use to construct queries for such type of data.

Here is what I found when I tested with your condition and sample data. My query is in Mongo Shell and not Java/Spring, so please rewrite it in the required language.

Since you want to fetch only those documents that have active movie timelines, "isActive" attribute in "movieTimelines" must be true. This condition alone can be satisfied using the below query:

db.collectionName.find({ "movieTimelines": { $elemMatch: { "isActive": true } } })

The release date also needs to be in the given range which means that the query needs to address "releaseDate" attribute in "movieTimelines". This condition can be queried this way:

db.collectionName.find({ "movieTimelines": { $elemMatch: { "releaseDate": { $gte: ISODate("2020-04-20T00:00:00"), $lte: ISODate("2020-04-21T00:00:00") } } } })

or

db.collectionName.find({ "movieTimelines": { $elemMatch: { "releaseDate": { $gte: ISODate("2020-04-20"), $lte: ISODate("2020-04-21") } } } })

For the above query, the "releaseDate" values are "ISODate()" objects instead of strings in my database. Strings can be converted to ISODate objects using using aggregators such as toDate, dateFromString, etc.

Even if "releaseDate" value is a string, the query will work if the datatype in the condition is changed to string. It just may not be good practice.

db.collectionName.find({ "movieTimelines": { $elemMatch: { "releaseDate": { $gte: "2020-04-20", $lte: "2020-04-21" } } } })

So to satisfy both your conditions, the two queries need to be combined into one like this:

db.collectionName.find({ "movieTimelines": { $elemMatch: { "isActive": true, "releaseDate": { $gte: ISODate("2020-04-20T00:00:00"), $lte: ISODate("2020-04-21T00:00:00") } } } })

I hope this answers your question.

To query documents - based on a property of an object which is part of a list within the document

1 Answers