How create a new array field with the aggregate framework

Question

I'm starting to use mongoDb and I'm stuck with a simple use case.

Let's say I've got a collection 'aCollection' with entries such as this:

{ 
    _id: ObjectId(123),
    lat: 48,56623,
    long: 2,56332
}

and I want to create a new collection with entries like this:

{ 
    _id: ObjectId(123),
    lat: 48,56623,
    long: 2,56332,
    geometry : { 
        type: "Point",
        coordinates: [48,56623, 2,56332]
    }
}

I thought about the aggregation framework:

db.aCollection.aggregate([{$project: { 
    _id: 1,
    lat: 1,
    long: 1,
    geometry: { 
        type: {$concat: ["Point"]},
        coordinates: ["$lat", "$long"]
    }
}}])

But it ain't working and I get this exception:

"exception: disallowed field type Array in object expression (at 'coordinates')"

The following aggregation is working but it doesn't produce the expected result:

db.aCollection.aggregate([{$project: { 
    _id: 1,
    lat: 1,
    long: 1,
    geometry: { 
        type: {$concat: ["Point"]},
        coordinates: "$lat"
    }
}}])

How would you proceed to create this collection 1) with the aggregation framework 2) without the aggregation framework

Thank you

Answer 1

In Modern MongoDB releases the most efficient way is to simply notate the array using the existing document properties. Direct notation of arrays was introduced in MongoDB 3.2:

db.collection.aggregate([
  { "$project": {
    "lat": 1,
    "long": 1,
    "geometry": {
      "type": { "$literal": "Point" },
      "coordinates": [ "$lat", "$long" ]
    }
  }},
  { "$out": "newcollection" }
])

Or even using $addFields to simply "append" the new property to the documents:

db.collection.aggregate([
  { "$addFields": {
    "geometry": {
      "type": { "$literal": "Point" },
      "coordinates": [ "$lat", "$long" ]
    }
  }},
  { "$out": "newcollection" }
])

---

If you are using MongoDB 2.6 and above you can do this with the aggregation framework and avoid looping results in your client program in order to create a new collection.

The main feature here that help you are the $out operator for sending the output to a new collection. But also being a little clever in order to create the array that you need.

db.collection.aggregate([
    { "$project": {
        "lat": 1,
        "long": 1,
        "type": { "$literal": ["lat","long"] }
    }},
    { "$unwind": "$type" },
    { "$group": {
        "_id": "$_id",
        "lat": { "$first": "$lat" },
        "long": { "$first": "$long" },
        "coordinates": {
            "$push": {
                "$cond": [
                    { "$eq": [ "$type", "lat" ] },
                    "$lat",
                    "$long"
                ]
            }
        }
    }},
    { "$project": {
        "lat": 1,
        "long": 1,
        "geometry": { 
            "type": { "$literal": "Point" },
            "coordinates": "$coordinates"
        }
    }},
    { "$out": "newcollection" }
])

So this makes use of the $literal operator in order to specify a new array at the head of the pipeline. This operator will put content in the document property exactly how it is supplied. So no variable substitutions are allowed, hence "literal".

In order to create the "coordintes" array, we simply unwind that first array which essentially creates two of every document with a different value in "type". This is then used in the $group stage to conditionally $push either the "$lat" or "$long" value onto that array.

Finally use $project again to finalize the document structure and then $out sends all output to the new collection.

---

Note that this only makes sense if your intention is to create a new collection and avoid sending traffic "over the wire". This could not be used purely within the aggregation framework to re-shape your document with the intent to then do a "geo-spatial" query in that same aggregation pipeline as "geo-spatial" queries will only work when actually indexed on a collection.

So this may help you create a new collection as you want to, but at least it serves as example ( or two examples actually ) of how to create an array's out of different values with the aggregation framework.

Answer 2

You don't need the aggregation feature for this. A find, forEach, and insert is one potential approach:

db.aCollection.find().forEach( function(myDoc) {
  myDoc.geometry = {type: "Point", coordinates: [myDoc.lat, myDoc.long]};
  db.newCollection.insert(myDoc);
});

It calls an individual insert for each document, but is quick and dirty if you have a small collection.