I have a document in MongoDB that looks like this:

  "_id": {
    "$oid": "620d69bd82a231557c4cbcf3"
  },
  "data": {
    "61e1de61c58c136d92570505": {
      "61bcd0c44a81621116162562": {
        "$date": {
          "$numberLong": "1645574400000"
        }
      }
    },
    "61d776ed0e027669a7070bc9": {
      "61e1df8713008628654a1fe2": {
        "$date": {
          "$numberLong": "1645527811222"
        }
      }
    }
  },
  "itemsToKeep": {
    "paths": [
      "61e1de61c58c136d92570505.61bcd0c44a81621116162562"
    ]
  }
}

And my aim is to filter the objects stored within the data property by the paths that exist in the itemsToKeep.paths property — i.e. after the operation is complete, the document looks like this:

  "_id": {
    "$oid": "620d69bd82a231557c4cbcf3"
  },
  "data": {
    "61e1de61c58c136d92570505": {
      "61bcd0c44a81621116162562": {
        "$date": {
          "$numberLong": "1645574400000"
        }
      }
    }
  },
  "itemsToKeep": {
    "paths": [
      "61e1de61c58c136d92570505.61bcd0c44a81621116162562"
    ]
  }
}

The paths are dynamic so I can't hard-code anything. I'd like to do this inside an aggregation query if possible, because there is a substantial amount of data to process — if I have to resort to fetching the data and processing it outside Mongo I can, but it's not ideal.

Typically I'd be looking at using $objectToArray and $filter but due to the nesting and dynamic nature of the paths involved I can't see how it'd work.

The itemsToKeep property is flexible — I could store the data in a different format if it would make filtering the data property easier.

I'm hoping someone could point me in the right direction :)

CodePudding user response：

One option is to use $objectToArray with $map and $reduce:

1. First $objectToArray and create paths.
2. Second level $objectToArray and keep only relevant second level paths on each item: innerPaths
3. Filter data to keep only items that match itemsToKeep (now matching innerPaths with item's internal.
4. Reformat again using $arrayToObject and filter empty entries using $reduce.
5. Format

db.collection.aggregate([
  {$set: {
      data: {$objectToArray: "$data"},
      paths: {$map: {
          input: "$itemsToKeep.paths",
          in: {$split: ["$$this", "."]}
      }}
  }},
  {$set: {
      data: {$map: {
          input: "$data",
          as: "item",
          in: {
            externalK: "$$item.k",
            internal: {$objectToArray: "$$item.v"},
            innerPaths: {$reduce: {
                input: "$paths",
                initialValue: [],
                in: {"$concatArrays": [
                    "$$value",
                    {$cond: [
                        {$eq: ["$$item.k", {$first: "$$this"}]},
                        [{$last: "$$this"}],
                        []
                    ]}
                ]}
            }}
        }}
      }
  }},
  {$set: {
      data: {$map: {
          input: "$data",
          in: {
            externalK: "$$this.externalK",
            internal: {$filter: {
                input: "$$this.internal",
                as: "item",
                cond: {$in: ["$$item.k", "$$this.innerPaths"]}
            }}
          }
      }}
  }},
  {$set: {
      data: {$reduce: {
          input: "$data",
          initialValue: [],
          in: {"$concatArrays": [
              "$$value",
              {$cond: [{$gt: [{$size: "$$this.internal"}, 0]},
                  [{k: "$$this.externalK", v: {$arrayToObject: "$$this.internal"}}],
                  []
              ]}
          ]}
      }}
  }},
  {$project: {itemsToKeep: 1, data: {$arrayToObject: "$data"}}}
])

See how it works on the playground example