Unwind 3 arrays in MongoDB-CodePudding

MongoDB collection data with multiple arrays:

{
    "_id": ObjectId("61aa6bf1742b00f59b894eb7"),
    "first": ["abc", "def", "ghi"], 
    "last": ["rst", "uvw", "xyz"],
    "numb": ["12", "34", "56"]
}

Expected output where the data in the arrays should be in this format:

{
    "first": "abc",
    "last": "rst",
    "numb": "12"
},
{
    "first": "def",
    "last": "uvw",
    "numb": "34"
},
{
    "first": "ghi",
    "last": "xyz",
    "numb": "56"
}

CodePudding user response：

You can make use of $zip to "transpose" multiple arrays (as many as you'd like actually):

// {
//   first: ["abc", "def", "ghi"],
//   last:  ["rst", "uvw", "xyz"],
//   numb:  ["12", "34", "56"]
// }
db.collection.aggregate([

  { $project: { x: { $zip: { inputs: ["$first", "$last", "$numb"] } } } },
  // { x: [["abc", "rst", "12"], ["def", "uvw", "34"], ["ghi", "xyz", "56" ]] }

  { $unwind: "$x" },
  // { x: [ "abc", "rst", "12" ] }
  // { x: [ "def", "uvw", "34" ] }
  // { x: [ "ghi", "xyz", "56" ] }

  { $replaceWith: {
    $arrayToObject: { $zip: { inputs: [["first", "last", "numb"], "$x"] } }
  }} 
])
// { first: "abc", last: "rst", numb: "12" }
// { first: "def", last: "uvw", numb: "34" }
// { first: "ghi", last: "xyz", numb: "56" }

This:

zips the 3 arrays such that elements at the same index will get grouped into the same sub-array.
$unwinds (explodes/flattens) those sub-arrays.
transforms the resulting arrays into objects to fit your expected output format:
- by $zipping (again!) the keys we want to associate with the array's values (the keys: ["first", "last", "numb"] and the values: "$x")
- and $replaceWith the current document with the result of the $zip.

Note that prior to Mongo 4.2, you can use $replaceRoot instead of $replaceWith.

CodePudding user response：

Query

map on indexes to combine the same index members to 1 document
keeps the _id also to know from which document those came from and the index to sort after
for each index take the element from each array
unwind
sort by _id and index to get the results sorted like it was in the arrays

*indexes are computed using the biggest array, to be safe, in case you already know that all are the same size, you can replace the :
{"$max": [{"$size": "$first"}, {"$size": "$last"}, {"$size": "$numb"}]} with the size of any array for example(we need the biggest to work):
{"$size": "$first"}

Test code here

aggregate(
[{"$project": 
    {"data": 
      {"$map": 
        {"input": 
          {"$range": 
            [0,
              {"$max": 
                [{"$size": "$first"}, {"$size": "$last"}, {"$size": "$numb"}]}]},
          "in": 
          {"_id": "$_id",
           "index": "$$this",
           "first": {"$arrayElemAt": ["$first", "$$this"]},
           "last": {"$arrayElemAt": ["$last", "$$this"]},
           "numb": {"$arrayElemAt": ["$numb", "$$this"]}}}}}},
  {"$unwind": {"path": "$data"}},
  {"$replaceRoot": {"newRoot": "$data"}},
  {"$sort": {"_id": 1, "index": 1}},
  {"$unset": ["index"]}])