MongoDB Aggregator turn array into root keys values

Question

My goal is to get the most recent updated key and re-create a user object with those updates.

I have the following data:

[{"_id":"5ea46dcec84e0bb1aaf510d6","createdAt":{"$numberLong":"1587834215451"}},
{"_id":{"$oid":"5ea46e88c84e0bb1aaf510d7"},"documentId":"5ea46dcec84e0bb1aaf510d6","key":"name","value":"Jim Jenkins","type":"update","createdAt":{"$numberLong":"1587834215451"}},
{"_id":{"$oid":"5ea46e88c84e0bb1aaf510d8"},"documentId":"5ea46dcec84e0bb1aaf510d6","key":"address.line1","value":"5638 Jackson Avenue","createdAt":{"$numberLong":"1587834215451"},"type":"update"},
{"_id":{"$oid":"5ea4711bc84e0bb1aaf510d9"},"documentId":"5ea46dcec84e0bb1aaf510d6","key":"name","value":"Jim Jenkins the third","type":"update","createdAt":{"$numberLong":"1587834213451"}},
{"_id":{"$oid":"5ea47298c84e0bb1aaf510da"},"documentId":"5ea46dcec84e0bb1aaf510d6","key":"name","value":"Bryan Jenkins","type":"update","createdAt":{"$numberLong":"1587835530397"}}]

And a pipeline that looks like this:

[{
    $match: {
        type: "update",
        documentId: "5ea46dcec84e0bb1aaf510d6"
    }
}, {
    $sort: {
        "createdAt": -1
    }
}, {
    $group: {
        "_id": {
            "referenceId": "$referenceId",
            "key": "$key"
        },
        "top": {
            "$first": "$$ROOT"
        }
    }
}, {
    $group: {
        "_id": "$top.documentId",
        "key-values": {
            "$push": {
                "k": "$_id.key",
                "v": "$top.value"
            }
        },
        "updatedAt": {
            "$first": "$top.createdAt"
        }
    }
}, {
    $lookup: {
        "from": "users",
        "localField": "_id",
        "foreignField": "_id",
        "as": "reference"
    }
}]

I have the following key-values in the pipeline:

{
    "key-values": [
        { k:"name", v:"Bryan Jenkins" },
        { k:"address.line1", v:"My Address"}
    ]
}

What I'd like to end up with is:

{
    "_id": "5ea46dcec84e0bb1aaf510d6"
    "name": "Bryan Jenkins",
    "address-line1": "My Address"
    "updatedAt": 1587835530397
}


//Or even better:
{
    "_id": "5ea46dcec84e0bb1aaf510d6"
    "name": "Bryan Jenkins",
    "address": {
        "line1": "My Address"
    },
    "updatedAt": 1587835530397
}

I can't for the life of me figure out how to replace the root by looping through the key-values. I've tried a series of $replaceRoot with $mergeObjects but can't seem to get it done because the key's in the key-values are completely unknown.

Does anyone have any suggestions?

Can you add the sample document and the pipeline stages you have tried so far? — Vijay Rajpurohit
– Vijay Rajpurohit, Commented Apr 25, 2020 at 18:17
"key-values":{"$push":{"k":"$_id.key","v":"$top.value"}} -> with that I would say you might end up with either { k:"name", v:"Bryan Jenkins" } or { k:"address.line1", v:"My Address"} - So how did you get both pushed into an array ? Please edit question with more details.. — whoami - fakeFaceTrueSoul
– whoami - fakeFaceTrueSoul, Commented Apr 25, 2020 at 18:34

whoami - fakeFaceTrueSoul · Accepted Answer · 2020-04-25 20:41:02Z

2

You can replace your second $group stage with :

  {
    $group: {
      "_id": "$top.documentId",
      "key-values": {
        $mergeObjects: {
          $arrayToObject: [
            [
              {
                "k": "$_id.key",
                "v": "$top.value"
              }
            ]
          ]
        }
      },
      "updatedAt": { "$first": "$top.createdAt" }
    }
  },
  {
    $replaceRoot: {
      newRoot: {
        $mergeObjects: [
          "$key-values",
          {
            _id: "$_id"
          },
          {
            updatedAt: "$updatedAt"
          }
        ]
      }
    }
  }

Just in case if you've more fields(not just _id & updatedAt) in root document which needs to be merge with key-values then :

  {
    $group: {
      "_id": "$top.documentId",
      "key-values": {
        $mergeObjects: {
          $arrayToObject: [
            [
              {
                "k": "$_id.key",
                "v": "$top.value"
              }
            ]
          ]
        }
      },
      "updatedAt": { "$first": "$top.createdAt" }
    }
  },
  {
    $replaceRoot: { newRoot: { $mergeObjects: [ "$key-values", "$$ROOT" ] } }
  },
  {
    $project: { "key-values": 0 }
  }

Test : mongoplayground

Ref : aggregation

answered Apr 25, 2020 at 20:41

whoami - fakeFaceTrueSoul

18.1k6 gold badges35 silver badges51 bronze badges

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

1 Comment

bryan Over a year ago

Thank you so much! Makes perfect sense

Collectives™ on Stack Overflow

MongoDB Aggregator turn array into root keys values

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related