Reputation: 4483
I'm a beginner using Mongo
. I have to embed one or more documents into another document, where the key used for the lookup is a ISODate
that should be rounded down to the second. How can I achieve this result?
And what if I should perform the lookup on more than one keys, let's say DtEvent
and WorkstationId
?
This is a sample dataset:
temperature
collection:
{
"WorkstationId" : 1,
"DtEvent" : ISODate("2020-01-01T00:00:10.723Z"),
"ParameterName" : "temperature",
"ParameterValue" : 232.1
}
{
"WorkstationId" : 1,
"DtEvent" : ISODate("2020-01-01T00:00:56.622Z"),
"ParameterName" : "temperature",
"ParameterValue" : 231.9
}
pressure
collection:
{
"WorkstationId" : 1,
"DtEvent" : ISODate("2020-01-01T00:00:10.725Z"),
"ParameterName" : "pressure",
"ParameterValue" : 3276.69
}
{
"WorkstationId" : 1,
"DtEvent" : ISODate("2020-01-01T00:00:56.641Z"),
"ParameterName" : "pressure",
"ParameterValue" : 3276.69
}
{
"WorkstationId" : 1,
"DtEvent" : ISODate("2020-01-01T00:01:42.622Z"),
"ParameterName" : "pressure",
"ParameterValue" : 3276.69
}
I've tried to do the following:
db.pressure.aggregate([{
$lookup: {
from: "temperature",
localField: "DtEvent",
foreignField: "DtEvent",
as: "temperature"
}
}])
But, as expected, the document is not embedded since the key is not exactly matching:
/* 1 */
{
"_id" : ObjectId("5e738e151cc1692a9ace9b8c"),
"WorkstationId" : 1,
"DtEvent" : ISODate("2020-01-01T00:00:10.725Z"),
"ParameterName" : "pressure",
"ParameterValue" : 3276.69,
"temperature" : []
}
/* 2 */
{
"_id" : ObjectId("5e738e151cc1692a9ace9b8f"),
"WorkstationId" : 1,
"DtEvent" : ISODate("2020-01-01T00:00:56.641Z"),
"ParameterName" : "pressure",
"ParameterValue" : 3276.69,
"temperature" : []
}
/* 3 */
{
"_id" : ObjectId("5e738e151cc1692a9ace9b92"),
"WorkstationId" : 1,
"DtEvent" : ISODate("2020-01-01T00:01:42.622Z"),
"ParameterName" : "pressure",
"ParameterValue" : 3276.69,
"temperature" : []
}
Upvotes: 1
Views: 686
Reputation: 13103
Classic $lookup
expects exact matching. Since your Dates
are not exact values, you need run uncorrelated sub-query
db.pressure.aggregate([
{
$lookup: {
from: "temperature",
let: {
dt_event: {
$dateToString: {
date: "$DtEvent",
format: "%Y-%m-%dT%H:%M:%S"
}
}
},
pipeline: [
{
$match: {
$expr: {
$eq: [
"$$dt_event",
{
$dateToString: {
date: "$DtEvent",
format: "%Y-%m-%dT%H:%M:%S"
}
}
]
}
}
}
],
as: "temperature"
}
}
])
Note: MongoDB won't use indexes, so it will have poor performance for large collection
Upvotes: 2