mongodb aggregation multiple unknown nested keys

Question

I have a document called 'InventoryPerDay' that contain inventory for stores for each day:

{
  _id: "20131202/store_a",
  _metadata: {
    date: ISODate("2013-12-02T00:00:00Z"),
    store: "store_a"
  },
  inventory: {
    quantity: {
      item_44: 1350,
      item_32: 1,
      item_2: 1,
      item_9: 1
    }
  }
},
{
  _id: "20131201/store_a",
  _metadata: {
    date: ISODate("2013-12-01T00:00:00Z"),
    store: "store_a"
  },
  inventory: {
    quantity: {
      item_44: 1000,
      item_32: 5,
      item_2: 10
    }
  }
}

I need the total quantity of each item in store_a for both of the days. The items in the 'quantity' hash are unknown. You can see that 'item_9' exists for 02/12/2013 but not for 01/12/2013.

How can i sum unknown nested keys in multiple documents using aggregation in mongodb?

The result for the above example should be:

{
  store: "store_a",
  inventory: {
    quantity: {
      item_44: 2350,
      item_32: 6,
      item_2: 11,
      item_9: 1
    }
  }
}

possible duplicate of [How to sum every fields in a sub document of MongoDB?](http://stackoverflow.com/questions/17661461/how-to-sum-every-fields-in-a-sub-document-of-mongodb) — WiredPrairie, Dec 12 '13 at 11:49

score 9 · Accepted Answer · edited May 23 '17 at 12:22

Unfortunately what you are seeking to do is not possible with the current capabilities of Mongodb (not with aggregation at least), and if you keep your current schema you will have to use map-reduce to do this which will be much slower.

You can refer here for an explanation, why your schema is not optimal.

You can refer here to understand how you should make your schema and how your aggregation should look like.

Also what you are asking would be possible with your current schema if you could project the names of the fields to values so maybe you can vote for this ticket so it gets more attention.

Update

You need to change your schema as such

{
  _id: "20131202/store_a",
  _metadata: {
    date: ISODate("2013-12-02T00:00:00Z"),
    store: "store_a"
  },
  inventory: {
    quantities: [
      { k : "item_44", v: 1350},
      { k : "item_32", v: 1},
      { k : "item_2", v: 1},
      { k : "item_9", v: 1},
    ]
  }
},
.
.
.

and the query should look like this

db.InventoryPerDay.aggregate(
 [
    {
        "$unwind" : "$inventory.quantities"
    },
    {
        "$group" : {
            "_id" : { "store": "$_metadata.store", "item" : "$inventory.quantities.k"},
            "total" : {
                "$sum" : "$inventory.quantities.v"
            }
        }
    }
])

This will give you a result like this

{
  result :
   [
    { "_id" : { store : "store_a", item : "item_44"}
      "total" : 2350
    },
    { "_id" : { store : "store_a", item : "item_32"}
      "total" : 6
    },
    { "_id" : { store : "store_a", item : "item_2"}
      "total" : 11
    },
    { "_id" : { store : "store_a", item : "item_9"}
      "total" : 1
    }
  ]
}

You can format these results with the $project operator at the end of the aggregation pipeline.

And regarding the map-reduce vs aggregation: Map reduce is much slower than aggregation and the main reason is because it is executed on a single thread. You can vote for this ticket so they implement it for multiple cores, in most cases, it takes orders of magnitude more time to compute with map-reduce vs aggregation.

That's what I was afraid of... Can you put an example of the new structure according to my example? — refaelos, Dec 12 '13 at 13:30
Also, In case i'm changing the structure, will aggregation framework will still be faster than map-reduce ? — refaelos, Dec 12 '13 at 13:31
My answer had some mistakes which i corrected so if you tested it and it didn't work check the edits to see what i changed. — Makis Tsantekidis, Dec 17 '13 at 13:58
I think this question is similar to this one https://stackoverflow.com/questions/56640054/mongodb-aggregate-fields-without-knowing-all-the-fields-before-hand — Shoebie, Jun 18 '19 at 17:51

mongodb aggregation multiple unknown nested keys

1 Answers1