Pivot rows to columns in MongoDB

Question

The relevant question is Efficiently convert rows to columns in sql server. But the answer is specific to SQL.

I want the same result i.e. pivot row to column without aggregating anything (as of now) in MongoDB.

The collection looks something as below. These are statistics of facebook page properties:


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"
}

Expected Output JSON:

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

First of all, mongoDb has documents with a json format. Post the json you have and the json you want to get back so we understand it better. Second, why do you want that?? — chaliasos, Jul 01 '13 at 09:14
This is something definitely better done on the client side, I would not use the answer below to do this, it would not be very long until you get scaling problems with such queries. — Sammaye, Jul 01 '13 at 12:25
For Dynamic filed names check [MongoDB Aggregation Framework - Dynamic Field Rename](https://stackoverflow.com/a/65246631/4732434) — afarag, Feb 24 '21 at 12:09

Wan B. · Answer 1 · 2018-01-11T00:24:11.693

Now, you can utilise new aggregation operator $arrayToObject to pivot MongoDB keys. This operator is available in MongoDB v3.4.4+

For example, given an example data of:

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})

You can utilise Aggregation Pipeline below:

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"}
  }
]);

The output would be:

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

See also $group, $project, $addFields, $zip, and $replaceRoot

This is Jedi-level aggreagtion framework mastery. Thank you for this! — tonysepia, Jul 31 '21 at 03:04

Pierre-Louis Gottfrois · Answer 2 · 2013-07-01T09:24:29.567

10

I have done something like this using aggregation. Could this help ?

db.foo.insert({ timestamp: '1371798000000', propName: 'page_fans', propValue: 100})
db.foo.insert({ timestamp: '1371798000000', propName: 'page_posts', propValue: 25})
db.foo.insert({ timestamp: '1371798000000', propName: 'page_stories', propValue: 50})

db.foo.aggregate({ $group: { _id: '$timestamp', result: { $push: { 'propName': '$propName', 'propValue': '$propValue' } }}})

{
    "result" : [
        {
            "_id" : "1371798000000",
            "result" : [
                {
                    "propName" : "page_fans",
                    "propValue" : 100
                },
                {
                    "propName" : "page_posts",
                    "propValue" : 50
                },
                {
                    "propName" : "page_stories",
                    "propValue" : 25
                }
            ]
        }
    ],
    "ok" : 1
}

You may want to use $sum operator along the way. See here

edited Jul 01 '13 at 09:24

answered Jul 01 '13 at 09:19

Pierre-Louis Gottfrois

17,561
8
47
71

1

Looks interesting. This was one of my ideas, but problem is I need to generate daily charts based on specific properties and their values. Obtaining a result like this would still require some additional processing in code which I want to avoid. I'll see though if I can fit this somewhere. Thanks for replying. – maverickm Jul 01 '13 at 09:55
You welcome. Please consider at least up voting if you think this do not answer completely your question ;) Good luck. – Pierre-Louis Gottfrois Jul 01 '13 at 10:19
Sorry, did that now. I think I can utilize above by getting the output and formatting in server side service and returning result to client in format that can be utilized for charting. Will wait if someone has better alternative to offer. Thanks again!!! – maverickm Jul 01 '13 at 10:24
1

Just thought about it, using `map_reduce` could be a good idea if you need to generate daily charts – Pierre-Louis Gottfrois Jul 01 '13 at 11:21

Pivot rows to columns in MongoDB

2 Answers2

Linked