Mongodb update with subquery

I started working with mongodb a month ago and still having problems working with complex queries but anyway, can anyone help me rewrite this SQL update query to mongoDB query? UPDATE teams SET active = 0 WHERE id = 3 AND leader_id IN (SELECT id FROM users WHERE organization = 'Accounting Department')

Below is my sample data.

Teams Collection:

{
   id: 1,
   name: "white horse",
   leader_id: "L1",
   active: 1
},
{
   id: 2,
   name: "green hornets",
   leader_id: "L2",
   active: 1
},
{
   id: 3,
   name: "pink flaminggo",
   leader_id: "L3",
   active: 1
}

Users Collection:

{
   id: "L1",
   name: "John Doe",
   organization: "Software Development",
   active: 1
},
{
   id: "L2",
   name: "Peter Piper",
   organization: "Software Development",
   active: 1
},
{
   id: "L3",
   name: "Mary Lamb",
   organization: "Accounting Department",
   active: 1
}

Hello, @Jayvee_Mendoza!

Currently, MongoDB does not support sub-queries in update operations.
So, to perform your update, you need to know the result of sub-query upfront.

Example solution:

// First, collect all the ids of users 
// from 'Accounting Department' 
db.users.aggregate([
  {
    $match: {
      organization: 'Accounting Department',
    },
  },
  {
    $group: {
      _id: null,
      usersIds: {
        $push: '$_id',
      },
    },
  },
]);
// Then, query and update your document 
// with known filter params
db.teams.updateOne({
  id: 3,
  leader_id: {
    $in: usersIds, // use value from previous operation
  },
}, {
  active: 0,
});

Hi @slava,

Thank you for your reply. I was stuck with my task trying to look for answers but now its clear to me that its not yet possible. I appreciate you giving me ideas with your example solution.

1 Like