Querying mongoDB for some chart data - my pipeline

2019-08-02 06:05发布

站内问答 / MongoDB
1982 1 3

This is a long question. If you bother answering, I will be extra grateful.

I have some time series data that I am trying to query to create various charts. The data format isn't the most simple, but I think my aggregation pipeline is getting a bit out of hand. I am planning to use charts.js to visualise the data on the client.

I will post a sample of my data below as well as my pipeline, with the desired output.

My question is in two parts - answering either one could solve the problem.

  1. Does charts.js accept data formats other than an array of numbers per row? This would mean my pipeline could try to do less.
  2. My pipeline doesn't quite get to the result I need. Can you recommend any alterations to get the correct result from my pipeline? Is there is a simpler way to get my desired output format?

Sample data

Here is a real data sample - a brand with one facebook account and one twitter account. There is some data for some dates in June. Lots of null day and month fields have been omitted.

Brand

  [{
    "_id": "5943f427e7c11ac3ad3652b0",
    "name": "Brand1",
    "facebookAccounts": [
      "5943f427e7c11ac3ad3652ac",
    ],
    "twitterAccounts": [
        "5943f427e7c11ac3ad3652aa",
    ],
  }]

FacebookAccounts

[
      {
        "_id" : "5943f427e7c11ac3ad3652ac"
        "name": "Brand 1 Name",
        "years": [
          {
            "date": "2017-01-01T00:00:00.000Z",
            "months": [
              {
                "date": "2017-06-01T00:00:00.000Z",
                "days": [
                  {
                    "date": "2017-06-16T00:00:00.000Z",
                    "likes": 904025,
                  },
                  {
                    "date": "2017-06-17T00:00:00.000Z",
                    "likes": null,
                  },
                  {
                    "date": "2017-06-18T00:00:00.000Z",
                    "likes": 904345,
                  },
                ],
              },
            ],
          }
        ]
      }
    ]

Twitter accounts

[
      {
        "_id": "5943f427e7c11ac3ad3652aa",
        "name": "Brand 1 Name",
        "vendorId": "twitterhandle",
        "years": [
          {
            "date": "2017-01-01T00:00:00.000Z",
            "months": [
              {
                "date": "2017-06-01T00:00:00.000Z",
                "days": [
                  {
                    "date": "2017-06-16T00:00:00.000Z",
                    "followers": 69390,
                  },
                  {
                    "date": "2017-06-17T00:00:00.000Z",
                    "followers": 69397,
                  {
                    "date": "2017-06-18T00:00:00.000Z",
                    "followers": 69428,
                  },
                  {
                    "date": "2017-06-19T00:00:00.000Z",
                    "followers": 69457,
                  },
                ]
              },
            ],
          }
        ]
      }
    ]

The query

For this example, I want, for each brand, a daily sum of facebook likes and twitter followers between June 16th and June 18th. So here, the required format is:

{
  brand: Brand1,
  date: ["2017-06-16T00:00:00.000Z", "2017-06-17T00:00:00.000Z", "2017-06-18T00:00:00.000Z"],
  stat: [973415, 69397, 973773]
}

The pipeline

The pipeline seems more convoluted due to the population, but I accept that complexity and it is necessary. Here are the steps:

db.getCollection('brands').aggregate([
{ $match: { _id: { $in: [ObjectId("5943f427e7c11ac3ad3652b0") ] } } },


// Unwind all relevant account types. Make one row per account
{ $project: { 
    accounts: { $setUnion: [ '$facebookAccounts', '$twitterAccounts' ] } ,
    name: '$name'
    }
 },
{ $unwind: '$accounts' },


// populate the accounts. 
// These transform the arrays of facebookAccount ObjectIds into the objects described above. 
{ $lookup: { from: 'facebookaccounts', localField: 'accounts', foreignField: '_id', as: 'facebookAccounts' } },
{ $lookup: { from: 'twitteraccounts', localField: 'accounts', foreignField: '_id', as: 'twitterAccounts' } },

// unwind the populated accounts. Back to one record per account.
{ $unwind: { path: '$facebookAccounts', preserveNullAndEmptyArrays: true } },
{ $unwind: { path: '$twitterAccounts', preserveNullAndEmptyArrays: true } },


// unwind to the granularity we want. Here it is one record per day per account per brand.
{ $unwind: { path: '$facebookAccounts.years', preserveNullAndEmptyArrays: true } },
{ $unwind: { path: '$facebookAccounts.years.months', preserveNullAndEmptyArrays: true } },
{ $unwind: { path: '$facebookAccounts.years.months.days', preserveNullAndEmptyArrays: true } },
{ $unwind: { path: '$facebookAccounts.years.months.days', preserveNullAndEmptyArrays: true } },
{ $unwind: { path: '$twitterAccounts.years', preserveNullAndEmptyArrays: true } },
{ $unwind: { path: '$twitterAccounts.years.months', preserveNullAndEmptyArrays: true } },
{ $unwind: { path: '$twitterAccounts.years.months.days', preserveNullAndEmptyArrays: true } },
{ $unwind: { path: '$twitterAccounts.years.months.days', preserveNullAndEmptyArrays: true } },


// Filter each one between dates
{ $match: { $or: [
    { $and: [
        { 'facebookAccounts.years.months.days.date': { $gte: new Date('2017-06-16') } } ,
        { 'facebookAccounts.years.months.days.date': { $lte: new Date('2017-06-18') } } 
    ]},
    { $and: [
        { 'twitterAccounts.years.months.days.date': { $gte: new Date('2017-06-16') } } ,
        { 'twitterAccounts.years.months.days.date': { $lte: new Date('2017-06-18') } } 
     ]}
] }},    


// Build stats and date arrays for each account
{ $group: {
    _id: '$accounts',
    brandId: { $first: '$_id' },
    brandName: { $first: '$name' },
    stat: { 
         $push: { 
             $sum: { 
                 $add: [
                    { $ifNull: ['$facebookAccounts.years.months.days.likes', 0] },
                    { $ifNull: ['$twitterAccounts.years.months.days.followers', 0] }
                 ] 
             } 
         } 
     },
     date: { $push: { $ifNull: ['$facebookAccounts.years.months.days.date', '$twitterAccounts.years.months.days.date'] } } ,
}}


])

This gives me the output format

[{
  _id: accountId, // facebook
  brandName: 'Brand1'
  date: ["2017-06-16T00:00:00.000Z", "2017-06-17T00:00:00.000Z", "2017-06-18T00:00:00.000Z"],
  stat: [904025, null, 904345]
},
{
  _id: accountId // twitter
  brandName: 'Brand1',
  date: ["2017-06-16T00:00:00.000Z", "2017-06-17T00:00:00.000Z", "2017-06-18T00:00:00.000Z"],
  stat: [69457, 69390, 69397]
}]

So I now need to perform column-wise addition on my stat properties.And then I am stuck - I feel like there should be a more pipeline friendly way to sum these rather than column-wise addition.

Note I accept the extra work that the population required and am happy with that. Most of the repetition is done programmatically.

Thank you if you've gotten this far.

1条回答
可以哭但决不认输i
2楼-- · 2019-08-02 06:49

I can trim a lot of fat out of this and keep it compatible with MongoDB 3.2 ( which you must be using at least due to preserveNullAndEmptyArrays ) available operators with a few simple actions. Mostly by simply joining the arrays immediately after $lookup, which is the best place to do it:

Short Optimize

db.brands.aggregate([
  { "$lookup": {
    "from": "facebookaccounts",
    "localField": "facebookAccounts",
    "foreignField": "_id",
    "as": "facebookAccounts"
  }},
  { "$lookup": {
    "from": "twitteraccounts",
    "localField": "twitterAccounts",
    "foreignField": "_id",
    "as": "twitterAccounts"
  }},
  { "$project": {
    "name": 1,
    "all": {
      "$concatArrays": [ "$facebookAccounts", "$twitterAccounts" ]
    }
  }},
  { "$match": {
    "all.years.months.days.date": {
      "$gte": new Date("2017-06-16"), "$lte": new Date("2017-06-18")
    }
  }},
  { "$unwind": "$all" },
  { "$unwind": "$all.years" },
  { "$unwind": "$all.years.months" },
  { "$unwind": "$all.years.months.days" },
  { "$match": {
    "all.years.months.days.date": {
      "$gte": new Date("2017-06-16"), "$lte": new Date("2017-06-18")
    }
  }},
  { "$group": {
    "_id": {
      "brand": "$name",
      "date": "$all.years.months.days.date"
    },
    "total": { 
      "$sum": { 
        "$sum": [
          { "$ifNull": [ "$all.years.months.days.likes", 0 ] },
          { "$ifNull": [ "$all.years.months.days.followers", 0 ] }
        ]
      }
    }
  }},
  { "$sort": { "_id": 1 } },
  { "$group": {
    "_id":  "$_id.brand",
    "date": { "$push": "$_id.date" },
    "stat": { "$push": "$total" }
  }}
])

This gives the result:

{
        "_id" : "Brand1",
        "date" : [
                ISODate("2017-06-16T00:00:00Z"),
                ISODate("2017-06-17T00:00:00Z"),
                ISODate("2017-06-18T00:00:00Z")
        ],
        "stat" : [
                973415,
                69397,
                973773
        ]
}

With MongoDB 3.4 we could probably speed it up a "little" more by filtering the arrays and breaking them down before we eventually $unwind to make this work across documents, or maybe even not worry about going across documents at all if the "name" from "brands" is unique. The pipeline operations to compact down the arrays "in place" though are quite cumbersome to code, if a "little" better on performance.

You seem to be doing this "per brand" or for a small sample, so it's likely of little consequence.

As for the chartjs data format, I don't seem to be able to get my hands on what I believe is a different data format to the array format here, but again this should have little bearing.

The main point I see addressed is we can easily move away from your previous output that separated the "facebook" and "twitter" data, and simply aggregate by date moving all the data together "before" the arrays are constructed.

That last point then obviates the need for further "convoluted" operations to attempt to "merge" those two documents and the arrays produced.

Alternate Optimize

As an alternate approach where this does in fact not aggregate across documents, then you can essentially do the "filter" on the array in place and then simply sum and reshape the received result in client code.

db.brands.aggregate([
  { "$lookup": {
    "from": "facebookaccounts",
    "localField": "facebookAccounts",
    "foreignField": "_id",
    "as": "facebookAccounts"
  }},
  { "$lookup": {
    "from": "twitteraccounts",
    "localField": "twitterAccounts",
    "foreignField": "_id",
    "as": "twitterAccounts"
  }},
  { "$project": {
    "name": 1,
    "all": {
      "$map": {
        "input": { "$concatArrays": [ "$facebookAccounts", "$twitterAccounts" ] },
        "as": "all",
        "in": {
          "years": {
            "$map": {
              "input": "$$all.years",
              "as": "year",
              "in": {
                "months": {
                  "$map": {
                    "input": "$$year.months",
                    "as": "month",
                    "in": {
                      "days": {
                        "$filter": {
                          "input": "$$month.days",
                          "as": "day",
                          "cond": {
                            "$and": [
                              { "$gte": [ "$$day.date", new Date("2017-06-16") ] },
                              { "$lte": [ "$$day.date", new Date("2017-06-18") ] }
                            ]
                          }
                        }
                      }
                    }
                  }
                }
              }
            }
          }
        }
      }
    }
  }}
]).map(doc => {
  doc.all = [].concat.apply([],[].concat.apply([],[].concat.apply([],doc.all.map(d => d.years)).map(d => d.months)).map(d => d.days));
  doc.all = doc.all.reduce((a,b) => {
    if ( a.findIndex( d => d.date.valueOf() == b.date.valueOf() ) != -1 ) {
      a[a.findIndex( d => d.date.valueOf() == b.date.valueOf() )].stat += (b.hasOwnProperty('likes')) ? (b.likes || 0) : (b.followers || 0);
    } else {
      a = a.concat([{ date: b.date, stat: (b.hasOwnProperty('likes')) ? (b.likes || 0) : (b.followers || 0) }]);
    }
    return a;
  },[]);

  doc.date = doc.all.map(d => d.date);
  doc.stat = doc.all.map(d => d.stat);
  delete doc.all;
  return doc;
})

This really leaves all the things that "need" to happen on the server, on the server. And it's then a fairly trivial task to "flatten" the array and process to "sum up" and reshape it. This would mean less load on the server, and the data returned is not really that much greater per document.

Gives the same result of course:

[
        {
                "_id" : ObjectId("5943f427e7c11ac3ad3652b0"),
                "name" : "Brand1",
                "date" : [
                        ISODate("2017-06-16T00:00:00Z"),
                        ISODate("2017-06-17T00:00:00Z"),
                        ISODate("2017-06-18T00:00:00Z")
                ],
                "stat" : [
                        973415,
                        69397,
                        973773
                ]
        }
]

Committing to the Diet

The biggest problem you really have is with the multiple collections and the heavily nested documents. Neither of these is doing you any favors here and will with larger results cause real performance problems.

The nesting in particular is completely unnecessary as well as not being very maintainable since there are limitations to "update" where you have nested arrays. See the positional $ operator documentation, as well as many posts about this.

Instead you really want a single collection with all those "days" entries in it. You can always work with that source easily for query as well as aggregation purposes and it should look something like this:

{
        "_id" : ObjectId("5948cd5cd6eb0b7d6ac38097"),
        "date" : ISODate("2017-06-16T00:00:00Z"),
        "likes" : 904025,
        "__t" : "Facebook",
        "account" : ObjectId("5943f427e7c11ac3ad3652ac")
}
{
        "_id" : ObjectId("5948cd5cd6eb0b7d6ac38098"),
        "date" : ISODate("2017-06-17T00:00:00Z"),
        "likes" : null,
        "__t" : "Facebook",
        "account" : ObjectId("5943f427e7c11ac3ad3652ac")
}
{
        "_id" : ObjectId("5948cd5cd6eb0b7d6ac38099"),
        "date" : ISODate("2017-06-18T00:00:00Z"),
        "likes" : 904345,
        "__t" : "Facebook",
        "account" : ObjectId("5943f427e7c11ac3ad3652ac")
}
{
        "_id" : ObjectId("5948cd5cd6eb0b7d6ac3809a"),
        "date" : ISODate("2017-06-16T00:00:00Z"),
        "followers" : 69390,
        "__t" : "Twitter",
        "account" : ObjectId("5943f427e7c11ac3ad3652aa")
}
{
        "_id" : ObjectId("5948cd5cd6eb0b7d6ac3809b"),
        "date" : ISODate("2017-06-17T00:00:00Z"),
        "followers" : 69397,
        "__t" : "Twitter",
        "account" : ObjectId("5943f427e7c11ac3ad3652aa")
}
{
        "_id" : ObjectId("5948cd5cd6eb0b7d6ac3809c"),
        "date" : ISODate("2017-06-18T00:00:00Z"),
        "followers" : 69428,
        "__t" : "Twitter",
        "account" : ObjectId("5943f427e7c11ac3ad3652aa")
}
{
        "_id" : ObjectId("5948cd5cd6eb0b7d6ac3809d"),
        "date" : ISODate("2017-06-19T00:00:00Z"),
        "followers" : 69457,
        "__t" : "Twitter",
        "account" : ObjectId("5943f427e7c11ac3ad3652aa")
}

Combining those referenced in the brands collection as well:

{
        "_id" : ObjectId("5943f427e7c11ac3ad3652b0"),
        "name" : "Brand1",
        "accounts" : [
                ObjectId("5943f427e7c11ac3ad3652ac"),
                ObjectId("5943f427e7c11ac3ad3652aa")
        ]
}

Then you simply aggregate like this:

db.brands.aggregate([
  { "$lookup": {
    "from": "social",
    "localField": "accounts",
    "foreignField": "account",
    "as": "accounts"
  }},
  { "$unwind": "$accounts" },
  { "$match": {
    "accounts.date": {
      "$gte": new Date("2017-06-16"), "$lte": new Date("2017-06-18")
    }
  }},
  { "$group": {
    "_id": {
      "brand": "$name",
      "date": "$accounts.date"
    },
    "stat": {
      "$sum": {
        "$sum": [
          { "$ifNull": [ "$accounts.likes", 0 ] },
          { "$ifNull": [ "$accounts.followers", 0 ] }
        ]
      }
    }
  }},
  { "$sort": { "_id": 1 } },
  { "$group": {
    "_id": "$_id.brand",
    "date": { "$push": "$_id.date" },
    "stat": { "$push": "$stat" }
  }}
])

This is actually the most efficient thing you can do, and it's mostly because of what actually happens on the server. We need to look at the "explain" output to see what happens to the pipeline here:

{
    "$lookup" : {
      "from" : "social",
      "as" : "accounts",
      "localField" : "accounts",
      "foreignField" : "account",
        "unwinding" : {
          "preserveNullAndEmptyArrays" : false
        },
        "matching" : {
           "$and" : [
             {
               "date" : {
                 "$gte" : ISODate("2017-06-16T00:00:00Z")
               }
             },
             {
               "date" : {
                 "$lte" : ISODate("2017-06-18T00:00:00Z")
               }
             }
           ]
        }
    }
}

This is what happens when you send $lookup -> $unwind -> $match to the server as the latter two stages are "hoisted" into the $lookup itself. This reduces the results in the actual "query" run on the collection to be joined.

Without that sequence, then $lookup potentially pulls in "a lot of data" with no constraint, and would break the 16MB BSON limit under most normal loads.

So not only is the process a lot more simple in the altered form, it actually "scales" where the present structure will not. This is something that you seriously should consider.

查看更多
0人赞 添加讨论(0) 举报
登录 后发表回答
相关问题
查看全部
相关文章
查看全部
收藏的人(3)