Question 117380 in Mongodb Question posted in
The official documentation can be found here.

Mongodb – Any help to convert this chain of query in a single bulk instruction?

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"}
Tags:

3

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

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

    Login or Signup to reply.
  3. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search