[SERVER-53360] Support positional $ operator with aggregation pipeline for update operations Created: 15/Dec/20  Updated: 06/Dec/22

Status: Backlog
Project: Core Server
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Improvement Priority: Major - P3
Reporter: Adrian B Assignee: Backlog - Query Optimization
Resolution: Unresolved Votes: 1
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Assigned Teams:
Query Optimization
Participants:
Case:

 Description   

I couldn't find a way to update a nested array matching a specific query when using the aggregation pipeline for update operations.

Example :

I have the following data :

"_id" : ObjectId("5fd3ba7eb0b91523b424e612"),
"data-block-array" : 
[
  {
    "block-id"   : ObjectId("5fd3ba7eb0b91523b424e614")
    "metadata"   : {...},
    "data-value" : "Current value"
  },
  {
    "block-id" : ObjectId("5fd3ba7eb0b91523b424e616")
    "metadata" : {...},
    "data-value" : ""
  }
]

 

I want to update the data-value of the data block with "block-id" : ObjectId("5fd3ba7eb0b91523b424e614") for the document with "_id" :  ObjectId("5fd3ba7eb0b91523b424e612").

 

With the "classical" update (without pipeline), I can do :

db.getCollection('myCollection').updateOne(
  { 
    "_id" : ObjectId("5fd3ba7eb0b91523b424e612"), 
    "data-block-array.block-id": ObjectId("5fd3ba7eb0b91523b424e614")
  },
  {
    $set : 
    {
      "data-block-array.$."data-value" : "New value"
    }
  }
)

But with the following aggregation pipeline, that fails : 

db.getCollection('myCollection').updateOne(
 { 
   "_id" : ObjectId("5fd3ba7eb0b91523b424e612"), 
   "data-block-array.block-id": ObjectId("5fd3ba7eb0b91523b424e614")
 },
 {
   $set : 
   {
     "data-block-array.$."data-value" : "New value"
   }
 }
)

I get the error :

"Invalid $set :: caused by :: FieldPath field names may not start with '$'."

We also cannot use arrayFilters in an aggregation pipeline.

I've seen in the issue SERVER-40397 that you will never implement arrayFilters for the update with aggregation pipeline, but could you add the support for positional $ operator ?

I could use the other update version, but when you have in implementation (in C++ in my case) where we use polymorphism with a class method that returns a pipeline - which also supports things that we can't do with the other version - , it may be difficult to do so.

 



 Comments   
Comment by Edwin Zhou [ 17/Dec/20 ]

Hi adrian.blandin@sovo-tech.com,

Here's a pipeline that can help achieve your desired result:

blockId = ObjectId("5fd3ba7eb0b91523b424e614");
value = "New value";
db.collection.updateOne(
  {
    _id: ObjectId("5fd3ba7eb0b91523b424e612"),
    "data-block-array.block-id": blockId,
  },
  [
    {
      $set: {
        "data-block-array": {
          $cond: {
            if: {
              $in: [
                blockId,
                { $ifNull: ["$data-block-array.block-id", []] },
              ],
            },
            then: {
              $map: {
                input: "$data-block-array",
                in: {
                  $cond: {
                    if: {
                      $ne: [
                        "$$this.block-id",
                        blockId,
                      ],
                    },
                    then: "$$this",
                    else: {
                      "block-id": blockId,
                      "data-value": value,
                    },
                  },
                },
              },
            },
            else: {
              $concatArrays: [
                { $ifNull: ["$data-block-array", []] },
                [{ "block-id": blockId, "data-value": value }],
              ],
            },
          },
        },
      },
    },
  ],
  { upsert: true }
);

will output:

{
  _id: ObjectId("5fd3ba7eb0b91523b424e612"),
  "data-block-array": [
    {
      "block-id": ObjectId("5fd3ba7eb0b91523b424e614"),
      "data-value": "New value",
    },
    {
      "block-id": ObjectId("5fd3ba7eb0b91523b424e616"),
      "data-value": "",
    },
  ],
};

Best,

Edwin

Comment by Adrian B [ 17/Dec/20 ]

Ok, thanks .

Comment by Edwin Zhou [ 17/Dec/20 ]

Hi adrian.blandin@sovo-tech.com,

Thank you for the detailed example. We're assigning this ticket to the appropriate team to be evaluated against our currently planned work. Updates will be posted on this ticket as they happen.

Kind regards,
Edwin

Comment by Adrian B [ 15/Dec/20 ]

Woops, couldn't find how to edit the issue, but I forgot to change the version with the aggregation pipeline.

The square brackets encompassing the { $set :

{...}

} are missing (in the issue description, not in my tests).

Generated at Thu Feb 08 05:30:41 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.