Mi inglés no es bueno, espero que entiendas mi pregunta. Soy novato, intento usar algún valor de la colección 2.
Tengo dos colecciones:
Colección 1:
{ _id: "61241ad82fcb9a369e59868a", "name": "aaaa", "products": [{ "_id": "611803232eeaf825336e26c4", "name": "products1", "pricePolicy": { "costPrice": 10 } }, { "_id": "611803232eeaf825336e26c5", "name": "products2", "pricePolicy": { "costPrice": 10 } }] }, { _id: "61241ad82fcb9a369e59868b", "name": "bbb", "products": [{ "_id": "611803232eeaf825336e26c4", "name": "products1", "pricePolicy": { "costPrice": 20 } }, { "_id": "611803232eeaf825336e26c5", "name": "products2", "pricePolicy": { "costPrice": 10 } }] }Y colección 2:
{ "_id": "61179c8d4ef00f31df195223", "name": "zzzz", "listProduct": [ { "id": "611803232eeaf825336e26c4", "name": "products1", "costPrice": 100, }, { "id": "611803232eeaf825336e26c6", "name": "products3", "costPrice": 10, } ] }, ¿Cómo $sum costPrice de dos colecciones y ordenar el producto por valor? resultado sigue:
{ _id: 611803232eeaf825336e26c4, name: products1, totalCollection1: 30, totalCollection2: 10, total: 40 } { _id: 611803232eeaf825336e26c5, name: products2, totalCollection1: 20, totalCollection2: 0, total: 20 } { _id: 611803232eeaf825336e26c6, name: products3, totalCollection1: 0, totalCollection2: 10, total: 10 }Probé esta consulta agregada y funciona como se desea. Usé los datos de su publicación de preguntas y el resultado está en la parte inferior:
db.coll1.aggregate([ { $unwind: "$products" }, { $sort: { "products._id": 1 } }, { $group: { _id: "$products._id", productName1: { $first: "$products.name" }, total1: { $sum: "$products.pricePolicy.costPrice" } } }, { $group: { _id: null, productSummary1: { $push: "$$ROOT" } } }, { $lookup: { from: "coll2", pipeline: [], as: "products2" }}, { $unwind: "$products2" }, { $unwind: "$products2.listProduct" }, { $sort: { "products2.listProduct.id": 1 } }, { $group: { _id: "$products2.listProduct.id", productSummary1: { $first: "$productSummary1" }, productName2: { $first: "$products2.listProduct.name" }, total2: { $sum: "$products2.listProduct.costPrice" } }}, { $group: { _id: null, productSummary1: { $first: "$productSummary1" }, productSummary2: { $push: { "_id": "$_id", "productName1": "$productName2", "total2": "$total2" } } }}, { $project: { products: { $concatArrays: [ "$productSummary1", "$productSummary2" ] } } }, { $unwind: "$products" }, { $group: { _id: "$products._id", productName: { $first: "$products.productName1" }, total1: { $sum: "$products.total1" }, total2: { $sum: "$products.total2" }, total1a: { $push: "$products.total1" }, total2a: { $push: "$products.total2" }, }}, { $project: { productName: 1, total1: 1, total2: 1, total: { $sum: { $concatArrays: [ "$total1a", "$total2a" ] } } } }, { $sort: { total: -1 } } ])La salida:
{ "_id" : "611803232eeaf825336e26c4", "productName" : "products1", "total1" : 30, "total2" : 100, "total" : 130 } { "_id" : "611803232eeaf825336e26c5", "productName" : "products2", "total1" : 20, "total2" : 0, "total" : 20 } { "_id" : "611803232eeaf825336e26c6", "productName" : "products3", "total1" : 0, "total2" : 10, "total" : 10 }$unwind deconstruye la matriz de products$group por nulo y construir la matriz de products con los campos obligatorios$lookup con la colección 2$unwind deconstruye la matriz listProduct$project para mostrar los campos obligatorios$project para concatenar la matriz de ambas colecciones en products de un solo campo$unwind deconstruye la matriz de products anterior$group by _id significa identificación del producto y obtiene la suma total del precio para la colección$ifNull para verificar si el valor es nulo, devolverá 0$sort por total en orden descendente db.col1.aggregate([ { $unwind: "$products" }, { $group: { _id: null, col1: { $push: { _id: "$products._id", name: "$products.name", totalCollection1: "$products.pricePolicy.costPrice" } } } }, { $lookup: { from: "col2", pipeline: [ { $unwind: "$listProduct" }, { $project: { _id: "$listProduct.id", name: "$listProduct.name", totalCollection2: "$listProduct.costPrice" } } ], as: "col2" } }, { $project: { products: { $concatArrays: ["$col1", "$col2"] } } }, { $unwind: "$products" }, { $group: { _id: "$products._id", name: { $first: "$products.name" }, totalCollection1: { $sum: { $ifNull: ["$products.totalCollection1", 0] } }, totalCollection2: { $sum: { $ifNull: ["$products.totalCollection2", 0] } } } }, { $addFields: { total: { $sum: ["$totalCollection1", "$totalCollection2"] } } }, { $sort: { total: -1 } } ])