mongodb - sottoaggregazione di documenti nidificati

Question

Supponiamo che in una pipeline uno dei passaggi produca i seguenti risultati:

{Name : "Avi", category : "a1",
    sales : [{year: 2003, month: 6, client: "Avi", location: "Tel Aviv",
        product_a: 4711.0, product_b: 928.0, product_c: 80.37},
        {year: 2004, month : 6, client: "Avi", location: "Jerusalem",
            product_a: 43.0, product_b: 345.0, product_c: 85.34}, 
        {year: 2003, month: 6, client: "Avi", location: "Jerusalem",
            product_a: 33.0, product_b: 545.0, product_c: 5.54}]
}     
{Name: "Moshe", category: "aa",
    sales: [{year: 2012, month: 3, client: "Moshe", location: "Ariel",
        product_a: 242.0, product_b: 34.0, product_c: 34.2},
        {year: 2003, month: 4, client: "Moshe", location: "Ariel",
            product_a: 423.0, product_b: 36.0, product_c: 47.11}, 
        {year: 2003, month: 2, client: "Moshe", location: "Jerusalem",
            product_a: 775.0, product_b: 452.0, product_c: 52.21}]
}... 

Nei passaggi successivi voglio aggregare il valore dei documenti nidificati (aggregare le vendite) di ciascun prodotto per posizione e omettere i campi non necessari.

Ad esempio per creare il seguente output:

{Name: "Avi", category : "a1", sales: [
    {location: "Tel Aviv", total_product_a: 4711.0, total_product_b: 928.0, total_product_c: 80.37},
    {location: "Jerusalem", total_product_a: 76.0, total_product_b: 890.0, total_product_c: 90.88}]
}     
{Name: "Moshe", category: "aa", sales:[
    {location: "Ariel", total_product_a: 665.0, total_product_b: 70.0, total_product_c: 81.31},
    {location: "Jerusalem", total_product_a: 755.0, total_product_b: 452.0, total_product_c: 52.21}]
}...

L'elenco prodotti è un elenco fisso che include solo tre prodotti (prodotto_a, prodotto_b, prodotto_c)

Answer 1

Puoi provare questo,

• $unwinddecostruire l' salesarray
• $groupby Namee sales.location, crea la somma di product_a, product_b, product_c nei campi total
• $groupper Namee inserisci il totale della posizione insales
• $projectrimuovere_id

db.collection.aggregate([
  { $unwind: "$sales" },
  {
    $group: {
      _id: {
        Name: "$Name",
        location: "$sales.location"
      },
      category: { $first: "$category" },
      total_product_a: { $sum: "$sales.product_a" },
      total_product_b: { $sum: "$sales.product_b" },
      total_product_c: { $sum: "$sales.product_c" }
    }
  },
  {
    $group: {
      _id: "$_id.Name",
      Name: { $first: "$_id.Name" },
      category: { $first: "$category" },
      sales: {
        $push: {
          location: "$_id.location",
          total_product_a: "$total_product_a",
          total_product_b: "$total_product_b",
          total_product_c: "$total_product_c"
        }
      }
    }
  },
  { $project: { _id: 0 } }
])

Terreno di gioco