Remove multiple elements from deeply nested array with single update query

Question

I have following type of documents:

[
 {
 "_id": ObjectId("5c05984246a0201286d4b57a"),
f: "x",
"_a": [
  {
    "_onlineStore": {}
  },
  {
    "_p": {
      "s": {
        "a": {
          "t": [
            {
              id: 1,
              "dateP": "20200-09-20",
              did: "x",
              dst: "y",
              den: "z"
            },
            {
              id: 2,
              "dateP": "20200-09-20"
            }
          ]
        },
        "c": {
          "t": [
            {
              id: 3,
              "dateP": "20300-09-22",
              
            },
            {
              id: 4,
              "dateP": "20300-09-23",
              did: "x",
              dst: "y",
              den: "z"
            },
            {
              id: 5,
              "dateP": "20300-09-23",
              
            }
          ]
        }
      }
    }
  }
]
}
]

I need to find update query that remove all documents that do not have "did" , "dst" and "den" fields , in the document nested array above this are the sub-objects with id:[2,3,5] , in the real collection I dont know the id's , is there option to remove all at once ?

My playground attempt , but unsuccessfull

The document after the update need to look like:

[
 {
 "_id": ObjectId("5c05984246a0201286d4b57a"),
f: "x",
"_a": [
  {
    "_onlineStore": {}
  },
  {
    "_p": {
      "s": {
        "a": {
          "t": [
            {
              id: 1,
              "dateP": "20200-09-20",
              did: "x",
              dst: "y",
              den: "z"
            }
          ]
        },
        "c": {
          "t": [
            {
              id: 4,
              "dateP": "20300-09-23",
              did: "x",
              dst: "y",
              den: "z"
            }
          ]
        }
      }
    }
  }
]
}
]

nimrod serok · Accepted Answer · 2022-11-03 20:41:52Z

1

Is this what you want?:

db.collection.update(
{},
[
  {
    $set: {
      "_a": {
        $map: {
          input: "$_a",
          as: "external",
          in: {
            $cond: [
              {
                $gt: [
                  "$$external._p",
                  0
                ]
              },
              {
                _p: {
                  s: {
                    a: {
                      t: {
                        $filter: {
                          input: "$$external._p.s.a.t",
                          as: "item",
                          cond: {
                            $and: [
                              {
                                $gt: [
                                  "$$item.did",
                                  0
                                ]
                              },
                              {
                                $gt: [
                                  "$$item.dst",
                                  0
                                ]
                              },
                              {
                                $gt: [
                                  "$$item.den",
                                  0
                                ]
                              }
                            ]
                          }
                        }
                      }
                    }
                  },
                  c: {
                    t: {
                      $filter: {
                        input: "$$external._p.s.c.t",
                        as: "item",
                        cond: {
                          $and: [
                            {
                              $gt: [
                                "$$item.did",
                                0
                              ]
                            },
                            {
                              $gt: [
                                "$$item.dst",
                                0
                              ]
                            },
                            {
                              $gt: [
                                "$$item.den",
                                0
                              ]
                            }
                          ]
                        }
                      }
                    }
                  }
                }
              },
              "$$external"
            ]
          }
        }
      }
    }
  }
], 
{multi:true})

See how it works on the playground example

answered Nov 3, 2022 at 20:41

nimrod serok

16.1k2 gold badges15 silver badges36 bronze badges

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

4 Comments

R2D2 Over a year ago

Exactly! Thanks alot @nimrod serok!

R2D2 Over a year ago

found one issue , not all documents have a.t and a.c , when one of them is missing "c": { "t": null } appear after update ... :( , how I can avoid that , I dont want the c.t:null to appear when there is no c in the original document ... I guess this is because of the $filter ?

R2D2 Over a year ago

I have 2x possible a.t and c.t , but they are not compulsory in all documents , even there is documents without both where after the update they will appear as c.t:null and a.t:null , but this is not expected to happen ...

R2D2 Over a year ago

added it as a new question here: stackoverflow.com/questions/74310123/…

Collectives™ on Stack Overflow

Remove multiple elements from deeply nested array with single update query

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related