Create a new array of objects in a aggregate query in Mongodb

Question

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.

Pipe · Accepted Answer · 2022-10-27 21:54:40Z

1

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

answered Oct 27, 2022 at 21:54

Pipe

2,4342 gold badges20 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tobias S. · Accepted Answer · 2022-10-27 21:55:07Z

1

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

answered Oct 27, 2022 at 21:55

Tobias S.

24.3k7 gold badges37 silver badges64 bronze badges

Collectives™ on Stack Overflow

Create a new array of objects in a aggregate query in Mongodb

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related