How to populate an array inside a document which stores uniqueID as strings and that uniqueID is fro-CodePudding

I have a user document as follows which holds an array of systemIDs', which is unique for another collection which holds systemID as unikey key. How to populate user with all system ID details?

User document:

{ 
    "_id" : ObjectId("6gfg85993266db5fdgs578"), 
    "email" : "[email protected]", 
    "role" : "user", 
    "systemIDs" : [
        "12345678", 
        "87654321"
    ], 
    "createdAt" : ISODate("2022-02-13T16:31:34.119 0000"), 
    "updatedAt" : ISODate("2022-02-13T16:31:34.119 0000"), 
    "__v" : NumberInt(0)
},

{ 
    "_id" : ObjectId("6gfg85993266db5fdgs578"), 
    "email" : "[email protected]", 
    "role" : "user", 
    "systemIDs" : [
        "1111111",
        "2135684"
    ], 
    "createdAt" : ISODate("2022-02-13T16:31:34.119 0000"), 
    "updatedAt" : ISODate("2022-02-13T16:31:34.119 0000"), 
    "__v" : NumberInt(0)
}

System IDs document:


{ 
    "_id" : ObjectId("62093fdsfsdfs97e1"), 
    "systemID" : "12345678", 
    "err" : [
        1, 5, 10
    ], 
    "__v" : NumberInt(0)
},

{ 
    "_id" : ObjectId("62093fdsfsdfs97e1"), 
    "systemID" : "87654321", 
    "err" : [
        3, 7
    ], 
    "__v" : NumberInt(0)
},

{ 
    "_id" : ObjectId("62087dsfsdfs97e1"), 
    "systemID" : "11111111", 
    "err" : [
        
    ], 
    "__v" : NumberInt(0)
},

I want to find details of all the systemIDs a user holds which results something like this if I query my users collection with email : [email protected], I should get the below result or populated result like shown:

{ 
    "_id" : ObjectId("6gfg85993266db5fdgs578"), 
    "email" : "[email protected]", 
    "role" : "user", 
    "systemIDs" : [
        { 
            "_id" : ObjectId("62093fdsfsdfs97e1"), 
            "systemID" : "12345678", 
            "err" : [
                1, 5, 10
            ], 
            "__v" : NumberInt(0)
        },

        { 
            "_id" : ObjectId("62093fdsfsdfs97e1"), 
            "systemID" : "87654321", 
            "err" : [
                3, 7
            ], 
            "__v" : NumberInt(0)
        },

    ]
    "createdAt" : ISODate("2022-02-13T16:31:34.119 0000"), 
    "updatedAt" : ISODate("2022-02-13T16:31:34.119 0000"), 
    "__v" : NumberInt(0)
},

I can create a foreach loop and call database each time but I suppose that wouldn't be a good practice. I am new with this so please bear with me and explain it to me in details.

CodePudding user response：

you can use aggregation with $match and $lookup to perform this task

db.users.aggregate([
  {
    "$match": {
      "email": "[email protected]"
    }
  },
  {
    "$lookup": {
      "from": "systems",
      "localField": "systemIDs",
      "foreignField": "systemID",
      "as": "systemIDs"
    }
  }
])

demo