Aggregate array of two or more collections in mongodb

Question

Hello,

I have two Collections and want to query the two Collections, like the sql "Join". The relation between the Collections is made as follows:

medico: [{
    medicoId: {
      type: mongoose.Schema.Types.ObjectId,
      ref: 'medicos'
    }
  }],

And each document will have the following json structure:

[
    {
        "nome": "clinica teste",
        "medico": [
            {
                "_id": "5e011a3796a5f80e3c0c8d20",
                "medicoId": {
                    "_id": "5dc5eef455a8f61698a0f2cd",
                    "nome": "Hancho Crutis",
                }
            },
            {
                "_id": "5e011a3796a5f80e3c0c8d1f",
                "medicoId": {
                    "_id": "5df16e5746783116709f09b7",
                    "nome": "camilinha",
                }
            }
        ],
    }
]

To do "Join" on Mongodb I saw that I have to use Aggregate. I have already researched and tried hard, but without success. The current code I have is this, but always returns me nothing.

Clinicas.aggregate([


    { $lookup: {
        from: "medicos",
        localField: "medico.medicoId.nome",
        foreignField: "nome",
        as: 'clinica_nome'
    }}, 
    { $unwind: "$medico"},
    { $match: {'medico.medicoId.nome': "camilinha" }},

Model da Collection CLINICAS

const mongoose = require('mongoose');
const Schema = mongoose.Schema;

const UserSchema = new Schema({
  nome: {
    type: String,
    required: true,
    maxlength: 50
  },
  medico: [{
    medicoId: {
      type: mongoose.Schema.Types.ObjectId,
      ref: 'medicos'
    }
  }],
  exame_consulta: [{
    exame_consulta_id: {
      type: mongoose.Schema.Types.ObjectId,
      ref: 'exames_consultas'
    }
  }],
});

module.exports = mongoose.model('clinicas', UserSchema);

MODEL MEDICAL COLLECTION

const mongoose = require('mongoose');
const Schema = mongoose.Schema;

const MedicoSchema = new Schema({
  nome:{
    type: String,
    required: true,
    maxlength: 80  
  },
  especialidade:{
    type: String,
    maxlength: 50
  },
});

module.exports = mongoose.model('medicos', MedicoSchema);

Answer 1

Hello, I recommend you do the unwind "medico" array first and then join the other collection:

Clinicas.aggregate([
  {
    $unwind: "$medico",
  },
  {
    $lookup: {
      localField: "medico.medicoId.nome",
      foreignField: "nome",
      as: "medico",
    },
  },
  {
    $unwind: "medico",
  },
  {
    $match: {
      "medico.nome": "camilinha"
    }
  },
])