How can I select an value from a array inside on another array?

Question

I'm trying to select all objects in my database which are between two dates. Problem is: Dates are inside of an array

Already tried using both Robo 3T and Studio 3T with SQL, with no sucess.

{
    "_id" : "5d9b703fe1bc4f138c5977b5",
    "Number" : 112795,
    "Finalizations" : [ 
        {
            "Value" : "89.95",
            "Portions" : [ 
                {
                    "Expiration" : ISODate("2019-11-06T02:00:00.000Z"),
                    "Value" : "89.95"
                }
            ]
        }
    ]
}

I need to return all the objects that have an "Expiration" between 11/01 and 11/25.

Answer 1

Assuming your collection is called mycollection you can query using the mongo shell...

db.mycollection.find(
{
    "Finalizations.Portions.Expiration": {"$gte": ISODate("2019-11-01")},
    "Finalizations.Portions.Expiration": {"$lt": ISODate("2019-11-25")}
}
)