linq - Azure DocumentDb 中数组的复杂 WHERE 子句

Question

我有两个如下所示的文档。

{      
  "CustomFields": [
    {
      "CustomFieldId": "FirstName",
      "StringValue": "John",          
    },
    {
      "CustomFieldId": "LastName",
      "StringValue": "Johnson",          
    }
  ],      
  "id": "f600bd8b-bca8-41a5-9f1c-0038e9cc7b35",      
},
{
  "CustomFields": [
    {
      "CustomFieldId": "FirstName",
      "StringValue": "John",          
    },
    {
      "CustomFieldId": "LastName",
      "StringValue": "Williams",          
    }
  ],      
  "id": "f600bd8b-bca8-41a5-9f1c-0038e9cc7b35",      
}

我已经尝试过这个:

items = items.Where(x => x.CustomFields.All(
                cf =>
                    cf.CustomFieldId == "01d1beab-8651-41df-ad93-ecc6195e912f" && cf.StringValue == "Pending"));

我想构建一个查询(使用 SQL 或 LINQ - 只要它可由 documentdb 执行)来检索所有文档

where (CustomFieldId == "FirstName" and StringValue == "John") 
  AND (CustomFieldId == "LastName" and StringValue == "Williams")

请不要建议使用ARRAY_CONTAINS，因为我需要利用索引，因为该集合包含超过 500,000 个文档。

谢谢

Answer 1

您可以编写如下所示的 SQL 查询 -

SELECT d 
FROM docs d 
JOIN f1 IN d.CustomFields 
JOIN f2 IN d.CustomFields 
WHERE (f1.CustomFieldId = "FirstName" and f1.StringValue == "John") 
AND (f2.CustomFieldId == "LastName" and f2.StringValue == "Williams")