I'm working on an mongo database with mongoose in Node.js.

I have a collection like this :

{
   cinema: 'xxx',
   account: 'yyy',
   media: {
       data: [{
           id: 1,
           name: 'zzz'
       }, {
           id: 2,
           name: 'zzz'
       },
       ...
       ]
   }
}

Schema:

const dataCacheSchema = new mongoose.Schema(
    {
        cinemaName: String,
        account: String
    },
    { timestamps: true, strict: false }
);

The list of data can be long. I would like to retrieve a single media from a findOne query :

{
   id: 1,
   name: 'zzz'
}

I've tried like this :

    const doc: mongoose.Document[] | null = await DataCache.findOne(
        {
            cinemaName: ci.cinema,
            account: ci.account,
            'media.data': { $exists: true, $elemMatch: { id: mediaId } }
        },
        { media: { data: { $elemMatch: { id: mediaId } } } }
    ).exec();

But it crashes with the error message :

MongoError: Cannot use $elemMatch projection on a nested field.

CodePudding user response：

You can do it with Aggregation Framework:

• $match - to filter documents based on document fields
• $project with $filter - to filter and return only media field
• $first- to return only the first item from the array generated above
• $replaceRoot - to return the value of media field directly and not as nested property

db.collection.aggregate([
  {
    "$match": {
      "cinemaName": "name 1",
      "account": "account 1",
      "media.data.id": "1"
    }
  },
  {
    "$project": {
      "media": {
        "$first": {
          "$filter": {
            "input": "$media.data",
            "cond": {
              "$eq": [
                "$$this.id",
                "1"
              ]
            }
          }
        }
      }
    }
  },
  {
    "$replaceRoot": {
      "newRoot": "$media"
    }
  }
])

Working example

CodePudding user response：

You can use aggregate query to get expected result:

db.aggregate(
{
  $match: {
    cinema: "xx3",
    account: "yy3",
  },
},
  { $unwind: "$media.data" },
  { $match: { "media.data.id": 1 } },
  { $project: {
    cinema: 1,
    account: 1,
    "media.data": [{id: "$media.data.id", name: "$media.data.name"}]
  } }
);
// Output: { "_id" : ObjectId("..."), "media" : { "data" : [ { "id" : 1, "name" : "zzz" } ] } }

db.aggregate(
{
  $match: {
    cinema: "xx3",
    account: "yy3",
  },
},
  { $unwind: "$media.data" },
  { $match: { "media.data.id": 1 } },
  { $project: {
    "result": "$media.data"
  } }
);
// Output: { "_id" : ObjectId("..."), "result" : { "id" : 1, "name" : "zzz" } }

db.aggregate(
{
  $match: {
    cinema: "xx3",
    account: "yy3",
  },
},
  { $unwind: "$media.data" },
  { $match: { "media.data.id": 1 } },
  { $replaceRoot: {
      newRoot: "$media.data"
  }}
);
// Output: { "id" : 1, "name" : "zzz" }