On a NodeJs application, I've a snippet which:

- check if an element exist (1 query)
- if present, update some value of it (1 query)
- else, add a new record (1 query) and delete an older one (1 query)

So basically 4 query for a "single" task. Here's my complete code:

let candleData;
try {
    candleData = await Candle.findOne({ instrument_name: instrumentName, exchangeType: exchangeType, dt: dt });
    if (!candleData) {
        try {
            // add new candle
            let newCandle = {
                instrument_name: instrumentName,
                exchangeType: exchangeType,
                dt: dt,
                o: o,
                h: h,
                l: l,
                c: c
            }
            await Candle.create(newCandle);

            // remove older candle
            await Candle.deleteMany({ instrument_name: instrumentName, exchangeType: exchangeType, dt: { $lt: dtOlder } });
        } catch (error) {
            console.log(error);
            return;
        }
    } else {
        // update candle
        candleData.c = c;
        await candleData.save();
    }
} catch (error) {
    console.log(error);
    return;
}

Can I convert it as bulk operation? And improve the whole chain?

Note: I'm finding/removing different items. The "where" are different. I find for dt=dt and remove for dt lower than dtOlder. So its not really the same. So its not an upsert instruction.

Here's some document to have some test:

{  "_id": {    "$oid": "62c40373d08fcf4ca03eb4b4"  },  "instrument_name": "BTCBUSD",  "exchangeType": "BINANCE",  "dt": 1657005300000,  "o": "20264.80000000",  "h": "20290.73000000",  "l": "20178.66000000",  "c": "20211.63000000"}
{  "_id": {    "$oid": "62c40373d08fcf4ca03eb4b5"  },  "instrument_name": "BTCBUSD",  "exchangeType": "BINANCE",  "dt": 1657006200000,  "o": "20213.33000000",  "h": "20218.26000000",  "l": "20155.26000000",  "c": "20202.61000000"}
{  "_id": {    "$oid": "62c40373d08fcf4ca03eb4b6"  },  "instrument_name": "BTCBUSD",  "exchangeType": "BINANCE",  "dt": 1657007100000,  "o": "20202.61000000",  "h": "20238.23000000",  "l": "20172.76000000",  "c": "20192.74000000"}
{  "_id": {    "$oid": "62c40373d08fcf4ca03eb4b7"  },  "instrument_name": "BTCBUSD",  "exchangeType": "BINANCE",  "dt": 1657008000000,  "o": "20192.75000000",  "h": "20192.75000000",  "l": "20033.95000000",  "c": "20090.00000000"}
{  "_id": {    "$oid": "62c40373d08fcf4ca03eb4b8"  },  "instrument_name": "BTCBUSD",  "exchangeType": "BINANCE",  "dt": 1657008900000,  "o": "20090.00000000",  "h": "20098.49000000",  "l": "19930.00000000",  "c": "19989.98000000"}

CodePudding user response：

Try this one:

db.candleData.updateOne(
   { instrument_name: instrumentName, exchangeType: exchangeType, dt: dt },
   {
      $set: { c: c },
      $setOnInsert: {
         instrument_name: instrumentName,
         exchangeType: exchangeType,
         dt: dt,
         o: o,
         h: h,
         l: l
      }
   },
   { upsert: true }
)

Ensure that the filter matches a single document only.

CodePudding user response：

If your situation is not appropriate to use upsert, MongoDB Realm Function or Database Trigger,
I think your logic is reasonable.

CodePudding user response：

If the number of documents that need to be deleted is one or zero, you can do it in one query. Something like:

1. $match the common ground. If a situation that there is no match here is reasonable, this should be inserted into both branches of the $facet
2. differ the two cases found/notFound using $facet. If found, replace the wanted fields, if not, limit the updated (needs to be deleted) document to 1.
3. format and updated the notFound case to a new document using the "deleted" document _id.
4. use $ifNull to set one new document to update according to our case: found/notFound.
5. use $merge to update the document (all cases end in one updated document).

db.collection.aggregate([
  {$match: {instrument_name: instrument_name, exchangeType: exchangeType}},
  {
    $facet: {
      found: [{$match: {dt: dt}}, {$set: {c: c}}],
      notFound: [{$match: {dt: {$lt: dtOlder }}}, {$limit: 1}]
    }
  },
  {
    $set: {
      found: {$first: "$found"},
      notFound: {$mergeObjects: [
        {$first: "$notFound"}, 
        {dt: dt, o: o, h: h, l: l, c: c, instrument_name: instrumentName,
         exchangeType: exchangeType}]}
    }
  },
  {$project: {newObj: {$ifNull: ["$found", "$notFound"]}}},
  {$merge: {into: "collection"}}
])

See how it works on the playground example