CODEWITHSUNDEEP
mongodb
user391986
user391986

Reputation: 30956

Combining read and write into one query with mongodb

I have two mongodb collections items and settings.

When documents are inserted into items, one of the fields level needs to be read from the settings table. Currently I'm running two queries to make this happen.

Is there a way to do this as one query like I could do with SQL with select queries inside an insert?

// read
let settingRecord = connection.collection("settings").findOne({
    "group_id": 2
}, function(err, result) {});

// write
connection.collection("items").insertOne({
    "item_id": 2,
    "group_id": 2,
    "level": settingRecord['level']
  }, function(err, result) {});

enter image description here

Upvotes: 1

Views: 181

Answers (2)

Mallik
Mallik

Reputation: 334

I could merge into a new collection with a merge command for the given requirement of updating the level field. Let me know how to update the same collection as in aggregate collection eg. items collection in this case.

> db.newCollection.find();
{ "_id" : 1, "groupid" : 2, "itemid" : 2, "settingsDoc" : [ { "level" : 10 } ] }
{ "_id" : 2, "groupid" : 3, "itemid" : 3, "settingsDoc" : [ { "level" : 30 } ] }
> db.items1.find();
{ "_id" : 1, "groupid" : 2, "itemid" : 2, "level" : null }
{ "_id" : 2, "groupid" : 3, "itemid" : 3, "level" : null }
> db.settings.find();
{ "_id" : 1, "groupid" : 2, "level" : 10 }
{ "_id" : 2, "groupid" : 3, "level" : 30 }
> db.items1.aggregate([
... {$lookup:{
...     from: "settings",
...     localField: "groupid",
...     foreignField: "groupid",
...     as:"settingsDoc"
...     }
... },
... {$project:{
...     groupid:1,
...     itemid:1,
...     "settingsDoc.level":1
...     }
... },
... {$merge:{into:"newCollection"}
... }
... ]);
> db.newCollection.find();
{ "_id" : 1, "groupid" : 2, "itemid" : 2, "settingsDoc" : [ { "level" : 10 } ] }
{ "_id" : 2, "groupid" : 3, "itemid" : 3, "settingsDoc" : [ { "level" : 30 } ] }
>

Upvotes: 1

Gibbs
Gibbs

Reputation: 22974

You can do as below using $merge but with some more pipelines as mentioned by @D. SM

db.getCollection('test').aggregate( [
   { $match : { group: 2 } },
   { $project: {level: 1, group:1} },
   { $addFields:{item:2} },
   { $merge : { into: { db: "local", coll: "test2" },   whenMatched: [{ $addFields: {
                "level":"$$new.level", "group":"$$new.group", "item":"$$new.item"
                } } ] , whenNotMatched: "insert" }}
] )

Explanation:

{ $match : { group: 2 } }

Match on a field to get the document - In your case, it would be settings collection

 { $project: {level: 1, group:1} },

Project only the required fields to the next stage. You can skip this stage.

{ $addFields:{item:2} },

Using this you can add list of new fields to be inserted.

Actual magic happens in $merge

  1. into - db and collection where data has to be inserted
  2. whenMatched - add what fields to be inserted, $$new is used to refer the fields matched in the previous stage

Refer this

You can play with the pipelines to avoid unnecessary things. $merge has more options. In this example, you can skip whenNotMatched if you don't want to insert for non match items. You can specify to fail in that case.

Upvotes: 1

Related Questions