Pivot mongo data in aggregation pipeline after $addToSet: one set becomes keys, one set values

Question

I have a pipeline so far that looks like this:

db.getCollection("Members").aggregate(
[
    { 
        "$match" : {
            "member.MemberInfo.BusinessUnitCode" : "20"
        }
    }, 
    { 
        "$group" : {
            "_id" : "$_id", 
            "items" : {
                "$addToSet" : {
                    "Type" : "$member.MemberIdentifications.MemberIdentification.IdentificationType", 
                    "Value" : "$member.MemberIdentifications.MemberIdentification.Identifier"
                }
            }
        }
    }}

And I have results that are:

{ 
"_id" : ObjectId("53ad60c1600f5b241c693cbd"), 
"items" : [
    {
        "Type" : [
            "Medicaid ID", 
            "Medicare ID", 
            "Amisys ID", 
            "Social Security Number", 
            "Market Prominence ID", 
            "MBI", 
            "MPI"
        ], 
        "Value" : [
            "221075***", 
            "450807099M", 
            "C0004125301", 
            "45*******", 
            "N00020269104", 
            "3K13EA8EY99", 
            "17296217"
        ]
    }
]}

What I want is this:

{
"_id" : ObjectId("53ad60c1600f5b241c693cbd"),
{"Medicaid ID": "221075501",
 "Medicare ID": "450807099M",
 "Amisys ID": "C0004125301",
 "Social Security Number": "45*******",
 "Market Prominence ID": "N00020269104",
 "MBI": "3K13EA8EY99",
 "MPI": "17296217"
 }
}

In table view this would look like (obviously with many more records):

_id                         Medicaid ID Medicare ID Amisys ID   Social Security Number  Market 
53ad60c1600f5b241c693cbd    221075***   450807099M  C0004125301 45*******               N00020269104

I'm not sure what next steps to take. I've looked at a similar question about pivoting data in mongo to make rows columns here, but Its a different case and it doesnt really apply to mine. What Im basically doing is pivoting from the two sets I created so one set become the kets and one set becomes the values of one new document

Valijon · Accepted Answer · 2020-01-08T23:01:00.127

2

You may solve it as you would do with for loop. For your aggregation, add 3 steps.

Explanation

$unwind: "$items" will create single document for each item.
$range: [0,{$size: "$items.Type"},1] returns [0,1,2,3,4,5,6]
We create array with this structure [{ k:"Type[i]", v:"Value[i]" }]
If we use $arrayToObject + $replaceRoot we get desired result

db.Members.aggregate([
  {
    $unwind: "$items"
  },
  {
    $project: {
      _id: 1,
      data: {
        $map: {
          input: {
            $range: [
              0,
              {
                $size: "$items.Type"
              },
              1
            ]
          },
          in: {
            k: {
              $arrayElemAt: [
                "$items.Type",
                "$$this"
              ]
            },
            v: {
              $arrayElemAt: [
                "$items.Value",
                "$$this"
              ]
            }
          }
        }
      }
    }
  },
  {
    $replaceRoot: {
      newRoot: {
        $arrayToObject: {
          $concatArrays: [
            [
              {
                k: "_id",
                v: "$_id"
              }
            ],
            "$data"
          ]
        }
      }
    }
  }
])

MongoPlayground

NOTE: You can get desired result with $unwind explained here

EDIT:

Try to change:

db.Members.aggregate([
{
    "$match": {
      "member.MemberInfo.BusinessUnitCode": "20"
    }
  },
  {
    "$group": {
      "_id": "$_id",
      "items": {
        "$push": {
          "k": "$member.MemberIdentifications.MemberIdentification.IdentificationType",
          "v": "$member.MemberIdentifications.MemberIdentification.Identifier"
        }
      }
    }
  },
  {
    $replaceRoot: {
      newRoot: {
        $arrayToObject: {
          $concatArrays: [
            [
              {
                k: "_id",
                v: "$_id"
              }
            ],
            "$items"
          ]
        }
      }
    }
  }
])

edited Jan 08 '20 at 23:01

answered Jan 08 '20 at 22:09

Valijon

12,667
4
34
67

I get error: `"$arrayElemAt's first argument must be an array, but is string"` – Ben Smith Jan 08 '20 at 22:50
1

Can you post data where aggregation fails? also, I've changed your code to get desired result in 3 steps – Valijon Jan 08 '20 at 23:01
No It didnt give an output from that error run. Currently running your new code but its taking a while – Ben Smith Jan 08 '20 at 23:20
Sorry but this still does not give the desired results. It gives me my same original results except now 'Type' is just called 'k' and Value is called 'v' – Ben Smith Jan 09 '20 at 15:00
Did you put `$replaceRoot` as last stage? – Valijon Jan 09 '20 at 15:04
Sorry I forgot, now I have all I you said but I get error `'$arrayToObject requires an object with keys 'k' and 'v', where the value of 'k' must be of type string. Found type: array'`. Can you explain why '_id' is passed to "k" and "$_id" is passed to "v" ? – Ben Smith Jan 09 '20 at 15:49
Try yourself [https://mongoplayground.net/p/R2RABPY5_ZA](https://mongoplayground.net/p/R2RABPY5_ZA). [$replaceRoot](https://docs.mongodb.com/manual/reference/operator/aggregation/replaceRoot/) serves to change output result. If your data are stored inside {items:{col1:val1, col2:val2, ...}}, to take out from `items`, you need to `$replaceRoot`. In your case, you data is grouped, so it doesn't have `items:{col1:val1, ...}` structure. We use `$arrayToObject` to transform into `key:value` pair – Valijon Jan 09 '20 at 16:29
But my error message says "k" must be of type string, but "k", and "v" is an array. – Ben Smith Jan 09 '20 at 17:09
Post your data, can't help you blindly – Valijon Jan 09 '20 at 17:11
Post 1-2 `Members` raw documents please – Valijon Jan 09 '20 at 17:18
I cant its too long for a comment. It looks the exact same as my original results but just "Type" is replaced by 'k" and "Value" with "v". that is after the $puch operator in the group stage. But then I get that error in $replaceRoot stage, $arrayToObject operator – Ben Smith Jan 09 '20 at 17:37
Please try my first solution, ignore second solution – Valijon Jan 09 '20 at 17:42
That's it! Thanks! – Ben Smith Jan 09 '20 at 18:38

Pivot mongo data in aggregation pipeline after $addToSet: one set becomes keys, one set values

1 Answers1

Explanation