CODEWITHSUNDEEP
mongodbviewaggregate-functionsmongodb-lookup
bernard
bernard

Reputation: 1

create view aggregating 3 collections with a concatenation

I have 3 collections. I would like to create a view that would tell me if the image file of the image collection is used on another collection.

Images

        "_id": "65018e77b4f945eb41faff44",
        "fileName": "file01.webp",
        "size": "66K",
        "createdDate": 1694600823,
        "updatedDate": 1694600823,
        "deleted": "",
        "sizeNumber": 67340

Mags

{
        "_id": "64fb1db918be726025f1fb9e",
        "title": "LES FEUILLES MORTES",
        "imageVertical": "/assets/uploads/file04.webp",
        "affiche": "/assets/uploads/file46.webp",
        "teaserImages": [
            "/assets/uploads/file32.webp",
            "/assets/uploads/file45.webp",
            "/assets/uploads/file12.webp"
        ],
        "releaseDate": 1694178264.818,
        "subtitle": "« Le génie d'Aki Kaurismäki est de savoir trenscender un univers sinistre avec un humour succulent et une poésie incomparable. »",
        "createdDate": 1694178745,
        "updatedDate": 1694179009,
        "deleted": "",
        "validated": true
    }

Movies


    {
        "_id": "6489f0c118be726025f1ee35",
        "category": "red",
        "title": "Interdit aux chiens et aux Italiens",
        "image": "/assets/uploads/file11.webp",
        "imageCard": "/assets/uploads/file24.webp",
        "director": "Alain Ughetto",
        "distributor": "Gebeka",
        "releaseDate": 1674601200,
        "videoLink": "https://player.vimeo.com/video/515151515845",
        "videoDlLink": "https://joon.doe",
        "imagesDlLink": "",
        "pressKitDlLink": "https://joon.doe",
        "createdDate": 1686761665,
        "updatedDate": 1686761959,
        "deleted": "",
        "validated": true,
        "__v": 0
    }

a convenient ouput would be :

[
  {
    "_id": "65018e77b4f945eb41faff44",
    "fileName": "file01.webp",
    "size": "66K",
    "createdDate": 1694600823,
    "updatedDate": 1694600823,
    "deleted": "",
    "sizeNumber": 67340,
    "used": {
      "movies": {
        "_id": "74118e77b4f945eb41faff24",
        "imageCard": "/assets/uploads/file01.webp"
      }
    }
  },
  {
    "_id": "65018e77b4f945eb41faff44",
    "fileName": "file32.webp",
    "size": "66K",
    "createdDate": 1694600823,
    "updatedDate": 1694600823,
    "deleted": "",
    "sizeNumber": 67340,
    "used": {
      "mags": {
        "_id": "21218e77b4f945eb41faff54",
        "imageVertical": "/assets/uploads/file32.webp"
      }
    }
  }
]

I can't get it working... I don't know if I messed up the concatenation at some point { "$concat": ["/assets/uploads/", "$fileName"] } } but I didn't manage to have any decent output

One of my attempt was this in the hope of having a way of connecting 2 collections on a few parameters :

db.images.aggregate([
  {
    $lookup: {
      from: "mags",
      let: { fileNameWithPrefix: { $concat: ["/assets/uploads/", "$fileName"] } },
      pipeline: [
        {
          $match: {
            $expr: {
              $or: [
                { $eq: ["$imageVertical", "$$fileNameWithPrefix"] },
                { $eq: ["$imageHorizontal", "$$fileNameWithPrefix"] },
                { $eq: ["$affiche", "$$fileNameWithPrefix"] }
              ]
            }
          }
        },
        {
          $project: {
            _id: 1,
            fileName: 1,
            size: 1,
            createdDate: 1,
            updatedDate: 1,
            deleted: 1,
            sizeNumber: 1,
            fileNameWithPrefix: 1,
            affiche: 1,
            imageHorizontal: 1,
            imageVertical: 1
          }
        }
      ],
      as: "joinedData"
    }
  },
  {
    $unwind: "$joinedData"
  },
  {
    $project: {
      _id: 1,
      idMag: "$joinedData._id",
      imageHorizontal: "$joinedData.imageHorizontal",
      imageVertical: "$joinedData.imageVertical",
      affiche: "$joinedData.affiche",
      fileName: 1,
      size: "$joinedData.size",
      createdDate: "$joinedData.createdDate",
      updatedDate: "$joinedData.updatedDate",
      deleted: "$joinedData.deleted",
      sizeNumber: "$joinedData.sizeNumber"
    }
  }
]);

here is the output :

[{
        "_id": "65018e77b4f945eb41fafde5",
        "fileName": "file08.webp",
        "idMag": "6475eefaa801c7bf4f5b8ee3",
        "imageVertical": "/assets/uploads/file31.webp",
        "affiche": "/assets/uploads/file08.webp",
        "createdDate": 1685450490,
        "updatedDate": 1685450490,
        "deleted": ""
    },
    {
        "_id": "65018e77b4f945eb41fafdff",
        "fileName": "file12.webp",
        "idMag": "6475f502a801c7bf4f5b8f15",
        "imageVertical": "/assets/uploads/file12.webp",
        "affiche": "/assets/uploads/file86.webp",
        "createdDate": 1685452034,
        "updatedDate": 1685549150,
        "deleted": ""
    }]

This output is not bad, I still try to find a way of getting the matches from insied the array of the mags collection of the value "teaserImages"

I tried this :

db.images.aggregate([
  {
    $lookup: {
      from: "mags",
      let: { fileNameWithPrefix: { $concat: ["/assets/uploads/", "$fileName"] } },
      pipeline: [
        {
          $match: {
            $expr: {
              $or: [
                { $eq: ["$imageVertical", "$$fileNameWithPrefix"] },
                { $eq: ["$imageHorizontal", "$$fileNameWithPrefix"] },
                { $eq: ["$affiche", "$$fileNameWithPrefix"] },
                {$in: ["$$fileNameWithPrefix", "$teaserImages"]}
              ]
            }
          }
        },
        {
          $project: {
            _id: 1,
            fileName: 1,
            size: 1,
            createdDate: 1,
            updatedDate: 1,
            deleted: 1,
            sizeNumber: 1,
            fileNameWithPrefix: 1,
            affiche: 1,
            imageHorizontal: 1,
            imageVertical: 1
          }
        }
      ],
      as: "joinedData"
    }
  },
  {
    $unwind: "$joinedData"
  },
  {
    $project: {
      _id: 1,
      idMag: "$joinedData._id",
      imageHorizontal: "$joinedData.imageHorizontal",
      imageVertical: "$joinedData.imageVertical",
      affiche: "$joinedData.affiche",
      fileName: 1,
      size: "$joinedData.size",
      createdDate: "$joinedData.createdDate",
      updatedDate: "$joinedData.updatedDate",
      deleted: "$joinedData.deleted",
      sizeNumber: "$joinedData.sizeNumber"
    }
  }
]);

here is the output :

{
    "message" : "PlanExecutor error during aggregation :: caused by :: $in requires an array as a second argument, found: missing",
    "ok" : 0,
    "code" : 40081,
    "codeName" : "Location40081"
}

Upvotes: 0

Views: 47

Answers (1)

bernard
bernard

Reputation: 1

My last attempt works fine to aggregates 2 of the collections. I will be using 2 queries and then I will concatenate the 2 result to have a 3 collection aggregation.

The last error that I had "PlanExecutor error during aggregation :: caused by :: $in requires an array as a second argument, found: missing" was just because for some reason there were a few test cases where the array was missing in the document, I deleted this documents that were not relevant and the aggregation worked fine as intended. Here is the output :

[{
        "_id": "65018e77b4f945eb41fafdd1",
        "fileName": "file-00.webp",
        "idMag": "6461d440a801c7bf4f5b8873",
        "imageVertical": "/assets/uploads/file-30.webp",
        "affiche": "/assets/uploads/file-e2.webp",
        "createdDate": 1684132928,
        "updatedDate": 1685528018,
        "deleted": "",
        "teaserImages": [
            "/assets/uploads/file-6a.webp",
            "/assets/uploads/file-00.webp",
            "/assets/uploads/file-19.webp"
        ]
    },
    {
        "_id": "65018e77b4f945eb41fafde5",
        "fileName": "file-0a.webp",
        "idMag": "6475eefaa801c7bf4f5b8ee3",
        "imageVertical": "/assets/uploads/file-3b.webp",
        "affiche": "/assets/uploads/file-0a.webp",
        "createdDate": 1685450490,
        "updatedDate": 1685450490,
        "deleted": "",
        "teaserImages": []
    }]

for this last updated query :

db.images.aggregate([
  {
    $lookup: {
      from: "mags",
      let: { fileNameWithPrefix: { $concat: ["/assets/uploads/", "$fileName"] } },
      pipeline: [
        {
          $match: {
            $expr: {
              $or: [
                { $eq: ["$imageVertical", "$$fileNameWithPrefix"] },
                { $eq: ["$imageHorizontal", "$$fileNameWithPrefix"] },
                { $eq: ["$affiche", "$$fileNameWithPrefix"] },
                {$in: ["$$fileNameWithPrefix", "$teaserImages"]}
              ]
            }
          }
        },
        {
          $project: {
            _id: 1,
            fileName: 1,
            size: 1,
            createdDate: 1,
            updatedDate: 1,
            deleted: 1,
            sizeNumber: 1,
            fileNameWithPrefix: 1,
            affiche: 1,
            imageHorizontal: 1,
            imageVertical: 1,
            teaserImages: 1
          }
        }
      ],
      as: "joinedData"
    }
  },
  {
    $unwind: "$joinedData"
  },
  {
    $project: {
      _id: 1,
      idMag: "$joinedData._id",
      imageHorizontal: "$joinedData.imageHorizontal",
      imageVertical: "$joinedData.imageVertical",
      affiche: "$joinedData.affiche",
      fileName: 1,
      size: "$joinedData.size",
      createdDate: "$joinedData.createdDate",
      updatedDate: "$joinedData.updatedDate",
      deleted: "$joinedData.deleted",
      sizeNumber: "$joinedData.sizeNumber",
      teaserImages: "$joinedData.teaserImages"
    }
  }
]);

Upvotes: 0

Related Questions