azure - 查询 Azure DocumentDB 中子集合的子集合

Question

我在 Cosmos 中有一个 Azure DocumentDB json 存储，并且我正在尝试对集合内的集合应用互斥条件 where 子句。我期望查询如何工作并没有返回预期结果!

假设我有一个产品类:

public class Product
{
    public int Id { get; set; }

    public string Name { get; set; }

    public IEnumerable<ProductAttribute> ProductAttributes { get; set; }
}

它有一个 ProductAttributes 集合，而 ProductAttributes 又包含一个字符串集合:

  public class ProductAttribute
{
    public string Name { get; set; }

    public IEnumerable<string> AttributeValues { get; set; }
}

如果我创建 2 个示例产品并将它们添加到 DocumentDB 集合中:

    var product = new Product
                {
                    Id = 1,
                    Name = "Banana",
                    ProductAttributes = new List<ProductAttribute>
                    {
                        new ProductAttribute{Name = "SourceCountry",AttributeValues = new List<string>{ "COL", "HON", "SA" }},
                        new ProductAttribute{Name = "Colours",AttributeValues = new List<string>{ "Yellow" }},
                    }
                };

                var product1 = new Product
                {
                    Id = 2,
                    Name = "Grape",
                    ProductAttributes = new List<ProductAttribute>
                    {
                        new ProductAttribute{Name = "SourceCountry",AttributeValues = new List<string>{ "FRA", "ITA", "SA" }},
                        new ProductAttribute{Name = "Colours",AttributeValues = new List<string>{ "Red", "Green" }},
                    }
                };

文档存储为以下 JSON:

{
"id": "1",
"name": "Banana",
"productAttributes": [
  {
    "name": "SourceCountry",
    "attributeValues": [
      "COL",
      "HON",
      "SA"
    ]
  },
  {
    "name": "Colours",
    "attributeValues": [
      "Yellow"
    ]
  }
]}

和

{
"id": "2",
"name": "Grape",
"productAttributes": [
  {
    "name": "SourceCountry",
    "attributeValues": [
      "FRA",
      "ITA",
      "SA"
    ]
  },
  {
    "name": "Colours",
    "attributeValues": [
      "Red",
      "Green"
    ]
  }
]}

我想查询产品以仅返回那些具有与两种 ProductAttribute 类型的条件相匹配的属性的产品。

以下对单个属性的查询确实按预期返回两种产品(Grape 和 Banana 都包含“SA”的 SourceCountry:

select p.name, p.productAttributes from c as p
join pa in p.productAttributes
join pav in pa.attributeValues
where (pa.name='SourceCountry' and pav = 'SA')

但是，我需要能够在两种类型的属性(即“SourceCountry”和“Colours”)中应用条件 - 所以我尝试了以下查询:

select p.name, p.productAttributes from c as p
join pa in p.productAttributes
join pav in pa.attributeValues
where (pa.name='SourceCountry' and pav = 'SA')
and (pa.name='Colours' and pav = 'Red')

我期望从上述查询中返回“Grape”，因为该产品是唯一同时具有“SA”“SourceCountry”属性和“Red”“Colour”属性的产品。

但是，没有任何产品被退回，我非常感谢有人能解释为什么会出现这种情况!

Answer 1

完全在黑暗中拍摄，但任何可能有效的机会:

select p.name, p.productAttributes from c as p
join pa in p.productAttributes
join pav in pa.attributeValues    
join pa2 in p.productAttributes
join pav2 in pa2.attributeValues
where (pa.name='SourceCountry' and pav = 'SA')
and (pa2.name='Colours' and pav2 = 'Red')