CODEWITHSUNDEEP
mongodbmongodb-query
miller
miller

Reputation: 934

Transform and group documents in MongoDB

I need to perform transformation of documents in mongodb collection.

Document structure examples:

{
    "_id" : ObjectId("530306d286ee881f7e6b4567"),
    "is_valid" : true,
    "is_visible" : false,
    "date" : ISODate("2014-02-18T07:07:23.000Z"),
    "product_type" : "book",
    "user_id" : 1234
},
{
    "_id" : ObjectId("530306d286ee881f7e8b4567"),
    "is_valid" : false,
    "is_visible" : true,
    "date" : ISODate("2014-02-18T07:07:23.000Z"),
    "product_type" : "cd",
    "user_id" : 5678
},
{
    "_id" : ObjectId("530306d286ee881f7e3b4567"),
    "is_valid" : true,
    "is_visible" : true,
    "date" : ISODate("2014-02-18T07:07:23.000Z"),
    "product_type" : "book",
    "user_id" : 3456
}

I need to group these documents by product_type, count their fields and list all user_id value in separate field. There result should look like:

{
    "product_type" : "book",
    "user_ids" : [1234, 3456],
    "valid_count" : 2,
    "visible_count" : 1,
},
{
    "product_type" : "cd",
    "user_ids" : [5678],
    "valid_count" : 1,
    "visible_count" : 1,
},

I've managed to group them by all three conditions, but unable to make a transformation with count:

db.getCollection('test').aggregate([
    { 
        $match: {
            date: {
                $gt: ISODate("2014-08-07 10:43:16.000Z"),
                $lt: ISODate("2015-07-07 10:43:16.000Z")
            }
        }
    },
    {
        $group: { _id : { product_type: "$product_type", is_valid: "$is_valid", is_visible: "$is_visible"}, total : { $sum : 1 } }
    }
])

Any help appreciated.

Upvotes: 2

Views: 331

Answers (1)

szymek
szymek

Reputation: 148

I was able to get what you need using following query:

db.getCollection('test').aggregate([
  { 
    $match: {
      date: {
        $gt: ISODate("2014-08-07 10:43:16.000Z"),
        $lt: ISODate("2015-07-07 10:43:16.000Z")
      }
    }
  },
  {
    $project: {
      validInt: { $cond: ["$is_valid", 1, 0] },
      visibleInt: { $cond: ["$is_visible", 1, 0] },
      product_type: "$product_type",
      user_id: "$user_id"
    }
  },
  {
    $group: {
      _id : { product_type: "$product_type" },
      user_ids: { $addToSet: "$user_id" },
      valid_count: { $sum: "$validInt" },
      visible_count: { $sum: "$visibleInt" }
    }
  }
])

I don't really know if there's a different way of casting boolean to integer values.

Upvotes: 2

Related Questions