Converting dates to $ project in MongoDB

Question

I get an error when trying to change the date field in my collection.

This is a schema, and I would like to change the area sentthat contains the dates:

> db.complaints.findOne()
{
    "_id" : ObjectId("55e5990d991312e2c9b266e3"),
    "complaintID" : 1388734,
    "product" : "mortgage",
    "subProduct" : "conventional adjustable mortgage (arm)",
    "issue" : "loan servicing, payments, escrow account",
    "subIssue" : "",
    "state" : "va",
    "ZIP" : 22204,
    "submitted" : "web",
    "received" : "2015-05-22",
    "sent" : "2015-05-22",
    "company" : "green tree servicing, llc",
    "response" : "closed with explanation",
    "timely" : "yes",
    "disputed" : ""
}

This is the request and error message I receive:

db.complaints.aggregate([
{$project:
    {_id:0, 
    sent: 1, 
    Year:{$year:"$sent"},
    Month: {$month:"$sent"}, 
    Day: {$dayOfMonth: "$sent"}, 
    product : 1}},

])
> db.complaints.aggregate([
... {$project:
... {_id:0,
... sent: 1,
... Year:{$year:"$sent"},
... Month: {$month:"$sent"},
... Day: {$dayOfMonth: "$sent"},
... product : 1}},
...
... ])
assert: command failed: {
    "errmsg" : "exception: can't convert from BSON type String to Date",
    "code" : 16006,
    "ok" : 0
} : aggregate failed
Error: command failed: {
    "errmsg" : "exception: can't convert from BSON type String to Date",
    "code" : 16006,
    "ok" : 0
} : aggregate failed
at Error (<anonymous>)
at doassert (src/mongo/shell/assert.js:11:14)
at Function.assert.commandWorked (src/mongo/shell/assert.js:254:5)
at DBCollection.aggregate (src/mongo/shell/collection.js:1278:12)
at (shell):1:15
2015-09-12T13:16:22.876+0100 E QUERY    Error: command failed: {
    "errmsg" : "exception: can't convert from BSON type String to Date",
    "code" : 16006,
    "ok" : 0
} : aggregate failed
at Error (<anonymous>)
at doassert (src/mongo/shell/assert.js:11:14)
at Function.assert.commandWorked (src/mongo/shell/assert.js:254:5)
at DBCollection.aggregate (src/mongo/shell/collection.js:1278:12)
at (shell):1:15 at src/mongo/shell/assert.js:13
> db.complaints.aggregate([
... {$project:
... {_id:0,
... sent: 1,
... Year:{$year:"$sent"},
... Month: {$month:"$sent"},
... Day: {$dayOfMonth: "$sent"},
... product : 1}},
...
... ])
assert: command failed: {
    "errmsg" : "exception: can't convert from BSON type String to Date",
    "code" : 16006,
    "ok" : 0
} : aggregate failed
Error: command failed: {
    "errmsg" : "exception: can't convert from BSON type String to Date",
    "code" : 16006,
    "ok" : 0
} : aggregate failed
at Error (<anonymous>)
at doassert (src/mongo/shell/assert.js:11:14)
at Function.assert.commandWorked (src/mongo/shell/assert.js:254:5)
at DBCollection.aggregate (src/mongo/shell/collection.js:1278:12)
at (shell):1:15
2015-09-12T13:21:08.767+0100 E QUERY    Error: command failed: {
    "errmsg" : "exception: can't convert from BSON type String to Date",
    "code" : 16006,
    "ok" : 0
} : aggregate failed
at Error (<anonymous>)
at doassert (src/mongo/shell/assert.js:11:14)
at Function.assert.commandWorked (src/mongo/shell/assert.js:254:5)
at DBCollection.aggregate (src/mongo/shell/collection.js:1278:12)
at (shell):1:15 at src/mongo/shell/assert.js:13
>

Answer 1

You can only use date operators, such as $yearwith values ​​that are of the BSON Date data type. The field value sentis a string, so you will need to use $substrto split the date string by position:

db.complaints.aggregate([
{$project:
    {_id: 0, 
    sent: 1, 
    Year: {$substr: ["$sent", 0, 4]},
    Month: {$substr: ["$sent", 5, 2]}, 
    Day: {$substr: ["$sent", 8, 2]}, 
    product: 1}}
])

Result:

{
"product" : "mortgage",
"sent" : "2015-05-22",
"Year" : "2015",
"Month" : "05",
"Day" : "22"
}