Create MongoDB fields with names based on sub-document values using aggregation pipeline?

Question

Given a MongoDB collection with the following document structure:

{
    array_of_subdocs:
         [
             {
                 animal: "cat",
                 count: 10
             },
             {
                 animal: "dog",
                 count: 20
             },

             ...
         ]
}

where each document contains an array of sub-documents, I want transform the collection into documents of the structure:

{
    cat: { count: 10 },
    dog: { count: 20 },
    ...
}

where each sub-document is now the value of a new field in the main document named after one of the values within the sub-document (in the example, the values of the animal field is used to create the name of the new fields, i.e. cat and dog).

I know how to do this with eval with a Javascript snippet. It's slow. My question is: how can this be done using the aggregation pipeline?

The aggregation framework cannot output a "key" name from a "value". You need JavaScript evaluation such as mapReduce, and that will alter your output. Do it in post processing. But it is bad practice anyway to use "data" for your keys. { "animal": "cat", "count": 10 }, { "animal": "dog", "count": 20 } is much more natural since you don't have to inspect keys but work with the current value. Or would that be acceptable but you just don't know how to get it out of the array as individual documents? — Neil Lunn
@NeilLunn - thanks Neil. Yeah, I've got it working with mapReduce; hoping to find a way with aggregation for max performance. The animal example here is artificial, for my actual application there are some advantages to using the values as key names. — Ghopper21
The main point is values as key names is bad practice. That sort of output is not supported by the aggregation framework. — Neil Lunn

Tolga Evcimen Tolga Evcimen · Accepted Answer · 2017-01-13T11:57:28

According to this feature request and its solution, the new feature will be added for this functionality - Function called arrayToObject.

db.foo.aggregate([
  {$project: {
    array_of_subdocs: {
      $arrayToObject: {
        $map: {
          input: "$array_of_subdocs",
          as: "pair",
          in: ["$$pair.animal", "$$pair.count"]
        }
      }
    }
  }}
])

But at the moment, no solution. I suggest you to change your data structure. As far as I see there are many feature requests labeled as Major but not done for years.

Create MongoDB fields with names based on sub-document values using aggregation pipeline?

1 Answers