对CosmosDB / DocumentDB的数组进行大小写不敏感的搜索(Case insensitive search in arrays for CosmosDB / DocumentDB)

 比方说，我有这些文件在我的CosmosDB。 （DocumentDB API，.NET SDK） 
 
{
    // partition key of the collection
    "userId" : "0000-0000-0000-0000",
    "emailAddresses": [
        "someaddress@somedomain.com", "Another.Address@someotherdomain.com"
    ]
    // some more fields
}
 
 我现在需要知道是否有给定电子邮件地址的文档。 但是，我需要查询不区分大小写。 
 
 有一些方法可以在字段中搜索不区分大小写的字段（但它们会执行全面扫描）： 
 
 如何在Azure DocumentDb上执行不区分大小写的搜索？ 
 
select * from json j where LOWER(j.name) = 'timbaktu'
e => e.Id.ToLower() == key.ToLower()
 
 这些不适用于数组。 有其他方法吗？ 用户定义的函数看起来可能有所帮助。 
 
 我主要是在寻找一种暂时的低效解决方案来支持这种情况（我有这样的多个集合）。 我可能需要在某个时候切换到这样的数据结构： 
 
{
    "userId" : "0000-0000-0000-0000",
    // Option A
    "emailAddresses": [
        {
            "displayName": "someaddress@somedomain.com",
            "normalizedName" : "someaddress@somedomain.com"
        },
        {
            "displayName": "Another.Address@someotherdomain.com",
            "normalizedName" : "another.address@someotherdomain.com"
        }
    ],
    // Option B
    "emailAddressesNormalized": {
        "someaddress@somedomain.com", "another.address@someotherdomain.com"
    }
}
 
 不幸的是，我的生产数据库已经包含需要更新以支持新结构的文档。 我的生产集合中只包含100个这样的项目，所以我甚至想要获取所有项目并在客户端的内存中进行比较。 

Lets say I have these documents in my CosmosDB. (DocumentDB API, .NET SDK)
 
{
    // partition key of the collection
    "userId" : "0000-0000-0000-0000",
    "emailAddresses": [
        "someaddress@somedomain.com", "Another.Address@someotherdomain.com"
    ]
    // some more fields
}
 
I now need to find out if I have a document for a given email address. However, I need the query to be case insensitive.
 
There are ways to search case insensitive on a field (they do a full scan however):
 
How to do a Case Insensitive search on Azure DocumentDb?
 
select * from json j where LOWER(j.name) = 'timbaktu'
e => e.Id.ToLower() == key.ToLower()
 
These do not work for arrays. Is there an alternative way? A user defined function looks like it could help.
 
I am mainly looking for a temporary low-effort solution to support the scenario (I have multiple collections like this). I probably need to switch to a data structure like this at some point:
 
{
    "userId" : "0000-0000-0000-0000",
    // Option A
    "emailAddresses": [
        {
            "displayName": "someaddress@somedomain.com",
            "normalizedName" : "someaddress@somedomain.com"
        },
        {
            "displayName": "Another.Address@someotherdomain.com",
            "normalizedName" : "another.address@someotherdomain.com"
        }
    ],
    // Option B
    "emailAddressesNormalized": {
        "someaddress@somedomain.com", "another.address@someotherdomain.com"
    }
}
 
Unfortunately, my production database already contains documents that would need to be updated to support the new structure. My production collections contain only 100s of these items, so I am even tempted to just get all items and do the comparison in memory on the client.

原文：https://stackoverflow.com/questions/47596474