Mongo query assistance update array element with field from object

Working with Data
1

Need help with a query

{
  "number": "CC850732",
  "products": [
    {
      "product": {
        "plan_group": "Old",
        "price": 1999,
        "type": "plan",
    },
     {
      "product": {
        "price": 1999,
        "type": "other",
    },
    {
      "product": {
        "plan_group": "New",
        "price": 1999,
        "type": "plan",
    }
  ],

}

to convert it to this

{
  "number": "CC850732",
  "products": [
    {
      "product": {
        "plan_group": ["Old"],
        "price": 1999,
        "type": "plan",
    },
     {
      "product": {
        "price": 1999,
        "type": "other",
    },
    {
      "product": {
        "plan_group": [ "New" ],
        "price": 1999,
        "type": "plan",
    }
  ],

}

Tried this but it doesn’t work, any help would be appreciated

db.order2.updateMany(
    {},
    { $set:  { "products.$[elem].product.plan_group":  [ '$products.$[elem].product.plan_group' ] }  },
    { "arrayFilters" : [ { "elem.product.type" : "plan" } ] }
    );
2

It looks like you redacted you sample documents from real documents because I cannot insert it in my installation because it has some syntax errors.

Looks like some missing closing braces.

When you write

What do you mean exactly? It updates the wrong documents, does not transform the field to an array, does not set to the correct value.

Share the results that you get as it may help us pin-point the issue.

3

Hey sorry about that. the data is from some production env that I have and cant share all the fields.

From

{
  "number": "CC850732",
  "products": [
    {
      "product": {
        "plan_group": "Old",
        "price": 1999,
        "type": "plan"
      }
    },
    {
      "product": {
        "price": 1999,
        "type": "other"
      }
    },
    {
      "product": {
        "plan_group": "New",
        "price": 1999,
        "type": "plan"
      }
    }
  ],
  "_id": {
    "$oid": "61ac78f025a3421dbfff8496"
  }
}

To

{
    "number": "CC850732",
    "products": [{
        "product": {
            "plan_group": ["Old"],
            "price": 1999,
            "type": "plan"
        }
    }, {
        "product": {
            "price": 1999,
            "type": "other"
        }
    }, {
        "product": {
            "plan_group": ["New"],
            "price": 1999,
            "type": "plan"
        }
    }],
    "_id": {
        "$oid": "61ac78f025a3421dbfff8496"
    }
}

The query that I wrote doesn’t update any document.

4

I still have no idea on how to do it within a single updateMany() but I have a few tips.

Tip 1 - $redact

For

That is what I thought. Fortunately, you may use https://docs.mongodb.com/manual/reference/operator/aggregation/redact/ to produce documents you can share rather than manual edits.

Tip 2 - Don’t use {} query

When doing migration like updateMany({},…), I still want to specify a query that is more restrictive. In your case, I would use the query:

{ 'products.product.type': 'plan',
  'products.product.plan_group': { '$type': 'string' } }

This way I would not end up with [[["Old"]]] rather than ["Old"] if by mistake I ran the migration more than once.

Tip 3 - Update into a temporary field

Specially, while developing your migration, rather than overwriting your source field plan_group, update a temporary field like updated_plan_group. This way, if your update does not do what it should you still have your original data.

Tip 4 - Use aggregation and $out in temporary collection

Since aggregation is more powerful, I find it easier to write a pipeline that do the migration and simply $out in a temporary collection. May be $merge back into the source collection can be used. With $unwind, working with arrays become easier.

Tip 5 - Write a migration script

Sometimes it is easier to simply read the collection with the driver of your preferred languages and do a bulk write for the update.

Tip 6 - Do not migrate

One of my preferred method. I simply leave the data as is and I add a little hook that modify only the documents that I process in the application. The schema-less nature of MongoDB allows us to do that. In this case, I would only modify the plan_group of CC850732 when and only when I have to do something to CC850732. One big advantage is that the migration does not cause a usage spike on the servers because it occurs during a longer period of time. One is nice, in your case, is that MQL works well. The query
{...plan_group:"Old"}
will match
{...plan_group:"Old"}
as well as
{...plan_group:["Old"]}.

May be https://docs.mongodb.com/manual/reference/operator/aggregation/function/ can help you. But I am not familiar enough to help you with it.

5

Got it, I think. But it needs https://docs.mongodb.com/manual/tutorial/update-documents-with-aggregation-pipeline/ from 4.2.

It’s kind of complicated. It is going to be a update pipeline with a $set stage that uses $map. It will update the products array into a temporary updated_products array, but you could $map in itself. I like the temp. field as in Tip3.express

// Apply the mapping only to document that needs it (see Tip 2)
update_query =
{ "products.product.type" : "plan" ,
  "products.product.plan_group" : { "$type" : "string" }
}

// update_plan_group replace plan_group which is a string to an array but keeps the
// old price and type ($$item is the ***as*** of the $map)
update_plan_group =
{  "product" :
  { "plan_group" : [ "$$item.product.plan_group" ] ,
    "price" : "$$item.product.price" ,
    "type" : "$$item.product.plan"
  }
}

// We want to only alter the product when plan_group is a string.  You might want to
// check for type:plan in case you have string plan_group for something else than plan
plan_group_is_string ={ "$eq" : [ { "$type":"$$item.product.plan_group"} , "string" ] }

// We map plan_group only when plan_group is a string
conditional_mapping =
{ '$cond' :
  { 'if' : plan_group_is_string ,
    "then" : update_plan_group ,
    "else" : "$$item"
  }
}
// Map the products array. This is where we defined $$item used above.
map =
{  "$map" :
  { "input" : "$products" ,
    "as" : "item" ,
    "in" : conditional_mapping 
  }
}

// Let's do it in a temporary array named updated_products. See Tip 3.
// You will need to move updated_products into products in
// another step but you get the chance to verify the result before and make any
// without losing the original values
update_pipeline = [ { "$set" : { "mapped_products" : map } } ]
c.updateMany( updateQuery , update_pipeline )upda

// The complete update_pipeline is:
[ { '$set': 
     { mapped_products: 
        { '$map': 
           { input: '$products',
             as: 'item',
             in: 
              { '$cond': 
                 { if: { '$eq': [ '$$item.product.type', 'plan' ] },
                   then: 
                    { product: 
                       { plan_group: [ '$$item.product.plan_group' ],
                         price: '$$item.product.price',
                         type: '$$item.product.plan' } },
                   else: '$$item' } } } } } } ]

// The updated document is
{ _id: 1,
  number: 'CC850732',
  products: 
   [ { product: { plan_group: 'Old', price: 1999, type: 'plan' } },
     { product: { price: 1999, type: 'other' } },
     { product: { plan_group: 'New', price: 1999, type: 'plan' } } ],
  updated_products: 
   [ { product: { plan_group: [ 'Old' ], price: 1999 } },
     { product: { price: 1999, type: 'other' } },
     { product: { plan_group: [ 'New' ], price: 1999 } } ] }

Enjoy!