在CosmosDB中查询JSON以查找违反varchar限制的字符串

Question

{
    "title": "My Json Data",
    "questions": [
      {
        "type": "Options",
        "position": 0,
        "text": "POTENTIONALLY LARGE STRING",
        "supportingQuestion": "POTENTIONALLY LARGE STRING",
        "options": [
          {
            "position": 0,
            "text": "Strongly Agree"
          },
          {
            "position": 1,
            "text": "Agree"
          },
          {
            "position": 2,
            "text": "Neutral"
          },
          {
            "position": 3,
            "text": "Disagree"
          },
          {
            "position": 4,
            "text": "Strongly Disagree"
          }
        ],
      },
      {
        "type": "Options",
        "position": 1,
        "text": "POTENTIONALLY LARGE STRING",
        "supportingQuestion": "POTENTIONALLY LARGE STRING",
        "options": [
          {
            "position": 0,
            "text": "Strongly Agree"
          },
          {
            "position": 1,
            "text": "Agree"
          },
          {
            "position": 2,
            "text": "Neutral"
          },
          {
            "position": 3,
            "text": "Disagree"
          },
          {
            "position": 4,
            "text": "Strongly Disagree"
          }
        ],
      }
    ]
}

我试着四处搜索，看看是否有任何操作符可以用于cosmos，但我一点运气都没有。

通过这样的查询，我可以搜索特定的违规者，但不能搜索我要搜索的一般违规者。

从c中选择*，其中ARRAY_CONTAINS(c.Questions,{text：“My question Text”},true）

        // Set some common query options.
        FeedOptions queryOptions = new FeedOptions { MaxItemCount = -1 };

        IQueryable<Forms> formQuery = client.CreateDocumentQuery<Forms>(
            UriFactory.CreateDocumentCollectionUri(DatabaseId, CollectionId), queryOptions)
            .Where(f => f.FormType == "Evaluation");

        // Execute the query synchronously. 
        Console.WriteLine("Running LINQ query...");
        foreach (Forms FormType in formQuery)
        {
            Console.WriteLine($"\tRead {FormType}");
        }

        // Now execute the same query using direct SQL.
        IQueryable<Forms> formQueryInSql = client.CreateDocumentQuery<Forms>(
            UriFactory.CreateDocumentCollectionUri(DatabaseId, CollectionId),
            selectLabel.Text + @" " + QueryBox.Text,
            //"SELECT * FROM c",
            queryOptions);

        Console.WriteLine("Running direct SQL query...");
        foreach (Forms Item in formQueryInSql)
        {
            Console.WriteLine($"\tRead {Item.Id}");
        }

Answer 1

看起来您使用的是SQL API，所以我将基于这个假设来回答。您可以创建一个查询，该查询遍历问题数组并提取要查找的字符串长度。

您可以通过门户或从API运行这些查询，但是门户有时会选择只处理数据子集，因此结果可能不准确。这意味着对于较大的数据集，您可能确实希望从代码中运行它们，但对值进行排序的工作将转移到CosmosDB，而不是试图在本地计算机上加载所有内容。

SELECT 
    c.position,
    c.text,
    LENGTH(c.text) AS textLength,
    c.supportingQuestion,
    LENGTH(c.supportingQuestion) AS supportingQuestionLength
FROM c IN items.questions
WHERE LENGTH(c.text) > 200 OR LENGTH(c.supportingQuestion) > 200

您的测试Json创建此输出（我将过滤器减少到20，以便得到结果）：

[
    {
        "position": 0,
        "text": "POTENTIONALLY LARGE STRING",
        "textLength": 26,
        "supportingQuestion": "POTENTIONALLY LARGE STRING",
        "supportingQuestionLength": 26
    },
    {
        "position": 1,
        "text": "POTENTIONALLY LARGE STRING",
        "textLength": 26,
        "supportingQuestion": "POTENTIONALLY LARGE STRING",
        "supportingQuestionLength": 26
    }
]

SELECT 
    MAX(LENGTH(c.text)) AS textLength,
    MAX(LENGTH(c.supportingQuestion)) AS supportingQuestionLength
FROM c IN items.questions

[
    {
        "textLength": 26,
        "supportingQuestionLength": 26
    }
]