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();

CodePudding user response：

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:

1. Aggregate - start an aggregation pipeline.
2. Unwind - replace each document with its copies, each containing only one of the values of ArtifactAttributes.
3. Match - filter documents, leaving only those where AttributeName == "Owner".
4. Group - group documents by AttributeValue. This allows to get all the distinct values in the _id field.
5. 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.
6. Select and ToList - get list of values using LINQ.