How do I return mongo document where item is in an array?

Question

I have the following document in a mongodb collection called "Items"

"_id": {
        "$oid": "5fcfa614d588d46ec44cc375"
    },
    "ProductId": {
        "$binary": {
            "base64": "LredEr/+9UGY3g5oIDWhPw==",
            "subType": "03"
        }
    },
    "ProductName": "ModelProductTest",
    "AccountId": {
        "$binary": {
            "base64": "Gzmr+JvFq0y0YRBOfkj57w==",
            "subType": "03"
        }
    },
    "Skus": [
        {
            "_id": {
                "$oid": "5fcfa614d588d46ec44cc376"
            },
            "ProductSkuId": {
                "$binary": {
                    "base64": "MBLj5bL6C0Cph0fMkpbDZA==",
                    "subType": "03"
                }
            },
            "ProductId": {
                "$binary": {
                    "base64": "LredEr/+9UGY3g5oIDWhPw==",
                    "subType": "03"
                }
            },
            "SkuCode": "kkss",
            "Barcode": "12345",
            "Description": "test",
            "StockQuantity": {
                "$numberInt": "2"
            },
            "Costs": [
                {
                    "_id": {
                        "$oid": "5fcfa614d588d46ec44cc378"
                    },
                    "ProductPriceId": {
                        "$binary": {
                            "base64": "81rqEsKc9k6y2IQQjE1DJg==",
                            "subType": "03"
                        }
                    },
                    "ProductSkuId": {
                        "$binary": {
                            "base64": "MBLj5bL6C0Cph0fMkpbDZA==",
                            "subType": "03"
                        }
                    },
                    "DateValidFrom": {
                        "$date": {
                            "$numberLong": "1607443988489"
                        }
                    },
                    "Cost": "33"
                },
                {
                    "_id": {
                        "$oid": "5fcfa614d588d46ec44cc379"
                    },
                    "ProductPriceId": {
                        "$binary": {
                            "base64": "aqdf9S6yUUCIZEjLY/1pvw==",
                            "subType": "03"
                        }
                    },
                    "ProductSkuId": {
                        "$binary": {
                            "base64": "MBLj5bL6C0Cph0fMkpbDZA==",
                            "subType": "03"
                        }
                    },
                    "DateValidFrom": {
                        "$date": {
                            "$numberLong": "1607443988492"
                        }
                    },
                    "Cost": "39"
                }
            ]
        },
        {
            "_id": {
                "$oid": "5fcfa614d588d46ec44cc377"
            },
            "ProductSkuId": {
                "$binary": {
                    "base64": "XFkTknYw2Uyy2ae/F/yj1A==",
                    "subType": "03"
                }
            },
            "ProductId": {
                "$binary": {
                    "base64": "LredEr/+9UGY3g5oIDWhPw==",
                    "subType": "03"
                }
            },
            "SkuCode": "kksass",
            "Barcode": "12346",
            "Description": "test",
            "StockQuantity": {
                "$numberInt": "6"
            },
            "Costs": [
                {
                    "_id": {
                        "$oid": "5fcfa614d588d46ec44cc37a"
                    },
                    "ProductPriceId": {
                        "$binary": {
                            "base64": "hxWucmMsnkOkRb1j9/EtGA==",
                            "subType": "03"
                        }
                    },
                    "ProductSkuId": {
                        "$binary": {
                            "base64": "XFkTknYw2Uyy2ae/F/yj1A==",
                            "subType": "03"
                        }
                    },
                    "DateValidFrom": {
                        "$date": {
                            "$numberLong": "1607443988492"
                        }
                    },
                    "Cost": "31"
                },
                {
                    "_id": {
                        "$oid": "5fcfa614d588d46ec44cc37b"
                    },
                    "ProductPriceId": {
                        "$binary": {
                            "base64": "ExyZdI6suE2Zs5oRdeiK9w==",
                            "subType": "03"
                        }
                    },
                    "ProductSkuId": {
                        "$binary": {
                            "base64": "XFkTknYw2Uyy2ae/F/yj1A==",
                            "subType": "03"
                        }
                    },
                    "DateValidFrom": {
                        "$date": {
                            "$numberLong": "1607443988492"
                        }
                    },
                    "Cost": "36"
                }
            ]
        }
    ]

I want to get the main object and the specific sku (not all the skus) returned when I search by barcode. How do I do this in C# using the mongo driver?

Answer 1

you can do it easily with the AsQueryable interface of the official driver like so:

collection.AsQueryable()
          .Where(i => i.Skus.Any(s => s.Barcode == "22222222")))
          .Select(i => new Item
          {
              Id = i.Id,
              ProductName = i.ProductName,
              Skus = i.Skus.Where(s => s.Description == "second sku")
          })
          .ToListAsync();

you need to do a manual projection for each property of the main entity you need returned and simply filter the Skus with a Where clause.

the above will get translated into an aggregation pipeline like this:

[
    {
        "$match": {
            "Skus": {
                "$elemMatch": {
                    "Barcode": "22222222"
                }
            }
        }
    },
    {
        "$project": {
            "_id": "$_id",
            "ProductName": "$ProductName",
            "Skus": {
                "$filter": {
                    "input": "$Skus",
                    "as": "s",
                    "cond": {
                        "$eq": [
                            "$$s.Barcode",
                            "22222222"
                        ]
                    }
                }
            }
        }
    }
]

test program using mongodb.entities:

using MongoDB.Bson;
using MongoDB.Driver;
using MongoDB.Driver.Linq;
using MongoDB.Entities;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace TestApplication
{
public class Item : Entity
{
    public string ProductName { get; set; }
    public IEnumerable<Sku> Skus { get; set; }
}

public class Sku
{
    [ObjectId]
    public string Id { get; set; }
    public string Barcode { get; set; }
}

public static class Program
{
    private static async Task Main()
    {
        await DB.InitAsync("test");

        await new Item
        {
            ProductName = "test product",
            Skus = new[] {
                new Sku { Id = ObjectId.GenerateNewId().ToString(), Barcode = "11111111" },
                new Sku { Id = ObjectId.GenerateNewId().ToString(), Barcode = "22222222" },
                new Sku { Id = ObjectId.GenerateNewId().ToString(), Barcode = "33333333" }
            }
        }.SaveAsync();

        var item = await DB.Queryable<Item>()
                           .Where(i => i.Skus.Any(s => s.Barcode == "22222222"))
                           .Select(i => new Item
                           {
                               ID = i.ID,
                               ProductName = i.ProductName,
                               Skus = i.Skus.Where(s => s.Barcode == "22222222")
                           })
                           .ToListAsync();
    }
}
}