MongoDB - upsert involving lists

Question

I'm a MongoDB newbie and wanted to ask how to write an update command involving upsert and list.

Basically I want to accomplish something like this:

{"_id" : ObjectId("4c28f62cbf8544c60506f11d"),
"some_other_data":"goes here",
"trips": [
    {"name": "2010-05-10",
     "loc": [{"lat":21.321231, "lng": 16.8783234, "updated_at": "Mon May 10 2010 15:24:35"}, 
        {"lat":21.321231, "lng": 16.8783234, "updated_at": "Mon May 10 2010 15:24:24"}]
    },
    {"name": "2010-05-08",
     "loc": [{"lat":21.324239, "lng": 16.8735234, "updated_at": "Mon May 8 2010 11:18:05"},
        {"lat":21.311234, "lng": 16.8743271, "updated_at": "Mon May 8 2010 11:17:55"}, 
        {"lat":21.321238, "lng": 16.8782219, "updated_at": "Mon May 8 2010 11:17:45"}]
    }
]}

Note that:

You supply a trip name and the current location
If the trip does not exist already, it needs to be created
trips.name should be unique so that if it exists, you append to the location array

This is the query I wrote combining the positional operator with $push.

    db.mycollection.update({"application_id": "MyTestApp", 
                            "trips.name": "2010-05-10"},
                           {$push: {'trips.$.loc': {"lat":11, "lng":11} }}, 
                           true);

But this results in data like this:

> db.mycollection.find({"application_id":"MyTestApp"})          
{ "_id" : ObjectId("4c29044ebf8544c60506f11f"), 
"application_id" : "MyTestApp", 
"trips" : { "$" : { "loc" : [ { "lat" : 11, "lng" : 11 } ] }, 
"name" : "2010-05-10" } 
}

You can see that

"trips" is not an array
it took "$" literally and created a key with that (doh!)

So far I've been pretty happy with MongoDB, but there's definitely a steep learning curve with writing complicated queries.

Any feedback will be appreciated.

Thanks in advance, Amie

check my answer below again - I edited it to include what I believe to be the solution (or at least something that should get you very close) — nearlymonolith
– nearlymonolith, Commented Jun 30, 2010 at 21:17

Scott Hernandez · Accepted Answer · 2010-06-28 23:42:59Z

You can't mix the positional operator ("$") and an upsert; the "$" will be treated as a field name during the insert. You can't do this for new documents, only existing one.

I suggested a structure more like this:

{"_id" : ObjectId("4c28f62cbf8544c60506f11d"),
"some_other_data":"goes here",
"trips": { 
    "2010-05-10":
       [{"lat":21.321231, "lng": 16.8783234, "updated_at": "Mon May 10 2010 15:24:35"}, 
        {"lat":21.321231, "lng": 16.8783234, "updated_at": "Mon May 10 2010 15:24:24"}],
    "2010-05-08": 
       [{"lat":21.324239, "lng": 16.8735234, "updated_at": "Mon May 8 2010 11:18:05"},
        {"lat":21.311234, "lng": 16.8743271, "updated_at": "Mon May 8 2010 11:17:55"}, 
        {"lat":21.321238, "lng": 16.8782219, "updated_at": "Mon May 8 2010 11:17:45"}]
    }
}

Then you can issue an update like this:

db.mycollection.update({application_id: "MyTestApp", "trips.2010-05-10":{$exists:true}},
                       {$push: {"trips.2010-05-10": {lat:11, lng:11} }}, 
                       true);

results in this being inserted.

> db.mycollection.find()
{ "_id" : ObjectId("4c2931d17b210000000045f0"), 
    "application_id" : "MyTestApp", 
    "trips" : { "2010-05-10" : [ { "lat" : 11, "lng" : 11 } ] } }

and running it again give you this:

> db.mycollection.find()
{ "_id" : ObjectId("4c2932db7b210000000045f2"), 
    "application_id" : "MyTestApp", 
    "trips" : { "2010-05-10" : 
        [ { "lat" : 11, "lng" : 11 }, 
          { "lat" : 11, "lng" : 11 } ] } }

This is very close to what I tried to achieve. Thanks @Scott Hernandez. With this schema, is it possible to retrieve a list of keys in the "trips"? ("2010-050-10", etc) db.mycollection.find({"application_id":"MytestApp"}, {"trips":true}) returns the entire "trips" hash.
I ended up going with this method. Thanks for the solution Scott!
The problem there is that you can't query for something within a trip, like find documents with a trip at lat 21.321231 (as mongo (today) has no way to 'wild-card' part of the path to the field).

Community · Accepted Answer · 2020-06-20 09:12:55Z

EDITED TO INCLUDE CORRECT SOLUTION

This is exactly the problem I hit learning Mongo - you're looking for the $addToSet operator (see docs here) that's used with the update command, in conjunction with the $ positional operator you were using.

$addToSet

{ $addToSet : { field : value } }

Adds value to the array only if its not in the array already.

The query thus becomes (db.stack is the collection I used for testing purposes), sample run to follow:

db.stack.update({ "trips.name":"2010-05-10" }, 
                { $addToSet: { "trips.$.loc":{"lat":11, "lng":12} } }
               );

TEST RUN (with some abbreviations for space of the elements that aren't important):

#### YOUR ITEM IN THE DB
> db.stack.find({"trips.name":"2010-05-10"})
{ "_id" : ObjectId("4c28f62cbf8544c60506f11d"), "some_other_data" : "goes here", 
    "trips" : [
    { "name" : "2010-05-10",
        "loc" : [ {
                "lat" : 21.321231,
                "lng" : 16.8783234,
                "updated_at" : "Mon May 10 2010 15:24:35"
            }, { "lat" : 21.321231,
                "lng" : 16.8783234,
                "updated_at" : "Mon May 10 2010 15:24:24"
            } ] },
    { "name" : "2010-05-08",
        "loc" : [ ... ]
    } ] }
#### SUCCESSFULLY ADDS ITEM TO PROPER ARRAY
> db.stack.update({"trips.name":"2010-05-10"}, {$addToSet: {"trips.$.loc":{"lat":11, "lng":11}}});
> db.stack.findOne()
{ "_id" : ObjectId("4c28f62cbf8544c60506f11d"), "some_other_data" : "goes here",
    "trips" : [
        { "loc" : [
                { "lat" : 21.321231,
                    "lng" : 16.8783234,
                    "updated_at" : "Mon May 10 2010 15:24:35"
                }, { "lat" : 21.321231,
                    "lng" : 16.8783234,
                    "updated_at" : "Mon May 10 2010 15:24:24"
                }, { "lat" : 11,
                    "lng" : 11
                }
            ], "name" : "2010-05-10"
        },
        { "name" : "2010-05-08",
            "loc" : [ ...  ]
        } ] }
#### ON REPEAT RUN DOESN'T ADD NEW ELEMENT
> db.stack.update({"trips.name":"2010-05-10"}, {$addToSet: {"trips.$.loc":{"lat":11, "lng":11}}});
> db.stack.findOne()
{ "_id" : ObjectId("4c28f62cbf8544c60506f11d"), "some_other_data" : "goes here",
    "trips" : [ {
            "loc" : [
                { "lat" : 21.321231,
                    "lng" : 16.8783234,
                    "updated_at" : "Mon May 10 2010 15:24:35"
                }, { "lat" : 21.321231,
                    "lng" : 16.8783234,
                    "updated_at" : "Mon May 10 2010 15:24:24"
                }, { "lat" : 11,
                    "lng" : 11
                }
            ], "name" : "2010-05-10"
        },
        { "name" : "2010-05-08",
            "loc" : [ ...  ]
        } ] }
#### BUT WILL CORRECTLY ADD ANOTHER ELEMENT TO THE SAME ARRAY IF IT'S NOT PRESENT
> db.stack.update({"trips.name":"2010-05-10"}, {$addToSet: {"trips.$.loc":{"lat":11, "lng":12}}});
> db.stack.findOne()
{ "_id" : ObjectId("4c28f62cbf8544c60506f11d"), "some_other_data" : "goes here",
    "trips" : [
        { "loc" : [
                { "lat" : 21.321231,
                    "lng" : 16.8783234,
                    "updated_at" : "Mon May 10 2010 15:24:35"
                }, { "lat" : 21.321231,
                    "lng" : 16.8783234,
                    "updated_at" : "Mon May 10 2010 15:24:24"
                }, { "lat" : 11,
                    "lng" : 11
                }, { "lat" : 11,
                    "lng" : 12
                }
            ], "name" : "2010-05-10"
        },
        { "name" : "2010-05-08",
            "loc" : [ ... ]
    } ] }

Thanks for the response @Anthony Morelli. I tried $addToSet and had to pass in upsert=true to insert a trip for the first time. When I changed the trip name to something else, this actually creates a new document instead of using the current doc. How can we specify that "trips" is an array?
What query did you run specifically? $addToSet only works with arrays, so I don't think that specification is the problem.
I ran your query exactly, and it didn't create a doc, so I passed in upsert=true. After it creates the first doc, when it ran for the second time, it created a second doc instead of appending it to the previous one..
I believe I fixed it - see edit. Basically, I forgot the positional operator which is necessary to specify which item in the trips array should be updated. The new query is db.stack.update({"trips.name":"2010-05-10"}, {$addToSet: {"trips.$.loc":{"lat":11, "lng":12}}}); - I tested it with your existing schema and it works fine.
Is it correct to say that this only works if the document already exists? (As Scott pointed out, we can't use "$" and an upsert together) But I can see this can be useful in some cases. Thanks for the comprehensive update!

Collectives™ on Stack Overflow

MongoDB - upsert involving lists

2 Answers 2

3 Comments

6 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

6 Comments

Linked

Related