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"
            }
        }
    ]
}

GitGitBoom · Accepted Answer · 2020-08-06 20:38:08Z

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.

edited Aug 6, 2020 at 20:38

answered Aug 6, 2020 at 19:00

GitGitBoom

1,9226 silver badges5 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Gianluca Mazzeo Over a year ago

I confirm to you that candles.time value is stored as a string, if I execute mongoplayground.net/p/onMZu8Brj9V return an error

GitGitBoom Over a year ago

Can that be fixed or is there a reason it's stored as a string? It is possible to convert it to a date during aggregation but since it fails conversion as is it will have to be modified.

GitGitBoom Over a year ago

Here's a working example converting the strings using replace to a value that can be converted by $toDate. I would be happy to help you write an updateMany() call to convert the fields to proper dates because this feels pretty hacky. mongoplayground.net/p/5zAI5J6HQok

Gianluca Mazzeo Over a year ago

works fine on mongoplayground but if I try in mongo shell return : Error: ReferenceError: $replaceOne is not defined , I don't know why...

Gianluca Mazzeo Over a year ago

GitGitBoom How can I convert it to a date with updateMany() method ?

|

Jacob Kaniuk · Accepted Answer · 2020-08-06 18:38:50Z

0

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) }});

edited Aug 6, 2020 at 18:38

answered Aug 6, 2020 at 18:29

Jacob Kaniuk

1756 bronze badges

Collectives™ on Stack Overflow

Mongodb Query on array in date string fields

2 Answers 2

8 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related