How to join two arrays into one array of objects

Question

In mongodb 3.4.5 aggregate, have below document:

{id:1, a:['x','y','z'], b:[2,3,4]}

And want to change it to

{id:1, field: [{a:'x', b:2}, {a:'y', b:3}, {a:'z', b:4}]}

How to do it in aggregate stage?

I'm tried to use $arrayToObject feature of mongodb 3.4.5, but unlucky...

Answer 1

You actually want $zip and $arrayElemAtwithin a $map here:

db.collection.aggregate([
  { "$project": {
     "field": { 
       "$map": {
         "input": { "$zip": { "inputs": [ "$a", "$b" ] } },
         "as": "el",
         "in": { 
           "a": { "$arrayElemAt": [ "$$el", 0 ] }, 
           "b": { "$arrayElemAt": [ "$$el", 1 ] }
         }
       }
     }
  }}
])  

Which produces:

{
        "field" : [
                {
                        "a" : "x",
                        "b" : 2
                },
                {
                        "a" : "y",
                        "b" : 3
                },
                {
                        "a" : "z",
                        "b" : 4
                }
        ]
}

The $zip does the "pairwise" and the $map processes each pair using $arrayElemAt to take each index for the new keys.

As an alternate to absolute indexes, you could use both of $arrayToObject and $objectToArray:

db.collection.aggregate([
  { "$project": {
     "field": {
       "$map": {
         "input": { "$objectToArray": { 
           "$arrayToObject": {
             "$zip": { "inputs": [ "$a", "$b" ] }
           }
         }},
         "as": "el",
         "in": { "a": "$$el.k", "b": "$$el.v" }
       }
     }
  }}
])  

Which does the same thing, but it's somewhat redundant since $zip works pairwise anyway, so we already know the results are "pairs" with 0 and 1 indexes.

---

Detailed Step by Step

First you want to $zip to create the pairs:

{ "$project": {
  "_id": 0,
  "field": { "$zip": { "inputs": [ "$a", "$b" ] } }
}}

Produces the "pairwise" from each array:

{ "field" : [ [ "x", 2 ], [ "y", 3 ], [ "z", 4 ] ] }

The $map from here should be self evident so instead we show the $arrayToObject step:

  { "$project": {
    "field": { "$arrayToObject": "$field" }
  }}

Which makes those array pairs into "keys" and "values":

{ "field" : { "x" : 2, "y" : 3, "z" : 4 } }

Then there is the transform with $objectToArray:

  { "$project": {
    "field": { "$objectToArray": "$field" }
  }}

Which makes an array of objects with keys "k" and "v":

{ 
  "field" : [
    { "k" : "x", "v" : 2 },
    { "k" : "y", "v" : 3 },
    { "k" : "z", "v" : 4 } 
  ]
}

Which is then passed to $map to rename the "keys":

  { "$project": {
    "field": {
      "$map": {
        "input": "$field",
         "as": "el",
         "in": { "a": "$$el.k", "b": "$$el.v" }
      }
    }
  }}

And gives the final output:

{ 
  "field" : [ 
    { "a" : "x", "b" : 2 },
    { "a" : "y", "b" : 3 },
    { "a" : "z", "b" : 4 }
  ]
}

As separate pipeline stages ( which you should not do ) but for the whole example:

db.collection.aggregate([
  { "$project": {
    "_id": 0,
    "field": { "$zip": { "inputs": [ "$a", "$b" ] } }
  }},
  { "$project": {
    "field": { "$arrayToObject": "$field" }
  }},
  { "$project": {
    "field": { "$objectToArray": "$field" }
  }},
  { "$project": {
    "field": {
      "$map": {
        "input": "$field",
         "as": "el",
         "in": { "a": "$$el.k", "b": "$$el.v" }
      }
    }
  }}
])

---

But I don't have MongoDB 3.4 or Greater

Then if you are not actually using the data for a continuing aggregation operation, it's very simple to do this in just about any language.

For example, iterating the cursor with JavaScript from the MongoDB shell:

db.collection.find().map(doc => {
  doc.field = doc.a.map((e,i) => [e, doc.b[i]]).map(e => ({ a: e[0], b: e[1] }));
  delete doc.a;
  delete doc.b;
  return doc;
})

Does exactly the same thing and is identical to the operation performed in the initial aggregation functions example.

Modern shell versions, or other JavaScript engines ( which you can even use with an older MongoDB versions) does this even cleaner:

db.collection.find().map(({ a, b }) => 
  ({ field: a.map((e,i) => [e, b[i]]).map(([a,b]) => ({ a, b })) })
)

Or frankly just..

db.collection.find().map(({ a, b }) =>
  ({ field: a.map((a,i) => ({ a, b: b[i] })) })
)

Since it's not really necessary to replicate all of the steps you would need to do with the aggregation framework as you can just transpose array elements by the matching indexes directly.