Want to join two collections and result should be filter from both collections

Question

  {
    "_id" : ObjectId("5e8dae610b209222e10a0ac4"),
    "serial" : "2248",
    "externalId" : "452085",
     "hierarchyPathByIds" : ",5e2eb1662698a2097c71d855,",
    "status" : "ACTIVE",
 }
device_events collection {
  {
    "_id" : ObjectId("5e9d863a235721333eddfb81"),
    "deviceId" : "5e8dae610b209222e10a0ac4",
    "type" : "PACKAGE_TEST",
     "createdAt" : ISODate("2020-04-20T11:04:27.943Z")
}

Note -> Have to fetch documents which have same device id in both collections if there is no any events with device collection, that would not be come as result. Device Events should filter for last hour and type as well. 1. Have to fetch all devices which is in device events created date descending order. 2. hierarchyPathByIds regex pattern match to '.,5e2eb1662698a2097c71d855,.'

Device has many device events.

I have used Aggregation(lookup) thing but it also not satisfy my requirement....... { "serial" : "5e8ec16aaa0baa1ed8e5f22e", "_id" : "5e8ec16aaa0baa1ed8e5f22e", "deviceEvents" : [ { "_id" : ObjectId("5ea295598463e006c7a23633"), "deviceId" : "5e8ec16aaa0baa1ed8e5f22e", "type" : "PACKAGE_TEST", "createdAt" : ISODate("2020-04-24T07:29:29.733Z") }] } — kasun De Silva, Apr 27 '20 at 03:03
[menna uththare](https://gist.github.com/dj-nitehawk/4f9a9bc567ddbc934e0811f802266628) — Dĵ ΝιΓΞΗΛψΚ, Apr 27 '20 at 06:44

varman · Accepted Answer · 2020-04-27T03:21:27.713

0

Since you store deviceId as String in device collection, you have to convert

Option 1 convert _id ObjectId to String of device collection

db.device.aggregate([
    {
        $project: {
            "_id": {
                "$toString": "$_id"
            },
            serial: 1,
            externalId: 1,
            status: 1

        }
    }, {
        $lookup: {
            from: 'device_events',
            localField: '_id',
            foreignField: 'deviceId',
            as: 'join'
        }
    }
])

Option 2 convert deviceId String to ObjectId of device_events collection

db.device_events.aggerate([
    {
        $project: {
            "deviceObjId": {
                "$toObjectId": "$deviceId"
            },
            _id: 0,
            type: 1
        }
    }, {
        $lookup: {
            from: 'device',
            localField: 'deviceObjId',
            foreignField: '_id',
            as: 'join'
        }
    }
])

edited Apr 27 '20 at 03:21

answered Apr 27 '20 at 03:11

varman

8,704
5
19
53

Thanks for the response, So I want to emit empty array join as well. { "serial" : "2259", "_id" : "5e8f5028414a60740b9cee92", "join" : [] } , How should I do that? – kasun De Silva Apr 27 '20 at 03:46
just use {$unwind: "$join","preserveNullAndEmptyArrays": true} after the lookup – varman Apr 27 '20 at 04:02
Yeah that also fine. And need to match regex pattern with device collection field, How should I do that? – kasun De Silva Apr 27 '20 at 04:16
When you ask a question, please clearly mention everything, most of the developers delete thire codes after responding. – varman Apr 27 '20 at 04:29
Refer https://stackoverflow.com/questions/16252208/how-to-use-regex-in-mongodb-aggregation-query-within-match – varman Apr 27 '20 at 04:31
db.getCollection('devices').aggregate([ { "$project": { "_id": { "$toString": "$_id" }, "hierarchyPathByIds" :{$regex: ".*,5e2eb1662698a2097c71d855,.*,", $options: 'g'} } }}] It was not worked for me. – kasun De Silva Apr 27 '20 at 04:43
Please explain clearly – varman Apr 27 '20 at 05:08
I have modify the my question now. Can You please read it and give me solution – kasun De Silva Apr 27 '20 at 05:13
{$match: { hierarchyPathByIds: { $regex: ",5e2eb1662698a2097c71d855," } } }, { $project: { "_id": { "$toString": "$_id" }, serial: 1, externalId: 1, status: 1, hierarchyPathByIds: 1 } }, { $lookup: { from: 'device_events', localField: '_id', foreignField: 'deviceId', as: 'join' } },{ $unwind: { path: '$join', preserveNullAndEmptyArrays: true } },{ $sort: { "join.createdAt": 1 } } – varman Apr 27 '20 at 05:34
This question is closed. Please create a new question. – varman Apr 27 '20 at 05:34

Want to join two collections and result should be filter from both collections

1 Answers1