猫鼬复杂聚合流水线问题 [英] mongoose complex aggregation pipeline question

问题描述

我正在尝试完成数据聚合管道，但在将数据转换为正确格式时遇到问题。我甚至不确定这是否可能在一个管道中完成。

原始数据如下：

[
  {
    answers: {
      'question1': 'a',
      'question2': 'c',
      'question3': ['a','b'],
      'question4': 1
    },
    createdAt: 2022-03-04T07:30:40.517Z,
  },
  {
    answers: {
      'question1': 'b',
      'question2': 'c',
      'question3': ['a','c']
      'question4': 2
    },
    createdAt: 2022-03-04T07:30:40.518Z,
  }
]

到目前为止，我已经得到了我的渠道：

{ $project: { 
    "answers": { $objectToArray: "$answers" },
    "date": { $dateToString: { format: "%Y-%m-%d", date: "$createdAt" }}
}},
{ $unwind: "$answers" },
{ $unwind: "$answers.v" },
{
  $group: {
     _id: { answers : "$answers", date: "$date"},
     c: { $sum: 1 }}
 },

数据现在如下所示：

{
    _id: {
      answers: { k: 'q3', v: 'b' },
      date: '2022-03-04'
    },
    count: 1
  },
  {
    _id: {
      answers: { k: 'q3', v: 'a' },
      date: '2022-03-04'
    },
    count: 2
  },
  {
    _id: {
      answers: { k: 'q4', v: 1 },
      date: '2022-03-04'
    },
    count: 1
  },
  {
    _id: {
      answers: { k: 'q1', v: 'b' },
      date: '2022-03-04'
    },
    count: 1
  },
  {
    _id: {
      answers: { k: 'q4', v: 2 },
      date: '2022-03-04'
    },
    count: 1
  },
  {
    _id: {
      answers: { k: 'q2', v: 'c' },
      date: '2022-03-04'
    },
    count: 2
  },
  {
    _id: {
      answers: { k: 'q3', v: 'c' },
      date: '2022-03-04'
    },
    count: 1
  },
  {
    _id: {
      answers: { k: 'q1', v: 'a' },
      date: '2022-03-04'
    },
    count: 1
  }

我希望得到如下所示的结果：

{
    'dates': [
        {
            'date': '2022-03-04',
            'q1': { 'a': 1, 'b': 1 }
            'q2': { 'c': 2 },
            'q3': { 'a': 2, 'b': 1, 'c': 1 },
            'q4': { '1': 1, '2': 1 }
        }
    ]
    'totals': { // this would be the totals across all the dates
        'q1': { 'a': 1, 'b': 1 }
        'q2': { 'c': 2 },
        'q3': { 'a': 2, 'b': 1, 'c': 1 },
        'q4': { '1': 1, '2': 1 }
    }
}

任何帮助都将不胜感激，即使我无法在一次查询中同时获得合计和明细。

这里是mongoplaygroud I've been working on

推荐答案

没那么简单。您必须使用$facet才能获得totals和dates

使用$setWindowFields聚合管道可能会更简单，但这只是一个快速猜测。

db.collection.aggregate([
  {
    $project: {
      _id: 0,
      answers: { $objectToArray: "$answers" },
      date: { $dateToString: { format: "%Y-%m-%d", date: "$createdAt" } }
    }
  },
  { $unwind: "$answers" },
  { $unwind: "$answers.v" },
  {
    $group: {
      _id: {
        answer: "$answers.v",
        question: "$answers.k",
        date: "$date"
      },
      count: { $sum: 1 }
    }
  },
  {
    $facet: {
      dates: [
        {
          $group: {
            _id: { question: "$_id.question", date: "$_id.date" },
            count: {
              $push: {
                k: { $toString: "$_id.answer" },
                v: "$count"
              }
            }
          }
        },
        {
          $group: {
            _id: "$_id.date",
            count: {
              $push: {
                k: "$_id.question",
                v: { $arrayToObject: "$count" }
              }
            }
          }
        },
        {
          $replaceWith: {
            $mergeObjects: [
              { date: "$_id" },
              { $arrayToObject: "$count" }
            ]
          }
        }
      ],
      totals: [
        {
          $group: {
            _id: { answer: "$_id.answer", question: "$_id.question" },
            v: { $push: "$count" }
          }
        },
        {
          $group: {
            _id: "$_id.question",
            count: {
              $push: {
                k: { $toString: "$_id.answer" },
                v: { $sum: "$v" }
              }
            }
          }
        },
        {
          $project: {
            _id: 0,
            k: "$_id",
            v: { $arrayToObject: "$count" }
          }
        }
      ]
    }
  },
  { $set: { totals: { $arrayToObject: "$totals" } } }
])

Mongo Playground

这篇关于猫鼬复杂聚合流水线问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！