MongoDB query elements that are in an array

Question

I am working in a mongoDB + node (with express and mongoose) API.

I have a document with more or less this structure:

// npcs
{
   "_id" : ObjectId("5ea6c0f88e8ecfd3cdc39eae"),
    "flavor" : {
        "gender" : "...",
        "description" : "...",
        "imageUrl" : "...",
        "class" : "...",
        "campaign" : [ 
            {
                "campaignId" : "5eac9dfe8e8ecfd3cdc41aa0",
                "unlocked" : true
            }
        ]
    },
},
// ...

And a second document in a separate table that is as follows:

// user
{
"_id" : ObjectId("5e987f8e4b88382a98c84042"),
    "username" : "KuluGary",
    "campaigns" : [ 
        "5eac9dfe8e8ecfd3cdc41aa0", 
        "5eac9e458e8ecfd3cdc41ac1", 
        "5eac9e978e8ecfd3cdc41adb", 
        "5eac9eae8e8ecfd3cdc41ae3"
    ]    
}

What I want to do is make a query in which I obtain all the NPCs that are a part of a campaign the user is part of, and are unlocked. The second part is fairly easy, just thought of once I retrieve the NPCs to filter those with unclocked false, but I'm having a hard time visualizing the query since I'm fairly unfamiliar with mongoDBs syntax and usage.

Any help would be greatly appreciated.

Tom Slabbaert · Accepted Answer · 2020-05-02 18:49:59Z

I understand you want to "join" a user with all relevant NPC's?

A simple aggregation with $lookup would work:

db.userCollection.aggregate([
    {
        $match: {
            // match relevant users with whatever condition you want
        }
    },
    {
        $lookup: {
            from: "npc_collection",
            let: {campaigns: "$campaigns"},
            pipeline: [
                {
                    $match: {
                        $expr: {
                            $gt: [
                                {
                                    $size: {
                                        $filter: {
                                            input: "$flavor.campaign",
                                            as: "campaign",
                                            cond: {
                                                $and: [
                                                    {$setIsSubset: ["$flavor.campaign.campaignId", "$$campaigns"]},
                                                    {$eq: ["$$campaign.unlocked", true]}
                                                ]
                                            }
                                        }
                                    }
                                },
                                0
                            ]
                        }
                    }
                }
            ],
            as: "relevant_npcs"
        }
    }
])

Note that due to the need of an NPC to be active in a specific campaign and not just a unlocked in any we require the use of $filter.

I recommend that if you only want to lookup on one user you split this into 2 calls as i feel using $elemMatch would give better performance:

let campaigns = await db.userCollection.distinct("campaigns", {_id: userId})
let results = await db.npcCollection.find({"flavor.campaign": {$elemMatch: { campaignId: {$in: campaigns}, unlocked: true}}})

Collectives™ on Stack Overflow

MongoDB query elements that are in an array

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related