skip to Main Content

i need to join two documents where in the first there a property as array of _ids and in the second documents to join

db={
"tipo_pratica": [
{
  "_id": "618981a4c1b8b3bc67ff80b6",
  "descrizione": "anticipata",
  "modulo": [
    "628015cd2fd9dfee86ac6820",
    "62801a4c2fd9dfee86ac6821",
    "6278f8d9d4aa4f4cef1a8266"
  ]},
{
  "_id": "628238d6f97b57efcb1fc504",
  "descrizione": "Supporto",
  "modulo": [
    "6278f8d9d4aa4f4cef1a8266",
    "628015cd2fd9dfee86ac6820",
    "62801a4c2fd9dfee86ac6821"
  ]}
]};


db={
"moduli": [
{
  "_id": "6278f8d9d4aa4f4cef1a8266",
  "tipo": "Incentivi auto",
  "documento": [
    "1652095190015_la_scuola_2021_copia.pdf"
  ],
  "contenuto": "<p>Inserire il documento allegato</p>"
},
{
  "_id": "628015cd2fd9dfee86ac6820",
  "tipo": "Mandato di assistenza e rappresentanza",
  "documento": [
    "1652561335432_Mandato_di_rappresentanza_privacy_0.pdf"
  ],
  "contenuto": "<p>no</p>"
},
{
  "_id": "62801a4c2fd9dfee86ac6821",
  "tipo": "Modello red da far... ",
  "documento": [
    "1652562502599_Modello_REX.pdf"
  ],
  "contenuto": null
}]
};

as documentation said:
Use $lookup with an Array

I tried:

const doc = await collection.aggregate([
 {
  $lookup: {
  from: "moduli",
  localField: "modulo",
  foreignField: "_id",
  as: "moduls"
   }
  }
  ])

with no success

so i tested the script on mongoplayground

and there it seems to work well.
I think the problem reside in array of Ids, also
i have tried many option, i have often read the documentation and serching on the web, but many results are specific to mongoose drive, while i use native drive.

I would like the same return as the playground example.

So, any help is largely apprecciate.

below the snippet i use in node for make call

app.post('/admin/moduli/join/', (req, res, error) => {

 async function run() {

 try {

      await client.connect();
      var ObjectId = require('mongodb').ObjectId;
      const db = client.db("admin");
      const collection = db.collection("tipo_pratica");

    // replace IDs array with lookup results passed to pipeline
     const doc = await collection.aggregate([
          {
           $lookup: {
               from: "moduli",
               localField: "modulo",
               foreignField: "_id",
            pipeline: [
               { $match: { $expr: {$in: ["$_id", "$$modulo"] } } },
              { $project: {_id: 0} } // suppress _id
              ],
              as: "productObjects"
                 }
             }
           ]);

// doc not work!

          // Unwind
          const doc2 = await collection.aggregate([
            // Unwind the source
            { "$unwind": "$modulo" },
           // Do the lookup matching
            { "$lookup": {
               "from": "moduli",
               "localField": "modulo",
               "foreignField": "_id",
               "as": "productObjects"
             }
           }

// doc2 not work!

        const doc3 = await collection.aggregate([
      {
       $facet: {
        moduli: [
       {
       $lookup: {
        from: "moduli",
        localField: "modulo",
        foreignField: "_id", // also tried ObjectId()
        as: "plugin"
      }
    },
    {
      $match: {
        plugin: {
          $not: {
            $size: 0
             }
           }
         }
       }
     ]
   }
  },
{
$project: {
  tipoPratiche: {
    "$concatArrays": [
      "$moduli"
       ]
     }
   }
 },
 {
   $unwind: "$tipoPratiche"
 },

]).toArray();

// doc3 not work!

     res.send(doc4);


} finally {
  await client.close();
  }

 }

   run().catch(console.dir);

 });

Thank you in advance for your time

2

Answers


  1. Chosen as BEST ANSWER

    The proble reside in the formatting text. So belove the solution

    const doc1 = await db.collection("tipo_pratica").aggregate([
    {
    '$set': {
    'modulo': {
    '$map': {
    'input': '$modulo',
    'as': 'item',
    'in': {
    '$toObjectId': '$$item'
    }
    }
    }
    }
    }, {
    '$lookup': {
    'from': 'moduli',
    'localField': 'modulo',
    'foreignField': '_id',
    'as': 'moduls'
    }
    }
    ]).toArray();
    

  2. One way to do it is using a $map before the $lookup:

    db.tipo_pratica.aggregate([
      {
        $set: {
          modulo: {
            $map: {
              input: "$modulo",
              as: "item",
              in: {$toObjectId: "$$item"}
            }
          }
        }
      },
      {
        $lookup: {
          from: "moduli",
          localField: "modulo",
          foreignField: "_id",
          as: "moduls"
        }
      }
    ])
    

    Playground

    Another option is to use a $lookup pipeline and cast the string to ObjectId inside it.

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search