Bulk Upsert for historical data

I’m working with MongoDB to create a stock ticker app, and I’m trying to figure out how to structure the db + queries. Right now, I have a collection of object that look like this:

{
  "id":"mongodb",
  "website":"www.mongodb.com",
  "history":[
    {"price":350, "timestamp":"2020-10-21T13:28:06.419Z"}, 
    {"price":320,"timestamp":"2020-10-21T13:28:06.419Z"}, 
    {"price":310,"timestamp":"2020-10-21T13:28:06.419Z"}
  ]
}

I have a function that calls an API in bulk and returns ~5k stocks with their id. I want to upsert this array as follows: (1) based on the id, update history.price by pushing a new object with price and timestamp (2) if the id doesn’t exist, create a new document with the rest of the API data (id, website) and write that stocks price to a history array as well as the first entry.

Right now, I’m thinking about bulk.find.upsert, but I can’t figure out how to pass the whole array to this so that it can upsert based off the ID.

Code so far (very basic):

var bulk = db.items.initializeUnorderedBulkOp();
bulk.find( { id: array[objId].id } ).upsert().update(
   {
     $setOnInsert: { Name: array[objId].name, website: array[objId].website .history: [{price: array[objid].price, timestamp: ISO}] },
     $set: { history: [{price: array[objId].price, timestamp: ISO}] }
   }
); 

bulk.execute();

Hello @lmb, welcome to the MongoDB Community forum.

That is done by looping thru the array using a for-loop (assuming JavaScript here), for example:

var bulk = db.items.initializeUnorderedBulkOp();

for (let doc in array) {
   // The bulk find and update query for each matching document in the collection
}

bulk.execute();

The execute sends the updates to the server all at once.

Also, in the code $set: { history: [{price: array[objId].price, timestamp: ISO}] }, you are trying to add (or push) to the history array - so, use the $push array update operator.

Hi @lmb,

Welcome to MongoDB community!

I think you are pretty close.

You should use a $push operation to add the price on update.

var bulk = db.items.initializeUnorderedBulkOp();
for ( ... ){
bulk.find( { id: array[objId].id } ).upsert().update(
  {
    $setOnInsert: { Name: array[objId].name, website: array[objId].website .history: [{price: array[objid].price, timestamp: ISO}] },
    $push : { history: {price: array[objId].price, timestamp: ISO}}
  }
); 
}

bulk.execute();

Thanks
Pavel

1 Like