Mongodb Query on array in date string fields

Question

I have document in mongodb
and I would like to have only candles where time from 2010-01-02 to 2010-01-05, but I'm not idea like to do this: Thanks

{
    "_id" : ObjectId("5f25916e771c501cf1c040be"),
    "instrument" : "AUD_USD",
    "granularity" : "D",
    "candles" : [ 
        {
            "complete" : true,
            "volume" : 5,
            "time" : "2009-12-31T22:00:00.000000000Z",
            "ask" : {
                "o" : "0.89815",
                "h" : "0.89850",
                "l" : "0.89800",
                "c" : "0.89825"
            }
        }, 
        {
            "complete" : true,
            "volume" : 330,
            "time" : "2010-01-02T22:00:00.000000000Z",
            "ask" : {
                "o" : "0.89820",
                "h" : "0.89944",
                "l" : "0.89725",
                "c" : "0.89881"
            }
        }, 
        {
            "complete" : true,
            "volume" : 15449,
            "time" : "2010-01-03T22:00:00.000000000Z",
            "ask" : {
                "o" : "0.89885",
                "h" : "0.91376",
                "l" : "0.89398",
                "c" : "0.91278"
            }
        }, 
        {
            "complete" : true,
            "volume" : 17119,
            "time" : "2010-01-04T22:00:00.000000000Z",
            "ask" : {
                "o" : "0.91282",
                "h" : "0.91751",
                "l" : "0.90936",
                "c" : "0.91196"
            }
        }, 
        {
            "complete" : true,
            "volume" : 16740,
            "time" : "2010-01-05T22:00:00.000000000Z",
            "ask" : {
                "o" : "0.91188",
                "h" : "0.92180",
                "l" : "0.90998",
                "c" : "0.91984"
            }
        }, 
        {
            "complete" : true,
            "volume" : 18321,
            "time" : "2010-01-06T22:00:00.000000000Z",
            "ask" : {
                "o" : "0.91972",
                "h" : "0.92676",
                "l" : "0.91605",
                "c" : "0.91753"
            }
        }, 
        {
            "complete" : true,
            "volume" : 18262,
            "time" : "2010-01-07T22:00:00.000000000Z",
            "ask" : {
                "o" : "0.91765",
                "h" : "0.92582",
                "l" : "0.91243",
                "c" : "0.92521"
            }
        }, 
        {
            "complete" : true,
            "volume" : 1,
            "time" : "2010-01-08T22:00:00.000000000Z",
            "ask" : {
                "o" : "0.92521",
                "h" : "0.92521",
                "l" : "0.92521",
                "c" : "0.92521"
            }
        }
    ]
}

Answer 1

Can you please confirm that the candles.time value is stored as a date and not as a string? I had to edit your sample data and wrap each date with ISODate to get this to work.

I think your asking how to filter down the candles array field to only the candles between two dates. You will have to use $filter like this:

https://mongoplayground.net/p/i9DvUTmtY2R

db.collection.aggregate({
  $addFields: {
    candles: {
      $filter: {
        input: "$candles",
        as: "candle",
        cond: {
          $and: [
            {$gte: ["$$candle.time", {$toDate: "2010-01-02"}]},
            {$lt: ["$$candle.time", {$toDate: "2010-01-05"}]}
          ]
        }
      }
    }
  }
});

Here's an example leaving your sample data as is, and attempting to convert it to a date on filter. As you will see it fails due to an extra 0 in the time string. https://mongoplayground.net/p/onMZu8Brj9V

To use updateMany to convert your ```time`` field from string to date do the following in shell.

db.COLLECTION.updateMany(
  {},
  [{$set: {
    'candles': {$map: {
      input: '$candles',
      as: 'candle',
      in: {$mergeObjects: [
        '$$candle',
        {
          time: {$toDate: {$concat: [
            // Trim trim the extra 0
            {$substrCP: ['$$candle.time', 0, 28]},
            'Z'
          ]}
        }}
      ]}

    }}
  }}]
)

I used $map since it doesn't appear you can access the current value of the array item array.$.field method. Be sure to update your schema so new values do not continue to be saved as strings. Once converted the original answer should work.

Answer 2

from_date = ISODate("2010-01-02T00:00:00Z"); day begin
to_date   = ISODate("2010-01-05T23:59:59Z"); day end

$time: {"$gte": from_date, "$lt": to_date}

A full query to return all items between those 2 dates would look like this:

db.your_collection.find({"time":{ "$gte": from_date, "$lt": to_date) }});