Use Rs mongolite to correctly (insert? update?) add data to existing collection

Question

I have the following function written in R that (I think) is doing a poor job of updating my mongo databases collections.

library(mongolite) 

con <- mongolite::mongo(collection = "mongo_collection_1", db = 'mydb', url = 'myurl')
myRdataframe1 <- con$find(query = '{}', fields = '{}')
rm(con)

con <- mongolite::mongo(collection = "mongo_collection_2", db = 'mydb', url = 'myurl')
myRdataframe2 <- con$find(query = '{}', fields = '{}')
rm(con)

... code to update my dataframes (rbind additional rows onto each of them) ...

# write dataframes to database
write.dfs.to.mongodb.collections <- function() {

  collections <- c("mongo_collection_1", "mongo_collection_2") 
  my.dataframes <- c("myRdataframe1", "myRdataframe2")

  # loop dataframes, write colllections
  for(i in 1:length(collections)) {

    # connect and add data to this table
    con <- mongo(collection = collections[i], db = 'mydb', url = 'myurl')
    con$remove('{}')
    con$insert(get(my.dataframes[i]))
    con$count()

    rm(con)
  }
}
write.dfs.to.mongodb.collections()

My dataframes myRdataframe1 and myRdataframe2 are very large dataframes, currently ~100K rows and ~50 columns. Each time my script runs, it:

uses con$find('{}') to pull the mongodb collection into R, saved as a dataframe myRdataframe1
scrapes new data from a data provider that gets appended as new rows to myRdataframe1
uses con$remove() and con$insert to fully remove the data in the mongodb collection, and then re-insert the entire myRdataframe1

This last bullet point is iffy, because I run this R script daily in a cronjob and I don't like that each time I am entirely wiping the mongo db collection and re-inserting the R dataframe to the collection.

If I remove the con$remove() line, I receive an error that states I have duplicate _id keys. It appears I cannot simply append using con$insert().

Any thoughts on this are greatly appreciated!

Would this perhaps get you one step further? https://stackoverflow.com/a/52823960/6440033 — dnickless, Oct 16 '18 at 18:44
@Canovice is there a way to get into contact with you? I think we are working on similar projects and using similar tools (shiny, R, mongoDB). I would love to see where you are @ and share some info with each other. Msg me on FB Jordan Wrong. Looking forward to chatting — Jordan Wrong, Apr 29 '19 at 23:08

score 2 · Accepted Answer · answered Oct 15 '18 at 19:46

2

When you attempt to insert documents into MongoDB that already exist in the database as per their primary key you will get the duplicate key exception. In order to work around that you can simply unset the _id column using something like this before the con$insert:

my.dataframes[i]$_id <- NULL

This way, the newly inserted document will automatically get a new _id assigned.

answered Oct 15 '18 at 19:46

dnickless

10,733
1
19
34

My _id columns are of specific unique formats (they are not randomly generated) and i dont think I should remove them. – Canovice Oct 16 '18 at 17:45
But this is a good workaround if _id columns were randomly generated – Canovice Oct 16 '18 at 17:45
I think I need to only insert the new data into collection, so add the additional step of filtering my dataframes before inserting them – Canovice Oct 16 '18 at 17:46

score 0 · Answer 2 · answered Oct 21 '18 at 21:32

you can use upsert ( which matches document with the first condition if found it will update it, if not it will insert a new one, first you need to separate id from each doc

 _id= my.dataframes[i]$_id
 updateData = my.dataframes[i]
 updateData$_id <- NULL

then use upsert ( there might be some easier way to concatenate strings in R)

 con$update(paste('{"_id":"', _id, '"}' ,sep="" ) , paste('{"$set":', updateData,'}', sep=""), upsert = TRUE)

Use Rs mongolite to correctly (insert? update?) add data to existing collection

2 Answers2

Linked