Re: Wide to long transformation in aggregation

From: Kevin Adistambha <kevinadi@xxxxxxxxxxx>
To: mongodb-user <mongodb-user@xxxxxxxxxxxxxxxx>
Date: Thu, 5 May 2016 00:02:32 -0700 (PDT)
Why ads?


Hi Boyd,

I am assuming that the numbers in your example aggregation represent counts 
of activities (i.e. ship, order, start, and stop) that occurred in each 
Year-Month combination. Please let me know if this assumption is incorrect.

Using your example data:

{
  "order_no": "A",
  "ship_dt": ISODate("2009-05-15T00:00:00Z"),
  "order_dt": ISODate("2008-01-28T00:00:00Z"),
  "start_dt": ISODate("2008-04-02T00:00:00Z"),
  "stop_dt": ISODate("2008-04-18T00:00:00Z")
}
{
  "order_no": "B",
  "ship_dt": ISODate("2009-05-15T00:00:00Z"),
  "order_dt": ISODate("2008-02-28T00:00:00Z"),
  "start_dt": ISODate("2008-04-15T00:00:00Z"),
  "stop_dt": ISODate("2008-05-01T00:00:00Z")
}

the aggregation query you need is:

db.orders.aggregate(

  // Project the year and month of each order, conserving the activity type, into an array
  {$project: {
    activities: [
      {year: {$year: "$ship_dt"}, month: {$month: "$ship_dt"}, type: {$literal: "ship"}},
      {year: {$year: "$order_dt"}, month: {$month: "$order_dt"}, type: {$literal: "order"}},
      {year: {$year: "$start_dt"}, month: {$month: "$start_dt"}, type: {$literal: "start"}},
      {year: {$year: "$stop_dt"}, month: {$month: "$stop_dt"}, type: {$literal: "stop"}}
    ]
  }},

  // Unwind the activities array
  {$unwind: "$activities"},

  // For each distinct year-month, push each activity type into an array
  {$group: {
    _id: {year: "$activities.year", month: "$activities.month"},
    type: {$push: "$activities.type"}
  }},

  // For each distinct year-month, put the count of each activity into separate fields
  {$project: {
    _id: "$_id",
    order: {$size: {$filter: {input: "$type", as: "x", cond: {$eq: ["$$x", "order"]} }} },
    start: {$size: {$filter: {input: "$type", as: "x", cond: {$eq: ["$$x", "start"]} }} },
    stop:  {$size: {$filter: {input: "$type", as: "x", cond: {$eq: ["$$x", "stop"]} }} },
    ship:  {$size: {$filter: {input: "$type", as: "x", cond: {$eq: ["$$x", "ship"]} }} }
  }},

  // Sort by year-month
  {$sort: {"_id.year": 1, "_id.month": 1}}
)

Running the aggregation query with your example data resulted in:

{
  "_id": {
    "year": 2008,
    "month": 1
  },
  "order": 1,
  "start": 0,
  "stop": 0,
  "ship": 0
},
{
  "_id": {
    "year": 2008,
    "month": 2
  },
  "order": 1,
  "start": 0,
  "stop": 0,
  "ship": 0
},
{
  "_id": {
    "year": 2008,
    "month": 4
  },
  "order": 0,
  "start": 2,
  "stop": 1,
  "ship": 0
},
{
  "_id": {
    "year": 2008,
    "month": 5
  },
  "order": 0,
  "start": 0,
  "stop": 1,
  "ship": 0
},
{
  "_id": {
    "year": 2009,
    "month": 5
  },
  "order": 0,
  "start": 0,
  "stop": 0,
  "ship": 2
}

Please note that this aggregation query assumes that the whole collection 
will be used, which may not be very performant in a very large collection. 
If your use case permits, I would recommend to add a $match stage as the 
first stage (e.g. before the $project stage) to reduce the amount of data 
that enters the pipeline.

If you find that you need to do this aggregation frequently, you might find 
that using a Pre-Aggregated Reports 
<https://docs.mongodb.org/ecosystem/use-cases/pre-aggregated-reports/
beneficial. An advantage of using pre-aggregated report is that it allows 
you to generate up-to-the-minute reports without the overhead of running a 
resource-intensive aggregation query, which could interfere with your 
day-to-day use of the database. The reports can then be retrieved using a 
single find() command. The tradeoff is that your application would need to 
update two collections at once for each data point insertion (i.e. an 
insert for the original collection, and another for the pre-aggregated 
report).

Best regards,
Kevin


-- 
You received this message because you are subscribed to the Google Groups "mongodb-user"
group.

For other MongoDB technical support options, see: https://docs.mongodb.org/manual/support/
--- 
You received this message because you are subscribed to the Google Groups "mongodb-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mongodb-user+unsubscribe@xxxxxxxxxxxxxxxx.
To post to this group, send email to mongodb-user@xxxxxxxxxxxxxxxx.
Visit this group at https://groups.google.com/group/mongodb-user.
To view this discussion on the web visit https://groups.google.com/d/msgid/mongodb-user/c0e417b2-23e6-477f-8e20-cfd06176da63%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
Why ads?