Index and upsert a mongo subdocument

Question

I was working on mongo and I'd like to do the following things:

when a set of (lat, lon, uid) comes in:

1. the collection has lat as unique index, also for each lat the lon index is unique
2. if (lat, lon) pair exists in this collection, update uid in the sub-document
3. if (lat) exists in this document , insert (lon, uid) in the lons sub-document
4. if (lat) document doesn't exist, create lat document and do 2

[{
    "lat" : 1,  (doc is unique by lat)
    "lons" : [ 
        {
            "lon" : 2,   (the subdocument is unique by lon)
            "uid" : 3
        },
        {
            "lon" : 3,
            "uid" : 3
        }
    ]
},
{
    "lat" : 2,
    "lons" : [ 
        {
            "lon" : 2,
            "uid" : 4
        }
    ]
}]

I tried to do the following things but apparently it's not working as what I imagined.

db.zones.update({'lat': 90}, {$push: {lons: {'uid' : 0, 'lon': -18}}}, { upsert: true })
db.zones.ensureIndex({'lat': -1, 'lons.lon':1}, {unique: true})

I checked this post Can mongo upsert array data? and some others but somehow none of them is working. I don't know if it's my problem or mongo problem. Thanks!

Answer 1

I would suggest you reconsider your schema:

• The upsert applies at a document level, which doesn't fit nicely with how your schema is structured. If a match isn't found in the lons array you want to push to the existing document rather than creating a new one.

• Documents including arrays with unbounded growth can lead to frequent document moves and performance issues (see: Why shouldn't I embed large arrays in my documents?)

• Your schema doesn't lend itself to a geospatial index (which would require longitude/latitude pairs as an array or embedded document). I'm guessing this isn't important for your use case since you are ensuring a normal unique index, but it might be worth considering.

A better schema (assuming you don't plan to use geospatial queries) would be:

{
    lon: -74.0059,
    lat: 40.7127,
    uid: 3
}

With this revised schema, your update requirements are more straightforward.

1. the collection has lat as unique index, also for each lat the lon index is unique

You still want to ensure a unique index:

      db.zones.ensureIndex({'lat': 1, 'lon':1}, {unique: true})

2. if (lat, lon) pair exists in this collection, update uid in the sub-document

3. if (lat) exists in this document , insert (lon, uid) in the lons sub-document

4. if (lat) document doesn't exist, create lat document and do 2

All this logic can now be handled by an upsert:

db.zones.update(

    // query criteria
    { lat: 40.7127, lon: -74.0060 },

    // update
    { $set: {
        uid: 3
    }},

    // options
    {
        upsert: true
    }
)

If you want to preserve the uid when updating an existing document, you could also use the $setOnInsert operator (instead of $set):

db.zones.update(

    // query criteria
    { lat: 40.7127, lon: -74.0060 },

    // update
    { $setOnInsert: {
        uid: 3
    }},

    // options
    {
        upsert: true
    }
)