Automating updates to specific array items within documents

Working with Data
,
1

I want to filter genetic variants that contain the ‘Amino Acid Change’: p.Ala161Thr’, and then $set a nested field for ‘Predicted cDNA’: ‘c.481G>A’ for arrays that contain ‘Variant (cDNA)’: ‘c.?’.

I have tried multiple things with nothing working.

db.LCFAOD.updateMany({“Variant.Amino Acid Change”:“p.Ala161Thr”},{$set: {“Variant.$[Predicted cDNA]”:“c.481G>A”}},{ arrayFilter: { “Variant.Variant (cDNA)”:“c.?” } })
MongoServerError: No array filter found for identifier ‘Predicted cDNA’ in path ‘Variant.$[Predicted cDNA]’

db.LCFAOD.updateMany({“Variant.Amino Acid Change”:“p.Ala161Thr”},{$set: {“Variant.Variant (cDNA”:“c.481G>A”}})
MongoServerError: Cannot create field ‘Variant (cDNA’ in element {Variant: [ { Gene: (continues on)

I am hoping to automate this once I figure out the correct way to call them, so I don’t want to manually use the Variant.0 method because it may not always be the first array. Was hoping to have queries tell it which array to update.

Thank you!

2

When specifying arrayFilters: (not arrayFilter:{} like you wrote), you will see in the documentation that the syntax is

{ arrayFilters : [ {  Filter_Identifier : Filter_Condition } ] }

The Filter_Identifier is the name you have to use withing your $set.

The following untested code should be bring you closer your goal.

db.LCFAOD.updateMany( { "Variant.Amino Acid Change" : "p.Ala161Thr" } ,
  { "$set" :  {
      "Variant.$[has_variant_c].Predicted cDNA" : "c.481G>A"
  }  } ,
  { arrayFilters : [
    {
      "has_variant_c" : { "Variant (cDNA)" : "c.?" }
    }
  ] }
)
3

Thank you very much for your help and time. I do not quite understand the ‘has_variant_c’ component, but I see what you mean regarding the square brackets vs curly.

When I try your suggestion, I get: [MongoServerError:] Error parsing array filter :: caused by :: The top-level field name must be an alphanumeric string beginning with a lowercase letter, found ‘has_variant_c’.

I have gotten this error with other options I have tried. Could you possibly explain the has_variant_c so I can further trouble shoot using your suggestion?

Thank you again!
Heather

4

If you look at the documentation I share you will see that has_variant_c is my filter <identifier>.

You did not share any sample documents so it is very hard to supply a tested solution, so it is very hard to see what I missed.

Try with this variant where I replaced has_variant_c to hasVariant (it looks like underscore is not liked). Also read my own document link to fix the filter specification.

db.LCFAOD.updateMany( { "Variant.Amino Acid Change" : "p.Ala161Thr" } ,
  { "$set" :  {
      "Variant.$[hasVariant].Predicted cDNA" : "c.481G>A"
  }  } ,
  { arrayFilters : [
    {
      "hasVariant.Variant (cDNA)" : "c.?"
    }
  ] }
)

I still do not know if it does the right thing, from the lack of sample documents to test, but at least there is no error.

5

And when I read my the documentation I shared I saw:

The <identifier> must begin with a lowercase letter and contain only alphanumeric characters.

Shame on me in trying to make it readable by using _.

6

Apologies; I did read the documentation but am not quite understanding it well enough to assume how to use the ‘has’ component.

A sample document is below:

 "Variant": [
    {
      "Gene": "ACADVL",
      "Genomic Coordinate GRCh37": "17:g.7124860?",
      "Genomic Coordinate GRCh38": "17:g.7221541?",
      "Transcript": "NM_000018.4",
      "Variant (cDNA)": "c.?",
      "Start Position": "481",
      "Variant Location": "Exon 7",
      "HGVS": "NM_000018.4:c.?",
      "Effect Type": "Missense",
      "Amino Acid Change": "p.Ala161Thr",
      "Predicted ACMG Call": "likely_pathogenic",
      "GNOMAD Frequency": "NA"
    },
    {
      "Gene": "ACADVL",
      "Genomic Coordinate GRCh37": "17:g.7125591?",
      "Genomic Coordinate GRCh38": "17:g.7222272?",
      "Transcript": "NM_000018.4",
      "Variant (cDNA)": "c.?",
      "Start Position": "848",
      "Variant Location": "Exon 9",
      "HGVS": "NM_000018.4:c.?",
      "Amino Acid Change": "p.Val283Ala",
      "Predicted ACMG Call": "likely_pathogenic"
    }
  ]

Your suggestion worked, but unfortunately for me it adds the Predicted cDNA field to each array item (This is because there are two variants with c.? but different amino acid outcomes. I tried editing your script to include the additional filter so that it is only added to array items that include both cDNA "c.?’ and amino acid change of “p.Ala161Thr”. However, it seems I cannot use hasVariant twice, and am getting a clone error when playing with the code. I still do not quite understand the different between hasVariant vs Variant.Variant (cDNA).

db.LCFAOD.updateMany( {
    "Variant.Amino Acid Change": "p.Ala161Thr"
},
{
    "$set": {
        "Variant.$[hasVariant].Predicted cDNA": "c.481G>A"
    }
},
{ arrayFilters : [
        {
            "hasVariant.Variant (cDNA)": "c.?",
            "hasVariant.Amino Acid Change": "p.Ala161Thr"
        }
    }
]
})

Error: clone(t={}){const r=t.loc||{};return e({loc:new Position("line"in r?r.line:this.loc.line,"column"in r?r.column:……)} could not be cloned.

db.LCFAOD.updateMany( {
    "Variant.Amino Acid Change": "p.Ala161Thr"
},
{
    "$set": {
        "Variant.$[hasVariant].Predicted cDNA": "c.481G>A"
    }
},
{ arrayFilters : [
        {
            "hasVariant.Variant (cDNA)": "c.?"
        },
        {
            "hasVariant.Amino Acid Change": "p.Ala161Thr"
        }
    ]
}
)

MongoServerError: Found multiple array filters with the same top-level field name hasVariant

Again, appreciate the help. I am quite new to Mongo

7

Try

{ arrayFilters : [ hasVariant : { $elemMatch : { 
    "Variant (cDNA)" : "c.?",
     "Amino Acid Change" : "p.Ala161Thr"
} } ] }
8

Thank you for an additional suggestion. I moved to the terminal because mongoDB compass was giving me a weird line clone error.

I updated the code to:

db.LCFAOD.updateMany( 
{ "Variant.Amino Acid Change" : "p.Ala161Thr" } , 
{ $set :  { "Variant.$[hasVariant].Predicted cDNA" : "c.481G>A" }  } ,
{ arrayFilters : [ hasVariant : { $elemMatch : { "Variant (cDNA)" : "c.?", "Amino Acid Change" : "p.Ala161Thr"} } ] }
} )

That gives this error:
ncaught exception: SyntaxError: unexpected token: identifier :

I was trying additional options and tried something similar to this help page, but get a similar error.

db.LCFAOD.updateMany(
{ "Variant.Amino Acid Change" : "p.Ala161Thr"}, 
{ $set: { "Variant.$[g].Predicted cDNA" : "c.481G>A" } }, 
{ arrayFilters: [ { “g.Variant (cDNA)" : "c.?", “g.Amino Acid Change" : "p.Ala161Thr" } ] }
)

And I get this error: uncaught exception: SyntaxError: illegal character :

9

In

arrays are a list of simple values, arrays or objects. With hasVariant: you are defining an object so you are missing curly braces arround the field name hasVariant and its value.

In

you have wrong quotes at the start and end.

1 Like
10

Oh thank you so much. Wow, it is always the little things. It worked with this code:

db.LCFAOD.updateMany( 
    {"Variant.Amino Acid Change": "p.Ala161Thr"},
{"$set": {"Variant.$[g].Predicted cDNA": "c.481G>A"}
},
{ arrayFilters : [
{
    "g.Variant (cDNA)": "c.?",
    "g.Amino Acid Change": "p.Ala161Thr"
}
]})

Again, thank you so much for the persistent help. Greatly appreciated.

H

1 Like
11

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.