MongoDB pivot on Key value pair

[Shrikant Kulkarni]

I am new bee in MongoDB, I have collection with key value pairs like below..
 {
      "restaurentid" : NumberInt("1"),
      "Properties" : [
       {
        "Key" : "A",
        "Value" : NumberInt("25")
       },
       {
        "Key" : "B",
        "Value" : "StringValue"
       },
      
    
          {
        "Key" : "C",
        "Value" : ISODate("2017-02-09")
       }
      
      ]
     }
I am looking result set as
{
 "restaurentid" : NumberInt("1"),
 "A" : NumberInt("25"),
 "B" : "StringValue",
 "C" : ISODate("2017-02-09")
}

how do I get it without hardcoding "A", "B", "C" in the aggregation pipeline. My key value pairs are going to get bigger and is variable for given id

[Wan Bachtiar]

Hi Shrikant,

Questions related to the use of MongoDB (i.e. your question) are better sent to mongodb-user group. This group (mongodb-dev) is for discussion for/by/about developers/development/code of MongoDB.

how do I get it without hardcoding “A”, “B”, “C” in the aggregation pipeline. My key value pairs are going to get bigger and is variable for given id

You can utilise new aggregation operator $arrayToObject (SERVER-18794) which currently is in MongoDB development version v3.5.5, and soon to be back-ported to v3.4.x.

For example, you can restructure your schema:

{
  "restaurantid" : NumberInt("1"),
  "Properties" : [
    { "k" : "A", "v" : NumberInt("25") },
    { "k" : "B", "v" : "StringValue" },
    { "k" : "C", "v" : ISODate("2017-02-09") }
  ]
}

Then you can utilise example aggregation pipeline below:

db.collection.aggregate(
[
    {$project:{"tmp":{$arrayToObject:"$Properties"}, "restaurantid":"$resturantid"}}, 
    {$addFields:{"tmp.restaurantid":"$restaurantid"}}, 
    {$replaceRoot:{newRoot:"$tmp"}}
]);

See also $replaceRoot and $addFields. Depending on your use case, you could also take advantage of MongoDB flexible schema and reconsider your document model.

If you have further questions, please post a new discussion on mongodb-user group.

I am new bee in MongoDB

I’d recommend to enrol in one of the free online courses at MongoDB University especially M101 or M102 to find out more about MongoDB.

Regards,

Wan.

​