I'm having difficulties putting up a code which returns an element in an array of subdocuments. I am actually trying to flatten a document to a new document which is strongly typed. My document is looking like;

{ 
    "_id" : BinData(3, "7FRf4nbe60ev6XmGKBBW4Q=="), 
    "status" : NumberInt(1), 
    "title":"Central station",
     "attributes" : [
        {
            "defId" : BinData(3, "QFDtR03NbkqwuhhG76wS8g=="), 
            "value" : "388", 
            "name" : null
        }, 
        {
            "defId" : BinData(3, "RE3MT3clb0OdLEkkqhpFOg=="), 
            "value" : "", 
            "name" : null
        }, 
        {
            "defId" : BinData(3, "pPgJR50h8kGdDaCcH2o17Q=="), 
            "value" : "Merkez", 
            "name" : null
        }
    ]}

What I am trying to achieve is;

{
  "title":"Central Station",
  "value":"388"
}

What I've done already;

  using (_dbContext)
        {
            var filter = Builders<CustomerModel>.Filter.Eq(q => q.Id, Guid.Parse("30B59585-CBFC-4CD5-A43E-0FDB0AE3167A")) &
                Builders<CustomerModel>.Filter.ElemMatch(f => f.Attributes, q => q.DefId == Guid.Parse("47ED5040-CD4D-4A6E-B0BA-1846EFAC12F2"));

            var projection = Builders<CustomerModel>.Projection.Include(f => f.Title).Include("attributes.value");
            var document = _dbContext.Collection<CustomerModel>().Find(filter).Project(projection).FirstOrDefault();
            if (document == null)
                return null;
            return BsonSerializer.Deserialize<TitleAndValueViewModel>(document);
        }

Note: TitleAndCodeViewModel contains title and value properties.

This block of code returns;

{{ "_id" : CSUUID("30b59585-cbfc-4cd5-a43e-0fdb0ae3167a"), "title" : "388 güvenevler", "attributes" : [{ "value" : "388" }, { "value" : "" }, { "value" : "Merkez " }] }}

I am trying to get "value":"388" but instead I am getting another two value properties even tough the ElemMatch filter added for subdocument.

Thank you for your help in advance.

Note: I am looking for answers in C# mongodb driver.

CodePudding user response：

Option 1: ( via aggregation)

db.collection.aggregate([
{
$match: {
  _id: 5,
  "attributes.defId": 1
  }
},
{
"$addFields": {
  "attributes": {
    "$filter": {
      "input": "$attributes",
      "as": "a",
      "cond": {
        $eq: [
          "$$a.defId",
          1
        ]
      }
    }
  }
}
},
{
  $unwind: "$attributes"
},
{
  $project: {
    _id: 0,
    title: 1,
    value: "$attributes.value"
  }
 }
])

Explained:

1. Match ( good to add index for the matching fields )
2. Filter only the attribute you need
3. Unwind to convert the array to object
4. Project only the necessary output

Playground

Option 2: ( find/$elemMatch )

db.collection.find({
 _id: 5,
  attributes: {
  "$elemMatch": {
    "defId": 1
   }
 }
},
{
  _id: 0,
  title: 1,
 "attributes": {
    "$elemMatch": {
        "defId": 1
     }
  }
})

Explained:

1. Match the element via _id and elemMatch the attribute
2. Project the necessary elements. ( Note here elemMatch also need to be used to filter the exact match attribute ) ( Note this version will not identify if there is second attribute with same attribute.defId , also projection of attribute will be array with single element if found that need to be considered from the app side )

Playground 2

CodePudding user response：

by specify defId

db.collection.aggregate(

    [{
        $project: {
            title: '$title',
            attributes: {
                $filter: {
                    input: '$attributes',
                    as: 'element',
                    cond: { $eq: ['$$element.defId', BinData(3, 'QFDtR03NbkqwuhhG76wS8g==')] }
                }
            }
        }
    }, {
        $project: {
            _id: 0,
            title: '$title',
            value: { $first: '$attributes.value' }
        }
    }]



)

resulf

{
  "title": "Central station",
  "value": "388"
}