How to flatten objects in mongodb automatically

Question

I have some data that needs to be flattened, which means go from this format:

"user": {
      "id": "AEC77ACD-02D3-0057-842F-DC23D6312389",
      "name": "Eliza Chukiua",
      "email": "eliza.chukiua@booking.com",
      "group": "SSO",
      "externalId": "eliza.chukiua@booking.com"

To this format:

    "user_email": "eliza.chukiua@booking.com",
    "user_externalId": "eliza.chukiua@booking.com",
    "user_group": "SSO",
    "user_id": "AEC77ACD-02D3-0057-842F-DC23D6312389",
    "user_name": "Eliza Chukiua"

To do this, the code is

db.collection.update({},
[
  {
    $set: {
      user_id: "$user.id",
      user_email: "$user.email",
      user_name: "$user.name",
      user_externalId: "$user.externalId",
      user_group: "$user.group"
    }
  },
  {
    $unset: "user"
  }
],
{
  multi: true
})

The problem is that I have more fields I have to flatten and they not always are on the data set, which means I need a way to flatten all the crap fields, even if I don't know their names. That is because new crappy fields can be created and I have no way to know every single crap field name.

What I am using to test it is this mongodb playground https://mongoplayground.net/p/KXJWzKIUDob, feel free to play around.

How do I flatten every crappy field automatically, without having to $set every single one of them?

Takis · Answer 1 · 2022-11-07T14:44:37.237

Query1

general case that you dont know which fields have as value documents and you dont know the fields in the nested document
same as Query2, but doesn't require to know which fields needs flattening, you dont give the "user" for example
it does nested reduce, to reduce the $$ROOT also

*works for deep nesting also, but you have to repeat it, like do one update for level 1 nesting, second update for level2 nesting etc i will produce field1_field2_field3 : value etc

Playmongo

aggregate(
[{"$set": {"root": {"$objectToArray": "$$ROOT"}}},
 {"$replaceRoot": 
   {"newRoot": 
     {"$reduce": 
       {"input": "$root",
        "initialValue": {},
        "in": 
         {"$mergeObjects": 
           ["$$value",
             {"$cond": 
               [{"$not": [{"$eq": [{"$type": "$$this.v"}, "object"]}]},
                 {"$arrayToObject": [[["$$this.k", "$$this.v"]]]},
                 {"$let": 
                   {"vars": 
                     {"nested_field": "$$this.k", "nested_value": "$$this.v"},
                    "in": 
                     {"$reduce": 
                       {"input": {"$objectToArray": "$$this.v"},
                        "initialValue": {},
                        "in": 
                         {"$mergeObjects": 
                           ["$$value",
                             {"$arrayToObject": 
                               [[[{"$concat": ["$$nested_field", "_", "$$this.k"]},
                                    "$$this.v"]]]}]}}}}}]}]}}}}}])

Query2

the above is big query but if you know the names of the fields that need flattening for example "user" field it can be much smaller
you can repeat the code for any other field, or genetate it dynamically for all fields
makes user an array to allow to process the field names as values
reduces to make them document again, after it concats the parent field name string to the subfield name
finally add those fields into root and $unset the field "user"

Playmongo

aggregate(
[{"$set": {"user": {"$objectToArray": "$user"}}},
 {"$set": 
   {"user": 
     {"$reduce": 
       {"input": "$user",
        "initialValue": {},
        "in": 
         {"$mergeObjects": 
           ["$$value",
             {"$arrayToObject": 
               [[[{"$concat": ["user_", "$$this.k"]}, "$$this.v"]]]}]}}}}},
 {"$replaceRoot": {"newRoot": {"$mergeObjects": ["$user", "$$ROOT"]}}},
 {"$unset": ["user"]}])

As Update
If you need this pipeline in update you can use it like updateMany({},[...stages...])

How to flatten objects in mongodb automatically

1 Answers1