CosmosDB - SubDocument Delselecting - LINQ Query

Question

I have a ProductDocument model in CosmosDB, which represents a Product. Within that model there is a subdocument contributors which holds who has contributed to the Product. Each contributor has a role.

Now I have been experimenting with a query that needs to:

1. Only select ProductDocument with a contributor.roleDescription of Author
2. Only select ProductDocument with a division of Pub 1
3. Only include contributors sub documents with a contributor.roleDescription of Author in the result set.

Now I'm struggling with:

1. Part 3 of select above. How do I accomplish this bit as my result set is including both contributor.roleDescription of Author AND Illustrator

Example Cosmos Model:

[

    {
        "id": "1",
        "coverTitle": "A Title",
        "pubPrice": 2.99,
        "division" :"Pub 1",
        "Availability": {
            "code": "20",
            "description": "Digital No Stock"
        },
        "contributors": [
            {
                "id": 1,
                "firstName": "Brad",
                "lastName": "Smith",
                "roleDescription": "Author",
                "roleCode": "A01"
            },
            {
                "id": 2,
                "firstName": "Steve",
                "lastName": "Bradley",
                "roleDescription": "Illustrator",
                "roleCode": "A12"
            }
        ]

    },
    {
        "id": "2",
        "coverTitle": "Another Title",
        "division" :"Pub 2",
        "pubPrice": 2.99,
        "Availability": {
            "code": "50",
            "description": "In Stock"
        },
        "contributors": [
            {
                "id": 1,
                "firstName": "Gareth Bradley",
                "lastName": "Smith",
                "roleDescription": "Author",
                "roleCode": "A01"
            }
        ]

    }]

Here is my SQL which I have been playing around with in the Data Explorer:

SELECT VALUE p
FROM Products p
JOIN c IN p.contributors
WHERE c.roleDescription = 'Author'
AND p.division = 'Pub 1'

Here is my LINQ query from my service:

        var query = client.CreateDocumentQuery<ProductDocument>(
            UriFactory.CreateDocumentCollectionUri("BiblioAPI", "Products"),
            new FeedOptions
            {
                MaxItemCount = -1,
                EnableCrossPartitionQuery = true
            }
            ) 
            .SelectMany(product  => product.Contributors
                .Where(contributor => contributor.RoleDescription == "Author")
                .Select(c => product)
                .Where(p => product.Division == "Pub 1"))
            .AsDocumentQuery();

        List<ProductDocument> results = new List<ProductDocument>();
        while (query.HasMoreResults)
        {
            results.AddRange(await query.ExecuteNextAsync<ProductDocument>());
        }

It selects the correct records, but how do I de-select the Illustrator sub document of contributor, because at the moment I get the following:

   {
        "id": "1",
        "coverTitle": "A Title",
        "pubPrice": 2.99,
        "division" :"Pub 1",
        "Availability": {
            "code": "20",
            "description": "Digital No Stock"
        },
        "contributors": [
            {
                "id": 1,
                "firstName": "Brad",
                "lastName": "Smith",
                "roleDescription": "Author",
                "roleCode": "A01"
            },
            {
                "id": 2,
                "firstName": "Steve",
                "lastName": "Bradley",
                "roleDescription": "Illustrator",
                "roleCode": "A12"
            }
        ]

    }

But the following output is what I want, excluding the Illustrator contributor sub document:

 {
        "id": "1",
        "coverTitle": "A Title",
        "pubPrice": 2.99,
        "division" :"Pub 1",
        "Availability": {
            "code": "20",
            "description": "Digital No Stock"
        },
        "contributors": [
            {
                "id": 1,
                "firstName": "Brad",
                "lastName": "Smith",
                "roleDescription": "Author",
                "roleCode": "A01"
            }

        ]

    }

EDIT:

1. I would like to filter on Product if one of the subdocument contributor.roleDescription equals Author. So if the Product record doesn't include a Author contributor I don't want it

2. I want to include each contributor subdocument that equals Author. So if there are multiple Author contributor subdocuments for a Product, I want to include them, but exclude the Illustrator ones.

3. You could have a Collection of ProductDocuments.

4. Help on the fluent LINQ syntax would help greatly.

Answer 1

Azure CosmosDB now supports subqueries. Using subqueries, you could do this in two ways, with minor differences:

1. You could utilize the ARRAY expression with a subquery in your projection, filtering out contributors that you don’t want, and projecting all your other attributes. This query assumes that you need a select list of attributes to project apart from the array.

   SELECT c.id, c.coverTitle, c.division, ARRAY(SELECT VALUE contributor from contributor in c.contributors WHERE contributor.roleDescription = "Author") contributors
   FROM c 
   WHERE c.division="Pub 1"
   

This assumes that you need to filter on division "Pub 1" first followed by the subquery with the ARRAY expression.

2. Alternately, if you want the entire document along with the filtered contributors, you could do this:

   SELECT c, ARRAY(SELECT VALUE contributor from contributor in c.contributors  WHERE contributor.roleDescription = "Author") contributors 
   FROM c 
   WHERE c.division="Pub 1"
   

This will project the original document with a "Pub 1" division in the property labeled "c", along with a filtered contributor array separately in the property labeled "contributors". You could refer this contributor array for your filtered contributors and ignore the one in the document.

Answer 2

This will do what you want, but obviously if you have multiple contributors you want to show it might not do quite what you are after - it's hard to tell with your question if that is what you want exactly

SELECT p.id, p.coverTitle, p.pubPrice, p.division, p.Availability, c as contributors
FROM Products p
JOIN c IN p.contributors
WHERE c.roleDescription = 'Author'
AND p.division = 'Pub 1'

and the output is:

[
    {
        "id": "1",
        "coverTitle": "A Title",
        "pubPrice": 2.99,
        "division": "Pub 1",
        "Availability": {
            "code": "20",
            "description": "Digital No Stock"
        },
        "contributors": {
            "id": 1,
            "firstName": "Brad",
            "lastName": "Smith",
            "roleDescription": "Author",
            "roleCode": "A01"
        }
    }
]

Note that contributors is not a list, it's a single value, so if multiple contributors match the filter, then you will get the same product returned multiple times.