Mongodb find document in collection from field in another collection-CodePudding

I have two collections: Sharing and Material.

Sharing:

{
 from_id: 2
 to_id: 1
 material_id: material1
}

Material:

{
 _id: material1
 organization_id: 2
},
{
 _id: material2
 organization_id: 1
},
{
 _id: material3
 organization_id: 1
},

--Edit: There are three materials, 2 belong to organization_id(1) and 1 belongs to organization_id(2). The organization_id does not match 1 in material1 (and instead belongs to material2), but in the Sharing collection, the to_id does match 1. If the match exists, I'd like to find the Material document _id which is equal to the material_id of Sharing AND find the Material documents where the organization_id is equal to 1.

I'd like to check if a field in Sharing (to_id) has a value that is equal to a field in Material (organization_id) AND check if organization_id is equal to 1. If there is a document that exists from this, do another check to find whether the _id of Material is equal to the material_id of Sharing and return all documents & the total count.

If there is no equal value, I'd like to omit that result and send the object with only organization_id equal to 1 and get the total count of this result.

Right now, I do it in a very inefficient way using .map() to find this. Below is my code:

export const getMaterials = async (req, res) => {
    const sharing = await Sharing.find({to_id: 1});
    let doneLoad;

    try {
        if (sharing && sharing.length>0) {
            const sharingTotal = await Material.find( {$or: [ {organization_id: 1}, {_id: sharing.map((item) => item.material_id)} ] } ).countDocuments();
            const sharingMats = await Material.find( {$or: [ {organization_id: 1}, {_id: sharing.map((item) => item.material_id)} ] } );
            res.status(200).json({data: sharingMats});
            doneLoad= true;
        }
        else if (!doneLoad) {
            const materialTotal = await Material.find({organization_id: 1}).countDocuments();
            const materials = await Material.find({organization_id: 1});
            res.status(200).json({data: materials});
        }
    } catch (error) {
        res.status(404).json({ message: error.message });       
    }
}

I have tried using aggregation to get my desired result but I cannot find any solution that fits my requirements. Any help would be great as I am quite new to using mongodb. Thanks.

Edit (desired result):

Materials: [
{
 _id: material1,
 organization_id: 1
},
{
 _id: material2,
 organization_id: 1
},
{
 _id: material3,
 organization_id: 1
}
]

CodePudding user response：

You can use sub-pipeline in a $lookup to perform the filtering. $addFields the count using $size later.

db.Sharing.aggregate([
  {
    "$match": {
      to_id: 1
    }
  },
  {
    "$lookup": {
      "from": "Material",
      "let": {
        to_id: "$to_id",
        material_id: "$material_id"
      },
      "pipeline": [
        {
          "$match": {
            $expr: {
              $or: [
                {
                  $eq: [
                    "$$to_id",
                    "$organization_id"
                  ]
                },
                {
                  $eq: [
                    "$$material_id",
                    "$_id"
                  ]
                }
              ]
            }
          }
        },
        {
          "$addFields": {
            "organization_id": 1
          }
        }
      ],
      "as": "materialLookup"
    }
  },
  {
    "$addFields": {
      "materialCount": {
        $size: "$materialLookup"
      }
    }
  }
])

Here is the Mongo playground for your reference.