CosmosDB-子文档Delselecting-LINQ查询 [英] CosmosDB - SubDocument Delselecting - LINQ Query

问题描述

我在CosmosDB中有一个ProductDocument模型，它代表一个产品.在该模型中，有一个子文档contributors，其中包含谁对产品做出了贡献.每个贡献者都有一个role.

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. 仅选择contributor.roleDescription为 Author
的ProductDocument
2. 仅选择ProductDocument和division为 Pub 1
3. 在结果集中仅包含contributors子文档，其contributor.roleDescription为 Author .

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.

现在我正在努力:

1. 上面选择的第3部分.由于结果集同时包含 Author 的contributor.roleDescription和 Illustrator
，我该如何完成此操作

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

宇宙模型示例:

[

    {
        "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"
            }
        ]

    }]

这是我在数据资源管理器中一​​直使用的SQL:

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'

这是我通过服务提供的LINQ查询:

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

它选择了正确的记录，但是如何取消选择贡献者的 Illustrator 子文档，因为目前我得到以下信息:

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"
            }
        ]

    }

但是我想要的是以下输出，但不包括Illustrator贡献者子文档:

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"
            }

        ]

    }

1. 如果子文档contributor.roleDescription之一等于Author，我想对Product进行过滤.因此，如果产品"记录中不包含作者"贡献者，则我不希望它

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

我想包含每个等于 Author 的contributor子文档.因此，如果Product有多个作者贡献者子文档，我想包含它们，但要排除 Illustrator 的那些.

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.

您可能具有ProductDocuments的集合.

对流利的LINQ语法的帮助会很有帮助.

Help on the fluent LINQ syntax would help greatly.

推荐答案

Azure CosmosDB现在支持子查询.使用子查询，您可以通过两种方式执行此操作，但有细微差别:

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

1. 您可以在投影中的子查询中使用ARRAY表达式，过滤掉不需要的贡献者，并投影所有其他属性.该查询假设您需要一个选择属性列表，以便从数组中投影出来.

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"

这假定您需要首先对"Pub 1"除法进行过滤，然后再对带有ARRAY表达式的子查询进行过滤.

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

1. 或者，如果您想要整个文档以及过滤的贡献者，则可以执行以下操作:

1. 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"

这将在标记为"c"的属性中以"Pub 1"划分的原始文档投影，并在标记为"contributors"的属性中单独过滤一个过滤的贡献者数组.您可以为过滤的贡献者引用这个贡献者数组，而忽略文档中的那个.

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.

这篇关于CosmosDB-子文档Delselecting-LINQ查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！