Aggregate with filters

About the Community Welcome
1

Hello,

I am trying to relate several collections, but when applying the aggregate it sends me the combination of documents that have no relationship.
Is there a way to filter by 2 or 3 fields so that the result is more specific?

Example using only one field as a relationship:

db.Compras.aggregate([
{
$lookup: {
from: ‘Subsidiaria’,
localField: ‘come.idCo’,
foreignField: ‘come.idCo’,
as: ‘subsidiaria_info’,
},
},
{
$lookup: {
from: ‘Sucursal’,
localField: ‘idSucursal’,
foreignField: ‘subsidiaria_info.idSucursal’,
as: ‘sucursal_info’,
},
},
{
$lookup: {
from: ‘Usuario’,
localField: ‘idUsuario’,
foreignField: ‘tra.idUsuario’,
as: ‘Usuario_info’,
},
},
{
$lookup: {
from: ‘Persona’,
localField: ‘idPersona’,
foreignField: ‘Usuario_info.persona.idPer’,
as: ‘Persona_info’,
},
},
{
$unwind: ‘$subsidiaria_info’,
},
{
$unwind: ‘$sucursal_info’,
},
{
$unwind: ‘$Usuario_info’,
},
{
$unwind: ‘$Persona_info’,
}
]);

Your help would be very useful to me.

2

Please update your post after reading

3

Hello,
I share a brief example of the result of the aggregate:

I notice that when I perform the query with the universe of documents in these collections it sends me a many-to-many result when in reality I am looking for a 1-to-1 result

db.Compras
{
_id: ObjectId(‘65d551KL89643e13db77f29d’),
come: { idCo: Long(‘298’), nombreComercio: ‘Rock’ },
estatusCompra: {
idEstatusVenta: Long(‘2’),
nombre: ‘Cancelado’
},
montoCompra: 405.64,
sucursal: { idSucursal: Long(‘1’), nombreSucursal: ‘Ishval’ },
tra: {
fechaActualiza: ‘2023-07-27 12:24:36’,
fechaCrea: ‘2023-07-27 12:24:32’,
idUsuarioActualiza: ‘1344’,
idUsuario: ‘1344’
},
subsidiaria_info: {
_id: ObjectId(‘65cf9222a91647a0df57cbdc’),
come: {
idCo: Long(‘298’),
idTipoComercio: Long(‘1’),
},
idAgente: Long(‘17’),
idSubsidiaria: Long(‘206’),
nombre: ‘Rock’,
nombreCorto: ‘Rock’,
tra: {
fechaActualiza: ’ ',
fechaCrea: ‘2023-07-27 12:11:56’,
idUsuarioActualiza: ’ ',
idUsuario: ‘901’
},
},
sucursal_info: {
_id: ObjectId(‘65cf9cfaa91647a0df57cbe1’),
idSucursal: 23,
idSubsidiaria: 8,
idAgente: 17,
nombre: ‘Las Tinas’,
horaApertura: ‘0’,
horaCierre: ‘0’,
tra: {
idUsuario: ‘USR’,
idUsuarioActualiza: ‘-’,
fechaCrea: ‘22-08-2022’,
fechaActualiza: ‘-’
},
},
Usuario_info: {
_id: ObjectId(‘65d619526397f5d9256e6ba3’),
idUsuario: Long(‘127’),
persona: { idPersona: 98 },
tra: {
idUsuario: ‘93’,
fechaCrea: ‘2023-02-17T10:18:44.617Z’,
fechaActualiza: ‘’,
idUsuarioActualiza: ‘’
},
correo: ‘Sistemas@baa.com.mx’
},
Persona_info: {
_id: ObjectId(‘65d6202b6397f5d9258459a5’),
idCome: Long(‘1032’),
idPersona: Long(‘98’),
nombre: ‘MANUEL’,
primerApellido: ‘ROSAS’,
segundoApellido: ‘LOPEZ’,
tra: {
fechaActualiza: ’ ',
fechaCrea: ‘2023-02-17 10:18:44’,
idUsuarioActualiza: ’ ',
idUsuario: ‘usuario’
}
}
}

What I’m looking for is that the documents match your only document that contains all the information about your relationships.

4

It looks like you have not read the link I shared because you published again a document that is not formatted correctly.