Problem using aggregation in mongodb retrieving data from two collections

Question

i am strugling with a query that i don't know how to perform... I have two collections,

Tarifas Collection

tarifaConfig = new Schema({
    producto: { type: String },
    titulo: { type: String },
    bloqueo: { type: Boolean },
    margen: { type: Number },
    precioVenta: { type: Number },
    precioVentaIva: { type: Number },
})

const tarifaSchema = new Schema({
    codigo: { type: String },
    titulo: { type: String },
    margen: { type: Number },
    estado: { type: Boolean },
    bloqueo: { type: Boolean },
    configs: [tarifaConfig]
})

Producto Collection

const productosSchema = new Schema({
    ref: { type: String },
    nombre: { type: String },
    precioCompra: { type: Number },
    precioCompraIva: { type: Number },
    precioVenta: { type: Number },
    precioVentaIva: { type: Number },
    iva: { type: Number },
})

Now i am using an Aggregation method to retrieve both collection in a response

 productosModel.aggregate([
        {
            $match: { _id: ObjectId(req.params.id) }
        },
        {
            $lookup: {
                from: "tarifas",
                as: "tarifas",
                pipeline: []
            }
        }
    ]).then((producto) => {
        res.json(producto);
    })

This is working and gives me both collections in the response... but.. In tarifa's collection i have a propertie called 'configs' that is an array with lot of sub collections... this sub collections are a config of each product that i have, So what i need to do is, retrieve all tarifas that has a configs for the product, and if the configs does not contain retrieve the tarifa with a empty array.

Expected result

{
   ref: 'rbe34',
   nombre: 'bike',
   precioCompra: 10,
   precioCompraIva: 12.1,
   precioVenta: "",
   precioVentaIva: "",
   iva: 21,
   tarifas:[
   { 
    codigo: 'NOR',
    titulo: 'Normal tarifa',
    margen: 33,
    estado: true,
    bloqueo: true,
    configs: [], ///HERE I NEED A EMPTY ARRAY IF THERE IS NOT ANY CONFIG THAT MATCH WITH THE PRODUCT ID,  
   }
   ]
}

i tried to add $match in my aggregation pipeline.

   productosModel.aggregate([
        {
            $match: { _id: ObjectId(req.params.id) }
        },
        {
            $lookup: {
                from: "tarifas",
                as: "tarifas",
                pipeline: [
                    { $match: { 'configs.producto': req.params.id } }
                ]
            }
        }
    ])

But if there is not any config that match the product it doesn't retrieve the rest of Tarifa's collection

How do you determine that a specific config matches the product or not? — Joe
– Joe, Commented Mar 19, 2020 at 1:34

Sergio Cano · Accepted Answer · 2020-03-19 16:33:50Z

1

It seems you are trying to $filter the array after you retrieve it.

This pipeline will return only the configs for which the producto field from the config matches the ref field from the product.

    [
        {
            $match: { _id: ObjectId(req.params.id) }
        },
        {
            $lookup: {
                from: "tarifas",
                as: "tarifas",
                pipeline: [
                  {
                    $addFields: {
                         "tarifas.configs":{ $filter:{
                                               input: "$tarifas.configs",
                                                cond: {$eq:["$$this.producto","$ref"]}
                           } }
                       } 
                   }
               ]
            }
        },

    ]

Change the fields in the $eq array to the ones you need to match.

edited Mar 19, 2020 at 16:33

Sergio Cano

7803 gold badges18 silver badges37 bronze badges

answered Mar 19, 2020 at 1:37

Joe

29k4 gold badges25 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Sergio Cano Over a year ago

i think you are really close... but i am doing $$this.producto, which is the field that i am looking inside the tarifas.config array, and i am changing $ref for the data that i want filter... but is not retreiving me config inside the array... it is retreiving with the tarifas agreggation but tarifas.config is empty... and i am checking the collection and there is a register that match with it :S

Sergio Cano Over a year ago

I tried a lot of ways to find the problem, and it seems that in $filter:{ input: "$tarifas.configs", } is not filtering good, because i tried to filter something up level like $filter:{ input: "$tarifas", cond:{$eq: [""$$this._id, ID] and it is filtering correctly... so why it input: "$tarifas.configs" is not filtering... maybe because configs is an array?.... dunno

Sergio Cano Over a year ago

ok solved it, just adding the $addFields in the pipeline and it is working... thanks man i am learning a lot about the power of aggregation ;:D

Collectives™ on Stack Overflow

Problem using aggregation in mongodb retrieving data from two collections

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related