Cannot infer query fields to set error on insert

Question

I'm trying to achieve a "getOrCreate" behavior using "findAndModify". I'm working in nodejs using the native driver.

I have:

var matches = db.collection("matches");
matches.findAndModify({
        //query
        users: {
            $all: [ userA_id, userB_id ]
        }, 
        lang: lang, 
        category_id: category_id
    }, 
    [[ "_id", "asc"]], //order
    {
        $setOnInsert: { 
            users: [userA_id, userB_id], 
            category_id: category_id, 
            lang: lang, 
            status: 0
        }
    }, 
    {
        new:true, 
        upsert:true
    }, function(err, doc){
        //Do something with doc
    });

What i was trying to do is: "Find specific match with specified users, lang and category... if not found, insert a new one with specified data"

Mongo is throwing this error:

Error getting/creating match { [MongoError: exception: cannot infer query fields to set, path 'users' is matched twice]
  name: 'MongoError',
  message: 'exception: cannot infer query fields to set, path \'users\' is matched twice',
  errmsg: 'exception: cannot infer query fields to set, path \'users\' is matched twice',
  code: 54,
  ok: 0 }

Is there a way to make it work? It's impossible?

Thank you :)

Ceremonious

Answer 1

It's not the "prettiest" way to handle this, but current restrictions on the selection operators mean you would need to use a JavaScript expression with $where.

Substituting your vars for values for ease of example:

matches.findAndModify(
    {
        "$where": function() {
            var match = [1,2];
            return this.users.filter(function(el) { 
                return match.indexOf(el) != -1;
            }).length >= 2;
        },
        "lang": "en",
        "category_id": 1
    },
    [],
    { 
        "$setOnInsert": {
            "users": [1,2],
            "lang": "en",
            "category_id": 1      
        }
    },
    {
        "new": true,
        "upsert": true
    },
    function(err,doc) {
        // do something useful here
    }
);

As you might suspect, the "culprit" here is the positional $ operator, even though your operation does not make use of it.

And the problem specifically is because of $all which is looking for the possible match at "two" positions in the array. In the event that a "positional" operator was required, the engine cannot work out ( presently ) which position to present. The position should arguably be the "first" match being consistent with other operations, but it is not currently working like that.

Replacing the logic with a JavaScript expression circumvents this as the JavaScript logic cannot return a matched position anyway. That makes the expression valid, and you can then either "create" and array with the two elements in a new document or retrieve the document that contains "both" those elements as well as the other query conditions.

---

P.S Little bit worried about your "sort" here. You may have added it because it is "mandatory" to the method, however if you do possibly expect this to match "more than one" document and need "sort" to work out which one to get then your logic is slightly flawed.

When doing this to "find or create" then you really need to specifiy "all" of the "unique" key constraints in your query. If you don't then you are likely to run into duplicate key errors down the track.

Sort can in fact be an empty array if you do not actually need to "pick" a result from many.

Just something to keep in mind.

Hervey

is there other way? I'm unable to reference other var in the $where function. ReferenceError is thrown when I use any var outside the function. – Possession

@SooChengKoh No there isn't. But this is not your question. So if you have another question then please ask it. – Hervey

I did a wrong check of the code, sorry... it doesn't works :( Any other ideas? Additionally to the issue mentioned by @SooChengKoh, Im using native mongodb driver in mongodb, it seems to ignore my function in $where clause, any other ideas? – Ceremonious

@Ceremonious Of course it works and of course it is tested. JavaScript functions sent to the server are self contained and cannot reference a variable in the scope as they are "sent" to the server as a package in BSON and executed there only. Valid formats are either as a function, which you can declare in a variable and give as an argument to $where ( but you probably want a closure to inject values ) or a a string that you can construct, it is basically sent as a coded string to the server anyway. The principle here is avoid matching the array element in a regular query operator. Which works. – Hervey

Thank you @Blakes Seven. I sent the function in plain text and it worked :). Is there a performance lose for using this $where function? – Ceremonious

@Ceremonious Look into learning closures. It's a much cleaner way since you are working in JavaScript already. Guarantee that once you understand how closures work you will be hooked. – Hervey