MongoDB : Insert and Update in an array

Question

I have "_id" and "ProductLot". How can I Update Qty "0" to "1 " in the Lot array if ProductLot if present or append a new Lot element if it is not present?

"_id" : ObjectId("5462e44c599e5c6c1300000a"),
"LocationName" : "Putaway", 
"Owner" : "", 
"Status" : "1", 
"Scrap" : "0", 
"Lot" : [{      
     "Qty" :"6",   
     "ProductLot" : ObjectId("5462dbd9599e5c200e000000"), 
     "Product" : ObjectId("543ca7be4cf59d400c000004"),       
     "Movement" :[ {"OriginId" : "266",Qty:2,Type:"DN"},
           {"OriginId" : "267" , Qty:1 , Type:"DN"},
           {"OriginId" : "2" , Qty:3 , Type:"IM"},

        ]
    },  
    {      
     "Qty" :"0",   
     "ProductLot" : ObjectId("5462dbd9599e5c200e000003"),  
     "Product" : ObjectId("543ca7be4cf59d400c000004"),    
     "Movement" :[ {"OriginId" : "266",Qty:2,Type:"DN"},
           {"OriginId" : "267" , Qty:1 , Type:"DN"},
           {"OriginId" : "2" , Qty:-3 , Type:"IM"},
        ]

    }] 
}

EG: I have "ProductLot" : ObjectId("5462dbd9599e5c200e000000") present in array so it should update qty 0 to 1; however, "ProductLot" : ObjectId("5462dbd9599e5c200e00000a") is not available in array so that should append a new element to the array.

PHP code, which is not updating creating appending array at every time:

            $inventoryId = new \MongoId($_POST["inventoryid"]);
            $productLotId = new \MongoId($invlotid);
            $originId = $_POST['id'];
            //$lotcontent = [ /* whatever this looks like */ ];

            $lotcontent = array(
                     'Qty' => $invqty,
                     'ProductLot' => new \MongoId($invlotid),
                     'Product'=>$invproduct,
                     'Movement'=> array(
                            array(
                            'OriginId' => $_POST['id'],
                            'Qty' => $invqty,
                            'Type'=> 'DN',              
                            ),  )                   
                     );
            $invcolname = 'Inventory';
            $result = $mongo->$dbname->$invcolname->update(
            // Match an inventory without the specific ProductLot/OriginId element
            array(
                '_id' => $inventoryId,
                'Lot' =>    array(
                    '$not' =>   array(
                        '$elemMatch' => array(
                            'ProductLot' => $productLotId,
                            //'OriginId' => $originId,
                        ),
                    ),
                ),
            ),
            // Append a new element to the Lot array field
            array('$push' => array( 'Lot' => $lotcontent ))
            );

            $movementcontent =  array(
                            'OriginId' => $_POST['id'],
                            'Qty' => $invqty,
                            'Type'=> 'DN',              
                            );

            $result = $mongo->$dbname->$invcolname->update(
            // Match an inventory without the specific ProductLot/OriginId element
            array(
                '_id' => $inventoryId,
                'Lot' =>    array(
                    //'$not' =>     array(
                        '$elemMatch' => array(
                            'ProductLot' => $productLotId,                              
                            'Movement'=>array(
                                '$not' =>   array(
                                    '$elemMatch' => array(
                                        'OriginId' => $originId,
                                    )
                                )
                            )                               
                        ),
                   // ),
                ),
            ),
            // Append a new element to the Lot.Movement array field
            array('$push' => array( 'Lot.$.Movement' => $movementcontent ))
            );


            $result = $mongo->$dbname->$invcolname->update(
                // Match an inventory with a specific ProductLot/OriginId element
              array(
                    '_id' => $inventoryId,
                    'Lot' => array(
                        '$elemMatch' => array(
                            'ProductLot' => $productLotId,
                            //'OriginId' => $originId,
                            'Movement'=>array(
                                '$elemMatch' => array(
                                        'OriginId' => $originId,
                                    )
                            )
                        ),
                    ),
                ),
                // Update the "Qty" field of the first array element matched (if any)                  
               //array( '$set' => array( 'Lot.$.Qty' => 'Updated' )),
               array( '$set' => array( 'Lot.$.Movement.$.Qty' => $invqty )),
            array('upsert' => true));

Please anyone help me to resolve this?

$set is what you're looking for ! Google is your friend

Aysennoussi
– Aysennoussi

2014-11-12 21:17:26 +00:00
Commented Nov 12, 2014 at 21:17 — Aysennoussi
– Aysennoussi, Commented Nov 12, 2014 at 21:17

jmikola · Accepted Answer · 2014-11-12 21:40:44Z

1

Using $addToSet is problematic in this case, because the following lot elements would be considered different:

{
    "Qty" :0,
    "ProductLot" : ObjectId("5462dbd9599e5c200e000003"),
    "Product" : ObjectId("543ca7be4cf59d400c000004"),
    "OriginId" : "266"
}

{
    "Qty" :5,
    "ProductLot" : ObjectId("5462dbd9599e5c200e000003"),
    "Product" : ObjectId("543ca7be4cf59d400c000004"),
    "OriginId" : "266"
}

The first element is likely what you would be adding (either with quantity 0 or 1), and the second element would be the same logical lot element, just with an incremented quantity. If the latter element already existed in the array, I imagine you'd like for your application to increment the quantity from 5 to 6 instead of adding the first element, which is essentially a duplicate.

We definitely need two updates here, but I would propose the following:

// Let's assume the following identifiers...
$inventoryId = new MongoId($_POST['inventoryid']);
$productLotId = new MongoId($invlotid);
$originId = new MongoId($_POST['id']);
$lotcontent = [ /* whatever this looks like */ ];

$result = $collection->update(
    // Match an inventory without the specific ProductLot/OriginId element
    [
        '_id' => $inventoryId,
        'Lot' => [
            '$not' => [
                '$elemMatch' => [
                    'ProductLot' => $productLotId,
                    'OriginId' => $originId,
                ],
            ],
        ],
    ],
    // Append a new element to the Lot array field
    [ '$push' => [ 'Lot' => $lotcontent ] ]
);

MongoCollection::update() will return a result document with an n field indicating the number of affected documents. Since we aren't using the multiple option and are also matching at most one document by _id, we can expect n to be either 0 or 1. If n was 0, we either couldn't find an inventory document with that _id or we found one but it already had a Lot element with the product and origin identifiers (i.e. our $elemMatch criteria matched something, invalidating our negation). If n was 1, that means we found the inventory document, it did not contain a matching Lot element, and we appended it (i.e. our job is done).

Assuming n was 0, we should issue another update and attempt to increment the quantity:

$result = $collection->update(
    // Match an inventory with a specific ProductLot/OriginId element
    [
        '_id' => $inventoryId,
        'Lot' => [
            '$elemMatch' => [
                'ProductLot' => $productLotId,
                'OriginId' => $originId,
            ],
        ],
    ],
    // Update the "Qty" field of the first array element matched (if any)
    [ '$inc' => [ 'Lot.$.Qty' => 1 ] ]
);

Here, I'm using the $ positional update operator to access a specific array element that was matched in the criteria. This allows us to craft an $inc without worrying about the index of the matched element.

Again, we can check $result['n'] here. If it's still 0, then we can assume that no document matches our _id (a completely separate error). But if n is 1 at this point, we successfully incremented the quantity and our job is done.

answered Nov 12, 2014 at 21:40

jmikola

6,9222 gold badges33 silver badges66 bronze badges

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

4 Comments

Silviaa Over a year ago

using '$inc' - getting Error : 'MongoCursorException' with message ': Cannot increment with non-numeric argument: {Lot.$.Qty: "4"}'

Silviaa Over a year ago

I try to update the nested array gettin error : db.Inventory.update({"_id":ObjectId("5462e44c599e5c6c13000000"),"Lot.ProductLot":ObjectId("54648649599e5c1c06000003"),"Lot.Movement.OriginId":"267"},{"Lot.$.Movement.$.Qty":"9"});

jmikola Over a year ago

In the $inc error you shared, you're using "4" (a string) as the increment value. You should be using 4 (a number). PHP's loose casting between types does not extend to BSON.

jmikola Over a year ago

In your second command (nested array error), you cannot use the positional operator multiple times. This is an outstanding feature request in MongoDB (see: SERVER-831). Also, it looks like you're missing an atomic modifier. The second argument to update() that you have there would be used as a replacement document, which is surely not what you want. You're also failing to use $elemMatch in the criteria, which is quite necessary/relevant.

Collectives™ on Stack Overflow

MongoDB : Insert and Update in an array

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related