Remove multiple inconsistent objects from double nested array 5

Question

here is another challenge: I need to clean my data from incorrect objects , objects under the array "t" that contain did , dst and den fields are considered correct , @nimrok serok / @rickhg12hs helped with a working solution , but still there is some edge cases where none of objects are valid and stay empty array after the update , so I am wondering if those can be cleared in same update query?

example document:

{
"_id": ObjectId("5c05984246a0201286d4b57a"),
f: "x",
"_a": [
  {
    "_onlineStore": {}
  },
  {
    "_p": {
      "pid": 1,
      "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",
              
            }
          ]
        }
      },
      h: "This must stay"
    }
  },
  {
    "_p": {
      "pid": 2,
      "s": {
        "a": {
          "t": [
            {
              id: 1,
              "dateP": "20200-09-20",
              
            }
          ]
        },
        "c": {
          "t": [
            {
              id: 3,
              "dateP": "20300-09-22"
            },
            {
              id: 4,
              "dateP": "20300-09-23",
              
            }
          ]
        }
      },
      h: "This must stay"
    }
  },
  {
    x: "This must stay"
  }
]
}

Expected output:

 {
"_a": [
  {
    "_onlineStore": {}
  },
  {
    "_p": {
      "h": "This must stay",
      "pid": 1,
      "s": {
        "a": {
          "t": [
            {
              "dateP": "20200-09-20",
              "den": "z",
              "did": "x",
              "dst": "y",
              "id": 1
            }
          ]
        }
      }
    }
  },
  {
    "_p": {
      "h": "This must stay",
      "pid": 2,
    }
  },
  {
    "x": "This must stay"
  }
],
"_id": ObjectId("5c05984246a0201286d4b57a"),
"f": "x"
}

Playground (As you can see in the playground example , job is almost done , just for cases where all array elements are wrong the array stay empty , so it need to be removed as well ...)

mongodb version 4.4

R2D2 · Accepted Answer · 2022-11-09 21:27:03Z

It touk me some time , but here is the solution for those who face similar problem:

db.collection.update({},
[
{
"$set": {
  _a2: {
    $filter: {
      input: "$_a",
      as: "elem",
      cond: {
        "$eq": [
          {
            "$type": "$$elem._p.s"
          },
          "missing"
        ]
      }
    }
   },
   _a: {
    $filter: {
      input: "$_a",
      as: "elem",
      cond: {
        "$ne": [
          {
            "$type": "$$elem._p.s"
          },
          "missing"
        ]
      }
    }
    }
   }
   },
   {
   "$set": {
   "_a": {
    "$map": {
      "input": "$_a",
      "as": "elem",
      "in": {
        "$mergeObjects": [
          "$$elem",
          {
            "_p": {
              "$mergeObjects": [
                "$$elem._p",
                {
                  s: {
                    "$arrayToObject": {
                      "$map": {
                        "input": {
                          "$objectToArray": "$$elem._p.s"
                        },
                        "as": "anyKey",
                        "in": {
                          "k": "$$anyKey.k",
                          "v": {
                            "t": {
                              "$filter": {
                                "input": "$$anyKey.v.t",
                                "as": "t",
                                "cond": {
                                  "$setIsSubset": [
                                    [
                                      "did",
                                      "dst",
                                      "den"
                                    ],
                                    {
                                      "$map": {
                                        "input": {
                                          "$objectToArray": "$$t"
                                        },
                                        "in": "$$this.k"
                                      }
                                    }
                                  ]
                                }
                              }
                            }
                          }
                        }
                      }
                    }
                  }
                }
              ]
            }
          }
        ]
      }
    }
   }
  }
 },
 {
 "$set": {
  "_a": {
    "$map": {
      "input": "$_a",
      "as": "elem",
      "in": {
        "$mergeObjects": [
          "$$elem",
          {
            "_p": {
              "$mergeObjects": [
                "$$elem._p",
                {
                  s: {
                    "$arrayToObject": {
                      "$filter": {
                        "input": {
                          "$objectToArray": "$$elem._p.s"
                        },
                        "as": "anyKey",
                        "cond": {
                          $ne: [
                            "$$anyKey.v.t",
                            []
                          ]
                        }
                      }
                    }
                  }
                }
              ]
            }
          }
        ]
      }
    }
   }
   }
  },
  {
   "$set": {
  "_a": {
    "$map": {
      "input": "$_a",
      "as": "elem",
      "in": {
        "$mergeObjects": [
          "$$elem",
          {
            "_p": {
              "$arrayToObject": {
                "$filter": {
                  "input": {
                    "$objectToArray": "$$elem._p"
                  },
                  "as": "anyKey",
                  cond: {
                    $not: {
                      $in: [
                        "$$anyKey.v",
                        [
                          {}
                        ]
                      ]
                    }
                  }
                }
              }
            }
          }
        ]
      }
    }
   }
  }
 },
 {
 $set: {
  _a: {
    "$concatArrays": [
      "$_a2",
      "$_a"
    ]
  }
 }
 },
 {
   $unset: "_a2"
 }
 ])

Explained:

Split the array in two arays via $set/$filter , _a2 (contain elements that will not be changed ) and _a ( contain the affected inconsistent )
$map/$mergeObjects/$mergeObjects/$map/$arrayToObject to remove the inconsistent objects inside _a[]._p.s.k.t[]
$map/$mergeObjects/$mergeObjects/$map/$arrayToObject/$filter to remove the empty _a[]._p.s.k.t[] arrays t with theyr keys k.
$map/$mergeObjects/$mergeObjects/$map/$arrayToObject/$filter to remove the empty _a[]._p.s:{} elements.
$concat on _a and _a2 to concatenete the fixed _a[] array elements with the ones that are correct and preserved in _a2[].
$unset the temporary array _a2[] since it has been already concatenated with _a[] in previous stage.

Special thanks to @nimrod serok & @rickhg12hs for the initial ideas!

Playground

Collectives™ on Stack Overflow

Remove multiple inconsistent objects from double nested array 5

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related