The below data was inserted through Laravel eloquent, updated_at field is populated automatically
{
"_id": {
"$oid": "6516706a81f4dcd6d4e188c1"
},
"bom_data": {
"loginTime": "123",
"description": "asdf",
"submit": "1"
},
"business_key": "Check_In_Process_e13ijeb9",
"created_at": {},
"taskid": null,
"updated_at": {
"$date": "2023-09-29T06:36:26.642Z"
}
}
a simple aggregate query for hourly
db.sample_data.aggregate([
{ $match: { business_key: 'Hourlyprocess_JWIL_67hh3jj0' } },
{
$project: {
_id: 1,
loginTime: "'$bom_data.loginTime'",
description: "'$bom_data.description'",
business_key: 1,
taskid: 1,
updated_at: 1,
m: { $month: '$updated_at' },
},
},
{ $sort: { updated_at: 1 } },
]);
i got the below error
can't convert from BSON type object to Date
how to group this
Seems to work on Mongo Playground, are you sure all the document have well formatted dates?
yes definitely all data in that business key is perfect,
is there any way to identify the malformed date
db.collection.aggregate([
{
$group: {
_id: {
$type: "$updated_at"
},
total: {
$sum: 1
}
}
}
])
Thanks for the input, i have tested it , few of them are stored as object, and few of them stored as date,
how should i convert it to date type
It depends on what the actual value is in there, how many were dates and how many objects?
If you run a query something like this you can see what the documents with the malformed element look like:
db.collection.aggregate([
{
$match: {
$expr: {
$eq: [
{
$type: "$updated_at"
},
"object"
]
}
}
}
])
If you see how the wrong ones are, then you can craft an update statement to correct them, and probably hunt for why you have inconsistent data in there.
yes i already got that, so for all of them are date only, with the proper time and date format
tried it with a different approach
db.getCollection('sample_data')
.find(
{
updated_at: { $type: 'object' },
},
{
_id:1,
updated_at: 1,
//updated_on:{$toDate:'$updated_at'}
}
);
If the data type reported is object and not date, then it’s not a date. What does one of those documents look like that’s reported to be an object for that field?
AS i said earlier, it was auto update prop, there is nothing changed
[
{
"_id": {
"$oid": "651632ea8a159ebe9cd50dfb"
},
"updated_at": {
"$date": "2023-09-27T11:54:30.868Z"
}
},
{
"_id": {
"$oid": "651632ea8a159ebe9cd50dfc"
},
"updated_at": {
"$date": "2023-09-27T11:54:30.868Z"
}
},
{
"_id": {
"$oid": "651632ea8a159ebe9cd50dfd"
},
"updated_at": {
"$date": "2023-09-27T11:54:30.868Z"
}
}
]
Can you add the $type output to that query, there must be something going on if the aggregation framework is reporting something is not a date but it’s falling over on date operations when you’re trying to run, something like:
db.collection.aggregate([
{
$match: {
$expr: {
$eq: [
{
$type: "$updated_at"
},
"object"
]
}
}
},
{
$project: {
theValue: "$updated_at",
theType: {
$type: "$updated_at"
}
}
}
])
[
{
"_id": {
"$oid": "651632ea8a159ebe9cd50dfb"
},
"theValue": {
"$date": "2023-09-27T11:54:30.868Z"
},
"theType": "object"
},
{
"_id": {
"$oid": "651632ea8a159ebe9cd50dfc"
},
"theValue": {
"$date": "2023-09-27T11:54:30.868Z"
},
"theType": "object"
},
{
"_id": {
"$oid": "651632ea8a159ebe9cd50dfd"
},
"theValue": {
"$date": "2023-09-27T11:54:30.868Z"
},
"theType": "object"
},
]
That’s weird, so adding this will break it?
db.collection.aggregate([
{
$match: {
$expr: {
$eq: [
{
$type: "$updated_at"
},
"object"
]
}
}
},
{
$project: {
theValue: "$updated_at",
theType: {
$type: "$updated_at"
},
m: { $month: '$updated_at' }
}
}
])
yes, it happens is it possible to update the records of the underlying type
steevej
October 3, 2023, 4:59pm
18
Looks like the same or related date vs object as Weired Problem with Date field query returning zero results only using Atlas Data API - #9 by steevej .
We will never know for sure as the author of the thread I shared, @S_F , never came back for the followup.
8-(
The only other think I can think of is it the data was somehow stored by the ORM with an actual $date as a field name as opposed to an actual date, in which case it could look like a date when it was not.
I crafted this:
The first document has an actual date, and the second, I created using $date as a field name, if I export using mongoexport it looks like this:
I wonder if this is what happened?
1 Like
db.getCollection("Test").aggregate([
{
$match: {
$expr: {
$eq: [
{
$type: "$updated_at"
},
"object"
]
}
}
},
{
$project:{
corrected:{
$cond:{
if:{$eq:['date', {$type:'$updated_at'}]},
then:{$toString:'$updated_at'},
else:{$objectToArray:'$updated_at'}
}
}
}
},
])
What does this show? With my funky data I get this as the output:
1 Like
