Trying to upsert (add) an element to an array field with java driver 3

[James Mangold]

Hi,

I tried to post this, but I think it didn't actually happen. Anyway, I'll post again...

I have a SQL database that I am trying to model and migrate to Mongo. Each row in SQL has a single attribute and value for a specific entityId and effective date. I want to merge them into a single doc with an array of attributes, like such:

{

        "_id" : ObjectId("56937445fa462644409d96b0"),

        "id" : NumberLong("21575752319"),

        "entityId" : NumberLong(2),

        "lastModifiedAppTime" : NumberLong("1452504132665"),

        "lastModifiedValueTime" : ISODate("2015-12-12T00:40:24Z"),

        "attributes" : [

                {

                        "attributeId" : 5,

                        "effectiveDate" : ISODate("2015-01-05T05:00:00Z"),

                        "timeStamp" : ISODate("2015-12-12T00:40:24Z"),

                        "attributeVal" : 9.75

                },

                {

                        "attributeId" : 2,

                        "effectiveDate" : 2,

                        "timeStamp" : 2,

                        "attributeVal" : 2

                }

        ]

}

When I use findOneAndUpdate from the java driver, I get an error: 

04:36:46.385 Error:com.idc.db.mongodb.edw.IndexServicesExtractor@2f084681 java.lang.IllegalArgumentException: Invalid BSON field name _id

org.bson.AbstractBsonWriter.writeName(AbstractBsonWriter.java:494)

My code to do the upsert is as follows:

// for every row I extract from SQL, either insert a new document, or add an attribute to an existing one

for (each row extracted) {

    FindOneAndUpdateOptions updateOptions = new FindOneAndUpdateOptions();

    updateOptions.upsert(true);

    final BasicDBObject query = new BasicDBObject("_id", makeKey(rset.getLong("entityId"),rset.getString("effectiveDate")));

    ArrayList<Document> attrs= new ArrayList<Document>();

                attrs.add(new Document().append("attributeId",rset.getInt("attributeId"))

                .append("effectiveDate", edwFormatter.parse(rset.getString("effectiveDate")))

                .append("timeStamp", edwFormatter.parse(rset.getString("timeStamp")))

                .append("attributeVal", normalize(rset.getObject("attributeVal"))));

    Document doc = new Document();

                doc.append("_id",makeKey(rset.getLong("entityId"),rset.getString("effectiveDate")))

                .append("id", rset.getLong("id"))

                .append("entityId", rset.getLong("entityId"))

                .append("lastModifiedAppTime", System.currentTimeMillis())

                .append("lastModifiedValueTime", edwFormatter.parse(rset.getString("timeStamp")))

                .append("attributes",attrs);

                

               

                collection.findOneAndUpdate(query, doc, updateOptions);

}

so when its finished, I end up with a document that started like the example above, but could have 100+ attributes in the "attributes" array, and all but the lastModDates remain unchanged.

Hopefully that is clear. Maybe I am not even doing it correctly.

Thanks,

James

[Jeff Yemin]

Hi James,

You should be using the findOneAndReplace method rather than findOneAndUpdate.   The findOneAndUpdate method is only applicable for use with update operators.  The findOneAndReplace method is applicable when you're providing a whole document, similar to what you would pass to the insertOne or insertMany method.  That seems to be what you're doing in the example that you provided.

Regards,

Jeff