Using C# code Get Distinct values from MongoDB array where it is matching some values specifically

Question

I am new to the MongoDB. I have one document named Documents in which I have multiple data as mentioned below

Data 1 -

{
    "ProjId": 18586,
    "ArtifactAttributes": [
        {
            
            "AttributeName": "Author",
            "AttributeValue": "XYZ"
            
        },
        {
            
            "AttributeName": "Address",
            "AttributeValue": "Addrr1"
            
        },
        {
            
            "AttributeName": "Owner",
            "AttributeValue": "manoj.naik"
            
        }
    ]
}

Data 2 -

{
    "ProjId": 18587,
    "ArtifactAttributes": [
        {
            
            "AttributeName": "Author",
            "AttributeValue": "ABC"
            
        },
        {
            
            "AttributeName": "Address",
            "AttributeValue": "Addrr2"
            
        },
        {
            
            "AttributeName": "Owner",
            "AttributeValue": "kumar.manoj"
            
        }
    ]
}

Data - 3

{
    "ProjId": 18588,
    "ArtifactAttributes": [
        {
            
            "AttributeName": "Author",
            "AttributeValue": "PQR"
            
        },
        {
            
            "AttributeName": "Address",
            "AttributeValue": "Addrr3"
            
        },
        {
            
            "AttributeName": "Owner",
            "AttributeValue": "kumar.manoj"
            
        }
    ]
}

I want to return distinct value where AttributeName is equals to Owner.

Expected Result - [manoj.naik,kumar.manoj]

For that I have written code as below but it is not returning expected result instead of that it is returning all distinct values from AttributeValue not specifically from AttributeName where it has Owner

Result which I am getting is as below

[XYZ,Addrr1,manoj.naik,ABC,Addrr2,kumar.manoj,PQR,Addrr3]

My C# Code -

var ownerFilter = Builders<Documents>.Filter.ElemMatch(x => x.ArtifactAttributes, p => p.AttributeName.Equals("Owner"));
var ownerValueFieldDefinition = new StringFieldDefinition<Documents, string>("ArtifactAttributes.AttributeValue");
var distinctItems = _projectArtifacts.Distinct(ownerValueFieldDefinition, ownerFilter).ToList();

Yonatan · Accepted Answer · 2021-12-01 23:00:30Z

1

It doesn't work because ElemMatch matches any document that has an element with AttributeName == "Owner" in its ArtifactAttributes. It returns original unmodified documents, it doesn't magically remove values of ArtifactAttributes that you don't need.

As far as I know, there is no elegant way to do what you want, but you can do this:

var distinctItems = _projectArtifacts
    .Aggregate()
    .Unwind(x => x.ArtifactAttributes)
    .Match(x => x["ArtifactAttributes.AttributeName"] == "Owner")
    .Group("{ _id: '$ArtifactAttributes.AttributeValue' }")
    .ToList()
    .Select(x => x["_id"].AsString)
    .ToList();

Step-by-step explanation:

Aggregate - start an aggregation pipeline.
Unwind - replace each document with its copies, each containing only one of the values of ArtifactAttributes.
Match - filter documents, leaving only those where AttributeName == "Owner".
Group - group documents by AttributeValue. This allows to get all the distinct values in the _id field.
ToList - get results from the database. Aggregation always returns a list of documents, not just values. So you have to do some more steps to get list of values.
Select and ToList - get list of values using LINQ.

answered Dec 1, 2021 at 23:00

Yonatan

6407 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Manoj Naik Over a year ago

thanks. I tried var ownerData = _projectArtifacts.Aggregate() .Unwind(doc => doc.ArtifactAttributes) .ReplaceRoot<ArtifactAttributes>("$ArtifactAttributes") .Match(attr => attr.AttributeName.Equals("Owner") && attr.AttributeValue != null && attr.AttributeValue != "") .Project(b => new ArtifactAttributes { AttributeValue = b.AttributeValue}) .ToList(); var ownerList = ownerData.Select(x => x.AttributeValue).Distinct().Where(y => y != null).OrderBy(p => p).ToList(); this one and it was working.

Manoj Naik Over a year ago

But you answer is more reliable hence i will go with that

Collectives™ on Stack Overflow

Using C# code Get Distinct values from MongoDB array where it is matching some values specifically

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related