I'm running a query on Mongodb to get the combine data from two different collections: User and Store.

User collection has a property named as store_ids, which is an array that contains a list of ObjectIds of each store that the User has access to.

I'm trying to add the name of each store in the query result.

Example:

User Document:

{
    _id: '58ebf8f24d52e9ab59b5538b',
    store_ids: [
        ObjectId("58dd4bb10e2898b0057be648"), 
        ObjectId("58ecd57d1a2f48e408ea2a30"),
        ObjectId("58e7a0766de7403f5118afea"),
    ]
}

Store Documents:

{
    _id: "58dd4bb10e2898b0057be648",
    name: "Store A",
},
{
    _id: "58ecd57d1a2f48e408ea2a30",
    name: "Store B",
},
{
    _id: "58e7a0766de7403f5118afea",
    name: "Store C"
}

I'm looking for a query that returns an output like this:

{
    _id: '58ebf8f24d52e9ab59b5538b',
    stores: [
        {
            _id: ObjectId("58dd4bb10e2898b0057be648"),
            name: "Store A"
        },
        {
            id: ObjectId("58ecd57d1a2f48e408ea2a30"),
            name: "Store B"
        },
        {
            _id: ObjectId("58e7a0766de7403f5118afea"),
            name: "Store C"
        }
    ]
}

I've already tried operations like $map and $set. I don't know if I'm applying them in the right way because they didn't work for my case.

CodePudding user response：

You can use an aggregate query:

db.users.aggregate([
  {
    $lookup: {
      from: "stores", //Your store collection
      localField: "store_ids",
      foreignField: "_id",
      as: "stores"
    }
  },
  {
    $project: {
      store_ids: 0
    }
  }
])

You can see a working example here: https://mongoplayground.net/p/ICsEEsmRcg0

CodePudding user response：

We can achieve this with a simple $lookup and with $project.

db.user.aggregate({
  "$lookup": {
    "from": "store",
    "localField": "store_ids",
    "foreignField": "_id",
    "as": "stores"
  }
},
{
  "$project": {
    store_ids: 0
  }
})

• $lookup will join with store table on with the store_ids array where the _id matches

• $project removes the store_ids array from the resulting objects

Playground