query sub document in mongoDB

Question

I am trying to query the company name and its admin users.

This is my structure:

companies: [
    {
        _id: "generatedComp1Id",
        name: "abc",
        users: [
          { _id: "...", user: "userId1", roles: ["admin"]},
          { _id: "...", user: "userId2", roles: ["user"]}
        ]
    },
    {
        _id: "generatedComp2Id",
        name: "xyz",
        users: [
          { _id: "...", user: "userId3", roles: ["admin"]},
          { _id: "...", user: "userId4", roles: ["admin"]},
          { _id: "...", user: "userId5", roles: ["user"]}
        ]
    }

]

I'm trying to query company name and admin users for each company given a companyId.

Output:

for company ID of generatedComp1Id:

{
    name: "abc",
    adminUsers: [
        {user: "userId1"}
    ]
}

for company ID of generatedComp2Id:

{
    name: "xyz",
    adminUsers: [
        {user: "userId3"},
        {user: "userId4"}
    ]
}

I tried to do the next thing with no success:

[  
   { $match: {_id: ObjectId("generatedComp1Id")}},
   {
        $project: {
            name: 1,
            adminUsers: {
                $filter: {
                    input: "$roles",
                    as: "role",
                    cond: {
                        $eq: ["$$role", "admin"]
                    }
                }                
            }
        }
    }
]

In addition, I tried unwind:

[
  {$unwind:'$users'}, 
  {$match:{'users.roles':{$in:['admin']}}},
  {$group:{_id:'$_id',users:{$push:'$users'}}},
  { $project: { name: 1 } }
  ]

In your $filter use the field users instead of roles. And, in the cond check if the user.roles has the specific role. — prasad_
– prasad_, Commented Jun 11, 2021 at 6:30

turivishal · Accepted Answer · 2021-06-11 06:33:44Z

2

$filter to iterate loop of users array, check condition for "admin" is in roles array
$map to iterate loop of above filtered result and return user field

[
  { $match: { _id: ObjectId("generatedComp1Id") } },
  {
    $project: {
      name: 1,
      adminUsers: {
        $map: {
          input: {
            $filter: {
              input: "$users",
              as: "role",
              cond: { $in: ["admin", "$$role.roles"] }
            }
          },
          in: { user: "$$this.user" }
        }
      }
    }
  }
]

Playground

answered Jun 11, 2021 at 6:33

turivishal

36.4k7 gold badges48 silver badges69 bronze badges

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

Collectives™ on Stack Overflow

query sub document in mongoDB

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related