如何解决Cosmos中QueryDefinition参数化IN子句失效问题

问题背景 #

直接拼接字符串的Cosmos查询可正常执行，但使用QueryDefinition做参数化传递时，IN子句无法匹配数据；尝试用ARRAY_CONTAINS替换IN也未生效。已知stockNumbers值为["11", "22"]，filters?.LineItemState值为"someString"。

可正常运行的字符串拼接代码 #

var allStockNumbers = string.Join(", ", stockNumbers.Select(x => "\"" + x + "\""));
var query = !string.IsNullOrWhiteSpace(filters?.LineItemState)
    ? $"SELECT f.id, f.location, f.stockNumber, f.vin, f.totalCarliCost, f.totalCostSubmitted, f.createdDateTime, f.createdBy, f.earliestPaymentDate, f.paymentItems, ARRAY(SELECT * FROM c IN f.lineItems where c.itemState = {"\"" + filters.LineItemState + "\"" }) as lineItems FROM f WHERE f.stockNumber IN ({allStockNumbers})"
    : $"SELECT f.id, f.location, f.stockNumber, f.vin, f.totalCarliCost, f.totalCostSubmitted, f.createdDateTime, f.createdBy, f.earliestPaymentDate, f.paymentItems, f.lineItems FROM f WHERE f.stockNumber IN ({allStockNumbers})";

失效的参数化查询代码 #

var allStockNumbers = string.Join(", ", stockNumbers.Select(x => "\"" + x + "\""));
QueryDefinition query;
if (!string.IsNullOrWhiteSpace(filters?.LineItemState))
{
    var lineItemState = "\"" + filters.LineItemState + "\"";
    query = new QueryDefinition(
            @"SELECT f.id, f.location, f.stockNumber, f.vin, f.totalCarliCost, f.totalCostSubmitted, f.createdDateTime, f.createdBy, f.earliestPaymentDate, f.paymentItems, ARRAY(SELECT * FROM c IN f.lineItems where c.itemState = @lineItemState) as lineItems FROM f WHERE f.stockNumber IN (@allStockNumbers)"
        ).WithParameter("@lineItemState", @lineItemState)
        .WithParameter("@allStockNumbers", allStockNumbers);
}
else
{
    query = new QueryDefinition(
        @"SELECT f.id, f.location, f.stockNumber, f.vin, f.totalCarliCost, f.totalCostSubmitted, f.createdDateTime, f.createdBy, f.earliestPaymentDate, f.paymentItems, f.lineItems FROM f WHERE f.stockNumber IN (@allStockNumbers)"
    ).WithParameter("@allStockNumbers", allStockNumbers);
}

失效的ARRAY_CONTAINS尝试代码 #

var allStockNumbers = string.Join(", ", stockNumbers.Select(x => "\"" + x + "\""));
QueryDefinition query;
if (!string.IsNullOrWhiteSpace(filters?.LineItemState))
{
     var lineItemState = "\"" + filters.LineItemState + "\"";
     query = new QueryDefinition(
              "SELECT f.id, f.location, f.stockNumber, f.vin, f.totalCarliCost, f.totalCostSubmitted, f.createdDateTime, f.createdBy, f.earliestPaymentDate, f.paymentItems, ARRAY(SELECT * FROM c IN f.lineItems where c.itemState = @lineItemState) as lineItems FROM f WHERE ARRAY_CONTAINS(@allStockNumbers, f.stockNumber)"
          ).WithParameter("@lineItemState", lineItemState)
          .WithParameter("@allStockNumbers", allStockNumbers);
}
else
{
    query = new QueryDefinition(
        @"SELECT f.id, f.location, f.stockNumber, f.vin, f.totalCarliCost, f.totalCostSubmitted, f.createdDateTime, f.createdBy, f.earliestPaymentDate, f.paymentItems, f.lineItems FROM f WHERE f.stockNumber IN (@allStockNumbers)"
    ).WithParameter("@allStockNumbers", allStockNumbers);
}

错误原因分析 #

1. 数组参数处理错误：将stockNumbers数组拼接成带引号的字符串传递给参数，Cosmos DB会将其识别为单个字符串值，而非数组，导致IN子句或ARRAY_CONTAINS无法匹配。
2. 字符串参数额外加引号：给@lineItemState传递的是带引号的字符串（如"\"someString\""），而实际数据中的itemState值是"someString"，导致匹配失败。

正确解决方案 #

直接传递原始数组和字符串作为参数，无需额外拼接或加引号：

修正后的参数化查询代码 #

QueryDefinition query;
if (!string.IsNullOrWhiteSpace(filters?.LineItemState))
{
    query = new QueryDefinition(
            @"SELECT f.id, f.location, f.stockNumber, f.vin, f.totalCarliCost, f.totalCostSubmitted, f.createdDateTime, f.createdBy, f.earliestPaymentDate, f.paymentItems, ARRAY(SELECT * FROM c IN f.lineItems where c.itemState = @lineItemState) as lineItems FROM f WHERE f.stockNumber IN (@allStockNumbers)"
        ).WithParameter("@lineItemState", filters.LineItemState)
        .WithParameter("@allStockNumbers", stockNumbers); // 直接传递数组
}
else
{
    query = new QueryDefinition(
        @"SELECT f.id, f.location, f.stockNumber, f.vin, f.totalCarliCost, f.totalCostSubmitted, f.createdDateTime, f.createdBy, f.earliestPaymentDate, f.paymentItems, f.lineItems FROM f WHERE f.stockNumber IN (@allStockNumbers)"
    ).WithParameter("@allStockNumbers", stockNumbers); // 直接传递数组
}

用ARRAY_CONTAINS的修正代码（可选） #

如果更倾向于用ARRAY_CONTAINS，同样直接传递数组参数：

QueryDefinition query;
if (!string.IsNullOrWhiteSpace(filters?.LineItemState))
{
    query = new QueryDefinition(
            @"SELECT f.id, f.location, f.stockNumber, f.vin, f.totalCarliCost, f.totalCostSubmitted, f.createdDateTime, f.createdBy, f.earliestPaymentDate, f.paymentItems, ARRAY(SELECT * FROM c IN f.lineItems where c.itemState = @lineItemState) as lineItems FROM f WHERE ARRAY_CONTAINS(@allStockNumbers, f.stockNumber)"
        ).WithParameter("@lineItemState", filters.LineItemState)
        .WithParameter("@allStockNumbers", stockNumbers);
}
else
{
    query = new QueryDefinition(
        @"SELECT f.id, f.location, f.stockNumber, f.vin, f.totalCarliCost, f.totalCostSubmitted, f.createdDateTime, f.createdBy, f.earliestPaymentDate, f.paymentItems, f.lineItems FROM f WHERE ARRAY_CONTAINS(@allStockNumbers, f.stockNumber)"
    ).WithParameter("@allStockNumbers", stockNumbers);
}

说明 #

Cosmos DB的参数化查询支持直接传递数组类型参数，IN子句会自动解析数组中的元素；字符串参数直接传递原始值即可，无需手动添加引号，QueryDefinition会自动处理参数的类型转换和转义。

内容的提问来源于stack exchange，提问作者GThree