I want to create a document with aggregation using pyMongo that generates a document by each value of a nested document.

My input collection:

  {
    "Id" : "12345-7",
    "Price: 
    {
            "Base" : "9.99",
            "Promo" : "7.99"
    },
    "Stock" : [ 
            {
                "Code" : "1",
                "Qty" : 1.0
            }, 
            {
                "Code" : "3",
                "Qty" : 7.0
            }
        ]
    }
    { 
    "Id" : "22222-0",
    "Price: 
    {
            "Base" : "2.99",
            "Promo" : "2.99"
    },
    "Stock" : [ 
            {
                "Code" : "3",
                "Qty" : 10.0
            }, 
            {
                "Code" : "5",
                "Qty" : 1.0
            },
            {
                "Code" : "10",
                "Qty" : 2.0
            }
         ]
    }

My expected aggregation output:

{
item_id : "12345-7",
store: "1",
price : 9.99,
quantity : 1,
sale_price: 7.99,
}
{
item_id : "12345-7",
store: "3",
price : 9.99,
quantity : 7,
sale_price: 7.99
}
{
item_id : 22222-0",
store: "3",
price : 2.99,
quantity : 10,
sale_price: 2.99
}
{
item_id : 22222-0",
store: "5",
price : 2.99,
quantity : 1,
sale_price: 2.99
}
{
item_id : 22222-0",
store: "10",
price : 2.99,
quantity : 2,
sale_price: 2.99
}

Where store is equal code, price is equal base, sale_price is equal promo, item_id is equal Id and quantity is equal Qty from input collection.

What I've done so far:

db.getCollection('File').aggregate([
{
    "$project" :
    {
        "price" : "$Price.Base",
        "sale_price" : "$Price.Promo",
        "item_id" : "$Id",
        "Stock" : 1
    }
},
{
    "$unset" : "Price"  
}
])

I've tried to use $unwind but with no success. How can I get my expected output using a simple aggregate if it is possible. Like I said before, I'm using pyMongo to perform this aggregate

CodePudding user response：

• $unwind
• $project

db.collection.aggregate([
  {
    "$unwind": "$Stock"
  },
  {
    "$project": {
      item_id: "$Id",
      store: "$Stock.Code",
      price: "$Price.Base",
      quantity: "$Stock.Qty",
      sale_price: "$Price.Promo",
      
    }
  }
])

mongoplayground