In my collection, I have an array of embedded documents like so:
{
"_id" : ObjectId("63506c5db40233003a1252ab"),
"filters" : [
{
"jaql" : {
"dim" : "country.Country"
}
},
{
"jaql" : {
"dim" : "[Commerce.Date (Calendar)]"
}
},
{
"jaql" : {
"dim" : "[Commerce.Revenue]"
}
},
{
"jaql" : {
"dim" : "[Country.Name]"
}
}
]
}
I want to update the embedded document’s field “dim” : “country.Country” and add the missing square brackets to the value so I want to reuse the value and have “dim” : “[country.Country]” in the end
When I don’t use aggregation and use arrayFilters I cannot reuse the value with $value and have to refer to the value explicitly, so the script is not universal:
db.dashboards.updateMany({"filters.jaql.dim":"country.Country"},{"$set":{"filters.$[elem].jaql.dim":"[country.Country]"}},{arrayFilters:[{"elem.jaql.dim":"country.Country"}]})
When I use aggregation I can use a pattern and reuse the value of the field, but cannot reach the embedded document: (simplified the script by just trying to reuse the value with $)
db.dashboards.updateMany({"filters.jaql.dim":{"$regex":/^[^[].*[^]]/}},[{"$set":{"filters":{"$map":{"input":"$filters","in":{"jaql.dim":"$$this.jaql.dim"}}}}}])
WriteError({
What is the best way to achieve my goal?
steevej
October 20, 2022, 12:55am
2
I think that in your $map, the in expression has to use $mergeObjects . Something along the following untested lines:
{ "$map" : {
"input" : "$filters" ,
"in" : { "$mergeObjects" : [
"$$this" ,
{ "jaql.dim" : { "$concat" : [ "[" , "$$this.jaql.dim" , "]" ] } }
] }
} }
Please be safe!
Thank you @steevej . Unfortunately it still doesn’t work for me: I still cannot access the embedded document:
db.dashboards.updateMany({"filters.jaql.dim":{"$regex":/^[^[].*[^]]/}},
[{"$set":
{"filters":
{ "$map" : {
"input" : "$filters" ,
"in" : { "$mergeObjects" : [
"$$this" ,
{ "jaql.dim" : { "$concat" : [ "[" , "$$this.jaql.dim" , "]" ] } }
]}
}}
}}])
WriteError({
when I remove the ‘.’ in the field key and leave it in the value it works:
db.dashboards.updateMany({"filters.jaql.dim":{"$regex":/^[^[].*[^]]/}},
[{"$set":
{"filters":
{ "$map" : {
"input" : "$filters" ,
"in" : { "$mergeObjects" : [
"$$this" ,
{ "jaql" : "$$this.jaql" }
]}
}}
}}])
but I still need to access the embedded document.
@steevej FYI the structure of the filters array is the following:
"filters" : [
{
"jaql" : {
"datasource" : {
"title" : "Sample Healthcare",
"fullname" : "LocalHost/Sample Healthcare",
"id" : "localhost_aSampleIAAaHealthcare",
"address" : "localHost",
"database" : "aSampleIAAaHealthcare"
},
"column" : "Gender",
"dim" : "Patients.Gender",
"datatype" : "text",
"filter" : {
"explicit" : false,
"multiSelection" : true,
"all" : true
},
"title" : "GENDER",
"collapsed" : true
},
"isCascading" : false
}]
steevej
October 26, 2022, 10:02pm
5
You make me work hard. But it makes me learn. B-)
The error message made me think that we may need a second level of $mergeObjects.
With the following:
set = [{"$set":
{"filters":
{ "$map" : {
"input" : "$filters" ,
"in" : { "$mergeObjects" : [
"$$this" ,
{ "jaql" : { "$mergeObjects" : [ "$$this.jaql" , { "dim" : { "$concat" : [ "[" , "$$this.jaql.dim" , "]" ] } } ] } }
]}
}}
}}]
I do NOT get the error
and your sample document from your last post is updated to:
{ _id: ObjectId("6359ab29cbf1ad6771bd5290"),
filters:
[ { jaql:
{ datasource:
{ title: 'Sample Healthcare',
fullname: 'LocalHost/Sample Healthcare',
id: 'localhost_aSampleIAAaHealthcare',
address: 'localHost',
database: 'aSampleIAAaHealthcare' },
column: 'Gender',
dim: '[Patients.Gender]',
datatype: 'text',
filter: { explicit: false, multiSelection: true, all: true },
title: 'GENDER',
collapsed: true },
isCascading: false } ] }
1 Like
@steevej perfect! You’re a genius! This script works marvelously and takes a few seconds to run instead of the previous JavaScript with ‘forEach’ which used to run for several minutes.
The lesson learned: dot notation doesn’t allow to access embedded documents when using $set with aggregation pipelines. The workaround is to use $mergeObjects. It looks like a good feature request for MongoDB.
1 Like
system
November 2, 2022, 3:40pm
7
This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.