Andrea
Andrea

Reputation: 4483

Mongo lookup rounding a date

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

Answers (1)

Valijon
Valijon

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

MongoPlayground

Note: MongoDB won't use indexes, so it will have poor performance for large collection

Upvotes: 2

Related Questions