Query mongo document array

Question

I have the next mongo document structure :
_id
     -countryCode
     -keywordID
     -name
-displayName
-categories:[Array]
     -_id
     -name
     -position
-canonical
I would like to get all the keywords that are in a specific category only knowing the category's ID. I am using the mongo C# driver but don't know how could I check what's inside that array.
I would like to send a list with the category ID's and get back all the keywords that have a category from that list.

public async Task<List<Keyword>> GetKeywords(List<long> keywordCatIds, string countryCode)
{
    var mongoCollection = MongoDatabase.GetCollection<Keyword>("Keywords");
    try
    {
        FilterDefinition<Keyword> mongoFilter = Builders<Keyword>.Filter.In(c=>c.Categories, keywordCatIds);
        return await mongoCollection.Find(mongoFilter,null).ToListAsync<Keyword>();
    }
    catch (Exception ex)
    {
        Logger.Error(ex, "Multiple ids for Country Code: {0}, ids: {1}", countryCode, string.Join(',', keywordCatIds.Select(s => s)));
        return null;
    }

}

Answer 1

Your In function looks like a "categories._id" filter in normal mongoDB. Which transitions into an ElemMatch. I created a project which fills the db, than selects

all the keywords that are in a specific category only knowing the category's ID

public class CustomID
{
    public string CountryCode { get; set; }
    public long KeywordId { get; set; }
    public string Name { get; set; }
}

public class Keyword
{
    [BsonId]
    public CustomID Id { get; set; }
    public List<Category> Categories { get; set; }
}

public class Category
{
    [BsonId]
    public long Id { get; set; }
    public string Name { get; set; }
    public int Position { get; set; }
}

internal class Program
{
    public static IMongoDatabase MongoDatabase { get; private set; }

    public static async Task Main()
    {
        var conventionPack = new ConventionPack
        {
            new CamelCaseElementNameConvention()
        };

        ConventionRegistry.Register(
            "CustomConventionPack",
            conventionPack,
            t => true);
        var client = new MongoClient();
        MongoDatabase = client.GetDatabase("SO");
        var ret = await GetKeywords(new List<long> {1L, 2L}, "HU-hu");
        // ret is A and B. C is filtered out because no category id of 1L or 2L, D is not HU-hu
    }

    public static async Task<List<Keyword>> GetKeywords(List<long> keywordCatIds, string countryCode)
    {
        var mongoCollection = MongoDatabase.GetCollection<Keyword>("keywords");
        // be ware! removes all elements. For debug purposes uncomment>
        //await mongoCollection.DeleteManyAsync(FilterDefinition<Keyword>.Empty);
        await mongoCollection.InsertManyAsync(new[]
        {
            new Keyword
            {
                Categories = new List<Category>
                {
                    new Category {Id = 1L, Name = "CatA", Position = 1},
                    new Category {Id = 3L, Name = "CatC", Position = 3}
                },
                Id = new CustomID
                {
                    CountryCode = "HU-hu",
                    KeywordId = 1,
                    Name = "A"
                }
            },
            new Keyword
            {
                Categories = new List<Category>
                {
                    new Category {Id = 2L, Name = "CatB", Position = 2}
                },
                Id = new CustomID
                {
                    CountryCode = "HU-hu",
                    KeywordId = 2,
                    Name = "B"
                }
            },
            new Keyword
            {
                Categories = new List<Category>
                {
                    new Category {Id = 3L, Name = "CatB", Position = 2}
                },
                Id = new CustomID
                {
                    CountryCode = "HU-hu",
                    KeywordId = 3,
                    Name = "C"
                }
            },
            new Keyword
            {
                Categories = new List<Category>
                {
                    new Category {Id = 1L, Name = "CatA", Position = 1}
                },
                Id = new CustomID
                {
                    CountryCode = "EN-en",
                    KeywordId = 1,
                    Name = "EN-A"
                }
            }
        });
        var keywordFilter = Builders<Keyword>.Filter;
        var categoryFilter = Builders<Category>.Filter;
        var mongoFilter =
            keywordFilter.ElemMatch(k => k.Categories, categoryFilter.In(c => c.Id, keywordCatIds)) &
            keywordFilter.Eq(k => k.Id.CountryCode, countryCode);
        return await mongoCollection.Find(mongoFilter).ToListAsync();
    }
}