Query mongo collection and exclude embedded document with unknown keys-CodePudding

I have a MongoDB collection with documents like:

    {
      _id: "...",
      name: "myName",
      versions: {
        1.0.0: {
          status: 'unpublished'
        },
        1.0.1: {
          status: 'published'
        }

        ...

        2.2.0: {
          status: 'unpublished'
        },
        14.3023.1: {
          status: 'published'
        }

        ...
      }

    }

How can I query a specific document within this collection, that has a given name, say "myName", and return the document, modified to exclude all versions that are unpublished?

I do not know the specific versions beforehand, ie; 1.0.0, 1.0.1 etc.

My attempt:

    findOne({
      name: "myName"
    }, {
      projection: {
        versions.$.status: {
           $cond: {
               if: { $eq: [ "unpublished", "$versions.$.status" ] },
               then: "$$REMOVE",
               else: "$versions.$.status"
            }
        }
      }
    })

CodePudding user response：

Using dynamic value as field name is generally considered as anti-pattern and should be avoided to reduce complexity of composing query. Nevertheless, you can achieve your expected behaviour with $objectToArray and $arrayToObject.

db.collection.aggregate([
  {
    $match: {
      name: "myName"
    }
  },
  {
    "$addFields": {
      "versions": {
        "$objectToArray": "$versions"
      }
    }
  },
  {
    "$addFields": {
      "versions": {
        "$filter": {
          "input": "$versions",
          "as": "ver",
          "cond": {
            $eq: [
              "$$ver.v.status",
              "published"
            ]
          }
        }
      }
    }
  },
  {
    "$addFields": {
      "versions": {
        "$arrayToObject": "$versions"
      }
    }
  }
])

Here is the Mongo Playground for your reference.