Is there a way to insert a record with a value from another record

Question

I have case of collection that holds "item" data, with required node "owner" and optional node "status", e.g.

{ _id: 123, item: {some: "data 123" }, owner: {id: 456} }
{ _id: 124, item: {some: "data 124" }, owner: {id: 789}, status: { ok: 1} }

and when insert a new record, if the new owner.id equals the old owner.id, then i want the new record to have the same status

expressed in SQL it'd be something like

INSERT INTO mytable 
(
    id, 
    item_some,
    owner_id,
    status_ok
) 
 VALUES (
    125, 
    'data 125', 
    789, 
    (SELECT COALESCE(status_ok, null) FROM mytable WHERE owner_id=789 LIMIT 1) 
);

I'm looking at $expr, but not sure how to use it in db.collection.insert()... Thanks in advance

score 1 · Accepted Answer · answered Sep 22 '22 at 12:35

I recommend you just execute a findOne first, like so:

const newObj = { item: {some: "data 123" }, owner: {id: 456} };
const prevObj = db.collection.findOne( {"owner._id": newObj.owner.id , status: {$exists: true}});
if (prevObj) {
    newObj.status = prevObj.status
}

db.collection.insertOne(newObj)

This is essentially what your SQL query does, there is no way in Mongo to do this in just 1 call. (I will attach a "hacky" way to achieve this using the aggregation pipeline and the $merge stage however I do not recommend you use it as it has a lot of overhead and also requires at least 1 document in the collection )

db.collection.aggregate([
    {
        $facet: {
            new: [
                {
                    $limit: 1
                },
                {
                    $replaceRoot: {
                        newRoot: newObj
                    }
                }
            ],
            old: [
                {
                    $match: {
                        "owner.id": newObj.owner.id,
                        status: {$exists: true}
                    }
                }
            ]
        },
    },
    {
       $replaceRoot: {
           newRoot: {
                $mergeObjects: [
                    {
                        $arrayElemAt: ["$new", 0]
                    },
                    {
                        $cond: [
                            {
                              $ne: [
                                  {
                                      $arrayElemAt: ["$old", 0]
                                  },
                                  null
                              ]
                            },
                            {
                                status: {
                                    $getField: {
                                        field: "status",
                                        input: {
                                            $arrayElemAt: ["$old", 0]
                                        }
                                    }
                                }
                            },
                            {}
                        ]
                    }
                ]
           }
       }
    },
    {
        $merge: {
            into: "collection",
            whenNotMatched: "insert"
        }
    }
])

Mongo Playground

Is there a way to insert a record with a value from another record

1 Answers1