Updating objects within Nested Array in mongodb with multiple criteria

Question

An example document in mongo looks as follows however my collection has a couple of thousand documents, where some have all of the following tests, and some have only a subset of the following tests:

{
"_id" : ObjectId("52435f0f6f73205f7d37a2b0"),
"ID" : {
    "schoolID" : "1234"
},
"institution" : {
    "tests" : [
        {
            "test" : "SAT Math",
            "25th_percentile" : null,
            "mean" : 404,
            "50th_percentile" : null,
            "75th_percentile" : null
        },
        {
            "test" : "SAT Verbal",
            "25th_percentile" : null,
            "mean" : 355,
            "50th_percentile" : null,
            "75th_percentile" : null
        },
        {
            "test" : "SAT Writing",
            "25th_percentile" : null,
            "mean" : 363,
            "50th_percentile" : null,
            "75th_percentile" : null
        },
        {
            "test" : "SAT Composite",
            "25th_percentile" : null,
            "mean" : 1122,
            "50th_percentile" : null,
            "75th_percentile" : null
        },
        {
            "test" : "ACT Math",
            "25th_percentile" : null,
            "mean" : null,
            "50th_percentile" : null,
            "75th_percentile" : null
        },
        {
            "test" : "ACT English",
            "25th_percentile" : null,
            "mean" : null,
            "50th_percentile" : null,
            "75th_percentile" : null
        },
        {
            "test" : "ACT Reading",
            "25th_percentile" : null,
            "mean" : null,
            "50th_percentile" : null,
            "75th_percentile" : null
        },
        {
            "test" : "ACT Science",
            "25th_percentile" : null,
            "mean" : null,
            "50th_percentile" : null,
            "75th_percentile" : null
        },
        {
            "test" : "ACT Composite",
            "25th_percentile" : null,
            "mean" : null,
            "50th_percentile" : null,
            "75th_percentile" : null
        }
    ]
}
}

I have currently received separate data for a couple of schools and want to update the document where ID is "schoolID" and where "test" is "ACT Composite" and "mean" is null.

I have tried the following:

db.collection.update({$and:[{"ID.schoolID":"1234"},    {"institution.tests.$.test": "ACT Composite"}, {"institution.tests.$.mean": null}]}, {"$set":{"institution.tests.$.mean":"trial"}})

however, the object was not updated. I have also tried using $elemMatch:

db.collection.update({$and:[{"ID.schoolID":"1234"},{"institution.tests": {$elemMatch:{"test": "ACT Composite", "mean":null}}}]},{"institution.tests.$.mean":"trial"})

when using $elemMatch I was able to get a count of the number of documents where this is true:

db.collection.find({"institution.tests":{$elemMatch:{"test":"ACT Composite", "mean":null}}}).count()

Lastly, I have also tried writing a python script using pymongo which turns the object into a pandas dataframe ( as I am more familiar as to how to do this in R using a dataframe and an "ifelse", however, I was having trouble only updating the specific object where "test" is "ACT Composite" and "mean" is null.

Any help would be greatly appreciated! Thank you in advance!

whoami - fakeFaceTrueSoul · Accepted Answer · 2020-06-01 18:58:19Z

2

You can do it as follows :

db.collection.update(
  {
    "ID.schoolID": "1234",
    "institution.tests": { $elemMatch: { test: "ACT Composite", mean: null } },
  },
  { $set: { "institution.tests.$.mean": "trial" } }
);

edited Jun 1, 2020 at 18:58

whoami - fakeFaceTrueSoul

18.1k6 gold badges35 silver badges51 bronze badges

answered Dec 9, 2013 at 16:04

Parvin Gasimzade

26.1k8 gold badges58 silver badges83 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

RCN Over a year ago

Thank you for the suggestion!! unfortunately, that did not update the document :( Instead it updated where "test": "ACT Math"

Parvin Gasimzade Over a year ago

I have updated query. Could you please try it again.

Collectives™ on Stack Overflow

Updating objects within Nested Array in mongodb with multiple criteria

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related