Complex WHERE clause on an array in Azure DocumentDb

1
votes

I have two documents which looks like this.

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

I have tried this:

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

I'd like to build a query (either in SQL or LINQ - as long as its executable by documentdb) that will retrieve all the documents 

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

Please do not suggest using ARRAY_CONTAINS as I need to leverage the indexes as the collection contains over 500,000 docs.

Thanks

2
linqazureazure-cosmosdb

2 Answers

2
votes

You can write a SQL query that looks like this - 

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")
0
votes

Don't use All, that only returns a boolean true if every single element satisfies your condition.

items = items.Where(x => 
            x.CustomFields.Where(cf => cf.CustomFieldId == "FirstName" && cf.StringValue == "John").Count() > 0
            && x.CustomFields.Where(cf.CustomFieldId == "LastName" && cf.StringValue == "Williams").Count() > 0);