CODEWITHSUNDEEP
pythonmongodbpymongo
archienorman
archienorman

Reputation: 1454

Update new document field value based on match of previous records in MongoDB collection

I have the following documents in a mongodb collection:

Records with ids 1-4 get inserted to the collection after records 4-8

[
    {id: "1" , name: "archie", age: "25", nationality: "british"},
    {id: "2" , name: "archie", age: "25", nationality: "british"},
    {id: "3" , name: "john", age: "25", nationality: "british"},
    {id: "4" , name: "alex", age: "27", nationality: "french"},
    {id: "5" , name: "archie", age: "25", nationality: "british", tag: "value2"},
    {id: "6" , name: "archie", age: "25", nationality: "british", tag: "value2"},
    {id: "7" , name: "john", age: "25", nationality: "british"},
    {id: "8" , name: "alex", age: "27", nationality: "french", tag: "value1"}
]

I want to add/update the field tag with the same value in previous records where the name, age and nationality match.

Here is the expected outcome:

[
    {id: "1" , name: "archie", age: "25", nationality: "british", tag: "value2"},
    {id: "2" , name: "archie", age: "25", nationality: "british", tag: "value2"},
    {id: "3" , name: "john", age: "25", nationality: "british"},
    {id: "4" , name: "alex", age: "27", nationality: "french", tag: "value1"},
    {id: "5" , name: "archie", age: "25", nationality: "british", tag: "value2"},
    {id: "6" , name: "archie", age: "25", nationality: "british", tag: "value2"},
    {id: "7" , name: "john", age: "25", nationality: "british"},
    {id: "8" , name: "alex", age: "27", nationality: "french", tag: "value1"}
]

What is the best way to achieve this?

(I am using PyMongo)

Many thanks,

Upvotes: 0

Views: 799

Answers (2)

Clement Amarnath
Clement Amarnath

Reputation: 5466

To acheive this use $update query with $set and multi

Let me have a collection with the following data - all these queries will work in MongoShell

db.collection.insert([{id: "1" , name: "archie", age: "25", nationality: "british"}, {id: "2" , name: "archie", age: "25", nationality: "british"}, {id: "3" , name: "john", age: "25", nationality: "british"},     {id: "4", name: "alex", age: "27", nationality: "french"}]);

Documents in the collection after insert

db.collection.find().pretty()
{
        "_id" : ObjectId("58ac57e742616c14fcf43aaf"),
        "id" : "1",
        "name" : "archie",
        "age" : "25",
        "nationality" : "british"
}
{
        "_id" : ObjectId("58ac57e742616c14fcf43ab0"),
        "id" : "2",
        "name" : "archie",
        "age" : "25",
        "nationality" : "british"
}
{
        "_id" : ObjectId("58ac57e742616c14fcf43ab1"),
        "id" : "3",
        "name" : "john",
        "age" : "25",
        "nationality" : "british"
}
{
        "_id" : ObjectId("58ac57e742616c14fcf43ab2"),
        "id" : "4",
        "name" : "alex",
        "age" : "27",
        "nationality" : "french"
}

Update Query

db.collection.update({name:"archie", age:"25", nationality:"british"}, {$set:{tag:"value2"}}, {multi:true});

Note that I have used $set and multi.

$set - to set only new tags, additional to existing values

multi - to update multiple documents

After update query is executed

db.collection.find().pretty()


{
        "_id" : ObjectId("58ac57e742616c14fcf43aaf"),
        "id" : "1",
        "name" : "archie",
        "age" : "25",
        "nationality" : "british",
        "tag" : "value2"
}
{
        "_id" : ObjectId("58ac57e742616c14fcf43ab0"),
        "id" : "2",
        "name" : "archie",
        "age" : "25",
        "nationality" : "british",
        "tag" : "value2"
}
{
        "_id" : ObjectId("58ac57e742616c14fcf43ab1"),
        "id" : "3",
        "name" : "john",
        "age" : "25",
        "nationality" : "british"
}
{
        "_id" : ObjectId("58ac57e742616c14fcf43ab2"),
        "id" : "4",
        "name" : "alex",
        "age" : "27",
        "nationality" : "french"
}

All Documents with matching name, age and nationality alone are updated.

Upvotes: 1

Vao Tsun
Vao Tsun

Reputation: 51519

You can prepare tag value based on existing row:

> db.coll.insert([     {id: "5" , name: "archie", age: "25", nationality: "british", tag: "value2"},     {id: "6" , name: "archie", age: "25", nationality: "british", tag: "value2"},     {id: "7" , name: "john", age: "25", nationality: "british"},     {id: "8" , name: "alex", age: "27", nationality: "french", tag: "value1"} ]);
BulkWriteResult({
        "writeErrors" : [ ],
        "writeConcernErrors" : [ ],
        "nInserted" : 4,
        "nUpserted" : 0,
        "nMatched" : 0,
        "nModified" : 0,
        "nRemoved" : 0,
        "upserted" : [ ]
})
> var obIns = {id: "1" , name: "archie", age: "25", nationality: "british"};
> db.coll.find({name:obIns.name, nationality: obIns.nationality}).limit(1).forEach(function(o){obIns.tag = o.tag;db.coll.insert(obIns);});
> db.coll.find();
{ "_id" : ObjectId("58ac489e9daf1be6a0d0456a"), "id" : "5", "name" : "archie", "age" : "25", "nationality" : "british", "tag" : "value2" }
{ "_id" : ObjectId("58ac489e9daf1be6a0d0456b"), "id" : "6", "name" : "archie", "age" : "25", "nationality" : "british", "tag" : "value2" }
{ "_id" : ObjectId("58ac489e9daf1be6a0d0456c"), "id" : "7", "name" : "john", "age" : "25", "nationality" : "british" }
{ "_id" : ObjectId("58ac489e9daf1be6a0d0456d"), "id" : "8", "name" : "alex", "age" : "27", "nationality" : "french", "tag" : "value1" }
{ "_id" : ObjectId("58ac48b49daf1be6a0d0456e"), "id" : "1", "name" : "archie", "age" : "25", "nationality" : "british", "tag" : "value2" }

Upvotes: 0

Related Questions