CosmosDB-SubDocument Delselecting-LINQ查询[英] CosmosDB - SubDocument Delselecting - LINQ Query

本文是小编为大家收集整理的关于CosmosDB-SubDocument Delselecting-LINQ查询的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

我在cosmosdb中有一个ProductDocument模型,它代表了一个产品.在该模型中有一个子文档contributors,其持有谁为产品有贡献.每个贡献者都有一个role.

现在我一直在尝试需要的查询:

  1. 只选择contributor.roleDescription 作者
  2. 只选择division pub 1
  3. 的division
  4. 在结果集中仅包含contributor.roleDescription的contributor.roleDescription的子文档作者.

现在我正在努力:

  1. 选择上面的第3部分.如何完成该位,因为我的结果集包括作者和插图
  2. 的contributor.roleDescription

示例cosmos型号:

[

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

    }]

这是我在 data explorer 中播放的SQL:

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

这是我服务的LINQ查询:

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

它选择正确的记录,但我如何选择贡献者的 illartator 子文件,因为目前我得到了以下内容:

   {
        "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贡献者子文档:

 {
        "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. 我想在Product上过滤,如果其中一个子文档contributor.roleDescription等于作者.因此,如果产品记录不包含作者贡献者,我不希望它

  2. 我希望包括等于作者的每个contributor子文档.因此,如果有多个作者贡献者子文档为Product,我希望包含它们,但排除插图的.

  3. 您可以收集ProductDocuments.

  4. 流利的LINQ语法的帮助将有助于大大帮助.

推荐答案

azure cosmosdb现在支持子查询.使用亚查询,您可以通过两种方式执行此操作,具有轻微的差异:

  1. 您可以使用投影中的子查询中的数组表达式,过滤出您不想要的贡献者,并投影所有其他属性.此查询假定您需要选择属性列表,以与数组分开投影.

    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",然后是带数组表达式的子查询.

  1. 交替,如果您希望整个文档以及过滤的贡献者,您可以执行此操作:

    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"划分将原始文档分开,以及在标有"贡献者"的属性中分别的过滤的贡献者阵列.您可以为过滤的贡献者引用此贡献者阵列,并忽略文档中的贡献者.

其他推荐答案

这将做你想做的事,但显然如果你有多个你想要表现出你想要的多个贡献者,那么如果这是你想要的,那么你的问题很难告诉你的问题P>

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'

和输出是:

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

注意,贡献者不是列表,它是一个值,所以如果多个贡献者匹配过滤器,那么您将获得多次返回的同一产品.

本文地址:https://www.itbaoku.cn/post/1557203.html

问题描述

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.

推荐答案

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.

  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"
    

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.

其他推荐答案

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.