mongodb - 在 MongoDB 中将行透视到列

Question

相关问题是Efficiently convert rows to columns in sql server .但答案是特定于 SQL 的。

我想要相同的结果，即在 MongoDB 中不聚合任何内容(截至目前)，将行转为列。

该集合如下所示。这些是 Facebook 页面属性的统计数据:

timestamp | propName | propValue--------------------------------1371798000000 | page_fans | 100--------------------------------1371798000000 | page_posts | 50--------------------------------1371798000000 | page_stories | 25--------------------------------

I need answer like:

timestamp | page_fans | page_posts | page_stories--------------------------------1371798000000 | 100 | 50 | 25--------------------------------

The column names are pre-determined. They don't have to be generated dynamically. But question is how to achieve this in MongoDB.

I believe aggregation is of no use for this purpose. Do I need to use MapReduce? But in that case I have nothing to reduce I guess? Well another option could be fetching these values in code and do the manipulation in programming language e.g. Java

Any insights would be helpful. Thanks in advance :)!!!

EDIT (Based on input from Schaliasos):

Input JSON:

{
        "_id" : ObjectId("51cd366644aeac654ecf8f75"),
        "name" : "page_storytellers",
        "pageId" : "512f993a44ae78b14a9adb85",
        "timestamp" : NumberLong("1371798000000"),
        "value" : NumberLong(30871),
        "provider" : "Facebook"
}
{
        "_id" : ObjectId("51cd366644aeac654ecf8f76"),
        "name" : "page_fans",
        "pageId" : "512f993a44ae78b14a9adb85",
        "timestamp" : NumberLong("1371798000000"),
        "value" : NumberLong(1291509),
        "provider" : "Facebook"
}
{
        "_id" : ObjectId("51cd366644aeac654ecf8f77"),
        "name" : "page_fan_adds",
        "pageId" : "512f993a44ae78b14a9adb85",
        "timestamp" : NumberLong("1371798000000"),
        "value" : NumberLong(2829),
        "provider" : "Facebook"
}

预期输出 JSON:

{
        "timestamp" : NumberLong("1371798000000"),
        "provider" : "Facebook",
        "page_storytellers" : NumberLong(30871),
        "page_fans" : NumberLong("1371798000000"),
        "page_fan_adds" : NumberLong("1371798000000")
}

Answer 1

现在，您可以使用新的聚合 operator $arrayToObject旋转 MongoDB 键。该运算符在 MongoDB v3.4.4+ 中可用

例如，给定一个示例数据:

db.foo.insert({ provider: "Facebook", timestamp: '1371798000000', name: 'page_storytellers', value: 20871})
db.foo.insert({ provider: "Facebook", timestamp: '1371798000000', name: 'page_fans', value: 1291509})
db.foo.insert({ provider: "Facebook", timestamp: '1371798000000', name: 'page_fan_adds', value: 2829})
db.foo.insert({ provider: "Google", timestamp: '1371798000000', name: 'page_fan_adds', value: 1000})

您可以使用Aggregation Pipeline下面:

db.foo.aggregate([
  {$group:
     {_id:{provider:"$provider", timestamp:"$timestamp"}, 
      items:{$addToSet:{name:"$name",value:"$value"}}}
  }, 
  {$project:
     {tmp:{$arrayToObject: 
       {$zip:{inputs:["$items.name", "$items.value"]}}}}
  }, 
  {$addFields:
     {"tmp.provider":"$_id.provider", 
      "tmp.timestamp":"$_id.timestamp"}
  }, 
  {$replaceRoot:{newRoot:"$tmp"}
  }
]);

输出将是:

{
  "page_fan_adds": 1000,
  "provider": "Google",
  "timestamp": "1371798000000"
},
{
  "page_fan_adds": 2829,
  "page_fans": 1291509,
  "page_storytellers": 20871,
  "provider": "Facebook",
  "timestamp": "1371798000000"
}

另见 $group , $project , $addFields , $zip , 和 $replaceRoot