MongoDB Aggregation Framework - Dynamic Field Rename

Question

I am finding the MongoDB aggregation framework to be extremely powerful - it seems like a good option to flatten out an object. My schema uses a an array of sub objects in an array called materials. The number of materials is variable, but a specific field category will be unique across objects in the array. I would like to use the aggregation framework to flatten the structure and dynamically rename the fields based on the value of the category field. I could not find an easy way to accomplish this using a $project along with $cond. Is there a way?

The reason for the array of material objects is to allow simple searching:

e.g. { 'materials.name' : 'XYZ' } pulls back any document where "XYZ" is found.

E.g. of before and after document

{
"_id" : ObjectId("123456"),
"materials" : [
    {
        "name" : "XYZ",
        "type" : "Red",
        ...
        "category" : "A"
    },
    {
        "name" : "ZYX",
        "type" : "Blue",
        ...
        "category" : "B"
    }]
}

to

{
"material_A_name" : "XYZ",
"material_A_type" : "Red",
...
"material_B_name" : "ZYX",
"material_B_type" : "Blue",
...
}

Thanks, updated - it was an error from creating the mock-up in my editor — user2234151
– user2234151, Commented Apr 2, 2013 at 2:27
Ok, I see what you're trying to do now. There's not much support for dynamically generated keys in the agg framework so I don't see how you could do this with that. You may have to just post process the result in code to do it. — JohnnyHK
– JohnnyHK, Commented Apr 2, 2013 at 2:55
Thanks for taking a look. I do post process in python code now, but I was looking for an elegant/elegant way to create create an API to query mongo from another app that would query to view data in tabular form. e.g. the API would take connection details and an aggregation json string pipeline that produces flattened results to generate a tabular report. — user2234151
– user2234151, Commented Apr 2, 2013 at 3:21

Asya Kamsky · Accepted Answer · 2013-09-01 20:46:28Z

6

There is a request for something like this in jira https://jira.mongodb.org/browse/SERVER-5947 - vote it up if you would like to have this feature.

Meanwhile, there is a work-around if you know up front what the possible values of the keys will be (i.e. all unique values of "category") and I have some sample code on it on my blog.

answered Sep 1, 2013 at 20:46

Asya Kamsky

42.4k5 gold badges113 silver badges133 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Asya Kamsky Over a year ago

Note that ability to transpose keys to values and vice versa has been available since MongoDB version 3.4.4.

turivishal · Accepted Answer · 2020-12-11 06:17:39Z

This would be useful from MongoDB v4.4,

$map to iterate loop of materials array
$map to iterate loop of name and type fields after converting to array using $objectToArray, concat your key fields requirement as per fields and value using $concat,
back to first $map convert returned result from second $map from array to object using $arrayToObject
$unwind deconstruct materials array
$group by null and merge materials object to one object
$replaceRoot to replace object in root

db.collection.aggregate([
  {
    $project: {
      materials: {
        $map: {
          input: "$materials",
          as: "m",
          in: {
            $arrayToObject: [
              {
                $map: {
                  input: {
                    $objectToArray: {
                      name: "$$m.name",
                      type: "$$m.type"
                    }
                  },
                  in: {
                    k: { $concat: ["material", "_", "$$m.category", "_", "$$this.k"] },
                    v: "$$this.v"
                  }
                }
              }
            ]
          }
        }
      }
    }
  },
  { $unwind: "$materials" },
  {
    $group: {
      _id: null,
      materials: { $mergeObjects: "$materials" }
    }
  },
  { $replaceRoot: { newRoot: "$materials" } }
])

Playground

Collectives™ on Stack Overflow

MongoDB Aggregation Framework - Dynamic Field Rename

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related