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
– chaliasos, Commented Jul 1, 2013 at 9: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
– Sammaye, Commented Jul 1, 2013 at 12:25
For Dynamic filed names check MongoDB Aggregation Framework - Dynamic Field Rename — afarag
– afarag, Commented Feb 24, 2021 at 12:09

Wan B. · Accepted Answer · 2018-01-11 00:24:11Z

13

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

edited Jan 11, 2018 at 0:24

answered Apr 26, 2017 at 3:19

Wan B.

18.9k4 gold badges60 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Jakub Keller Over a year ago

This is a better answer.

tonysepia Over a year ago

This is Jedi-level aggreagtion framework mastery. Thank you for this!

Nate Anderson Over a year ago

Thanks! Great answer. Looks like we can skip the $zip if we $addToSet and create the format required by $arrayToObject. I used the {k: "$name", v: "$value"} format. Example here.

Pierre-Louis Gottfrois · Accepted Answer · 2013-07-01 09:24:29Z

11

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 1, 2013 at 9:24

answered Jul 1, 2013 at 9:19

Pierre-Louis Gottfrois

17.6k9 gold badges49 silver badges73 bronze badges

4 Comments

maverickm Over a year ago

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.

Pierre-Louis Gottfrois Over a year ago

You welcome. Please consider at least up voting if you think this do not answer completely your question ;) Good luck.

maverickm Over a year ago

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!!!

Pierre-Louis Gottfrois Over a year ago

Just thought about it, using map_reduce could be a good idea if you need to generate daily charts

Collectives™ on Stack Overflow

Pivot rows to columns in MongoDB

2 Answers 2

3 Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related