CODEWITHSUNDEEP
mongodbmongodb-query
Logan
Logan

Reputation: 117

Mongo Query in Update

I'm new to Mongo (an SQL guy by preference) and working in an application set up by someone else. I have records like this, plus more information:

City:
    name: "name"
    State: ObjectId({id here})
    slug: "slug"
    images: [images]
    ...

And state also has a text field with slug in it. I want to create a new entry for a city for related Cities. So it would be an array with the images, the slug, the state slug, and the name. I have tried the following, but I end up with query information for the stateSlug:

db.getCollection('city').find(
{
    $or: 
        [
            {"name": "Camden"}, 
            {"name": "Virginia Beach"}, 
            {"name": "Annapolis"}
        ]
},{images:1, slug:1, state:1, name:1}
).forEach(function(city){
    db.city.update({"name" : "Lewes"}, {$push:{'relatedCities':
        { "images":city.images,
          "slug":city.slug,
          "name":city.name,
          "stateSlug":db.state.find(city.state,{slug:1,_id:0})
        }
    }})
})

Is there a way that I can make this query work? I have the names that are used for the queries in an excel file and a python script that can output the info in this format to a js file to execute if I can figure out how to make it work. Thanks for any help!

Upvotes: 1

Views: 625

Answers (1)

chridam
chridam

Reputation: 103365

The approach you need to take involves running an aggregation pipeline that uses the following pipeline steps (in the order given):

1) $match to filter documents that get into the pipeline. Consider using an $in operator instead of $or if your expression involves equality checks for the value of the same field. This is similar to the SQL's WHERE clause where e.g.

SELECT name, images, state, slug
WHERE name in ('Camden', 'Virginia Beach', 'Annapolis')

instead of

SELECT name, images, state, slug
WHERE 
    (name = 'Camden') OR
    (name = 'Virginia Beach') OR
    (name = 'Annapolis')

2) $lookup to do a left outer join to an unsharded collection in the same database to filter in documents from the "joined" collection which is the state collection for processing. The $lookup stage does an equality match between the state field from the input documents with the _id field from the documents of the "joined" collection state.

3) $unwind - The new field from the results of the previous $lookup pipeline is an array so you'll need to add an $unwind stage to your pipeline so that you can flatten the array as it needs to be processed further down as a denormalised field.

4) $group pipeline step to group all the documents and create an array relatedCities that has fields from the previous pipeline. The $group pipeline operator is similar to the SQL's GROUP BY clause. In SQL, you can't use GROUP BY unless you use any of the aggregation functions. The same way, you have to use an aggregation function in MongoDB as well. You can read more about the aggregation functions here.

The accumulator operator you would need to create the array is $push.

5) $project final stage is then used to select or rename properties from the documents in the preceding pipeline - similar to what you would do with the SQL SELECT clause. For creating a new field with a string literal, you would need the $literal operator which is similar to SQL's AS or ALIAS keyword.

One thing to note here is when executing a pipeline, MongoDB pipes operators into each other. "Pipe" here takes the Linux meaning: the output of an operator becomes the input of the following operator. The result of each operator is a new collection of documents. So Mongo executes the above pipeline as follows:

collection | $match | $lookup | $unwind | $group | $project => result

Now, when you run this aggregation pipeline in mongo shell, the results will be an array which you get when you use the toArray() method on the cursor returned from calling the aggregate() method on the city collection.

You can then create the new city entry in the city collection by first accessing the only element in the result array through the zero index which is the new city document that has the aggregated fields, and use the save() method on the city collection to persist the document.

The following example demonstrates the above concept:

var pipeline = [
        {
            "$match": {
                "name": { "$in": ["Camden", "Virginia Beach", "Annapolis"] }
            }
        },
        {
            "$lookup": {
                "from": "state",
                "localField": "state",
                "foreignField": "_id",
                "as": "states"
            }
        },
        { "$unwind": "$states" },
        {
            "$group": {
                "_id": null,
                "relatedCities": {
                    "$push": {
                        "images": "$images",
                        "slug": "$slug",
                        "name": "$name",
                        "stateSlug": "$states.slug"
                    }
                }
            }
        },
        {
            "$project": {
                "_id": 0,
                "name": { "$literal": "Lewes" },
                "relatedCities": 1
            }
        }       
    ],
    newCity = db.city.aggregate(pipeline).toArray()[0];

db.city.save(newCity);

Upvotes: 1

Related Questions