CODEWITHSUNDEEP
javascriptmongodbmongoosemongodb-query
Eric Gruby
Eric Gruby

Reputation: 389

Mongoose query array inside array of objects

So I'm using node v10, mongodb v4.2 and mongoose v5.9.

I have a UsersSchema:

const UsersSchema = new Schema({
    name: {
        type: String,
        required: true,
    },
    email: {
        type: String,
        required: true,
        lowercase: true
    },
 ...

A CompaniesSchema:

const CompaniesSchema = new Schema({
    name: {
        type: String,
        required: true,
    },
    user: {
        type: mongoose.Schema.Types.ObjectId,
        ref: 'Users',
        required: true
    },
    branches: [{
        type: mongoose.Schema.Types.ObjectId,
        ref: 'Branches'
    }]
    ...

})

And a BranchesSchema:

const BranchesSchema = new Schema({
    name: {
        type: String,
        required: true,
    },
    company: {
        type: mongoose.Schema.Types.ObjectId,
        ref: 'Companies',
        required: true
    }
    users: [{
        type: mongoose.Schema.Types.ObjectId,
        ref: 'Users',
    }]
})

I need to query all the companies the user own, OR the companies he's been added to a branch. I tried to do like:

const id = 'MY_USER_ID'
const queryCompanies = await Companies.find({
  $or: [{ user: id }, { "branches.users": [id] }],
});

The { user: id } part works. I'm having trouble trying to query the companies he's in based on the branches he's in. Is that even possible?

Upvotes: 1

Views: 55

Answers (1)

Tom Slabbaert
Tom Slabbaert

Reputation: 22296

With the current schema setup you have 2 options:

  1. Split into 2 calls:
const id = 'MY_USER_ID';
let companyIds = await Branches.distinct("company", {users: id});
const queryCompanies = await Companies.find({
    $or: [{ user: id }, { _id: {$in: companyIds} }],
});
  1. Use $lookup
const id = 'MY_USER_ID';
const queryCompanies = await Companies.aggregate([
    {
        $lookup: {
            from: "branches",
            let: {companyId: "$_id", userId: id},
            pipeline: [
                {
                    $match: {
                        $expr: {
                            $and: [
                                {
                                    $eq: ["$$companyId", "$company"]
                                },
                                {
                                    $setIsSubset: [["$$userId"], "$users"]
                                }
                            ]
                        }
                    }      
                }
            ],
            as: "branches"
        }
    },
    {
        $match: {
            $or: [
                {
                    user: id
                },
                {
                    "branches.0": {$exists: true}
                }
            ]
        }
    }
]);

I personally recommend option 1 as Mongo does not like lookuping, especially here where you have to do so on the entire collection.

Upvotes: 1

Related Questions