title: Query JSON in CosmosDB to find strings that breach varchar limit
body: <p>A few days ago I encountered a problem that was totally my bad, and now I need some help fixing some data. </p>
  
  <p>I have some JSON documents stored in CosmosDB, and I have a function app to translate these JSON documents into an Azure SQL table. The error I encountered was that I put one of the columns as varchar 200, and the error was caused by strings in this JSON that exceed that character limit. A bit of an oversight, but I've put validation in place, and made it a much more robust process but I'm still left with a load of data that is unable to fulfil its purpose and I seem to be defeated by the querying limits of cosmosDB.</p>
  
  <p>So my question is. How can I query cosmosDB to find the a document with strings that exceed 200 chars? </p>
  
  <p>Here is an example of the JSON i'm trying to query stored in cosmosDB</p>
  
  <pre><code>{
      "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"
            }
          ],
        }
      ]
  }
  </code></pre>
  
  <p>I've tried searching around to see if there are any operators I can use for cosmos and I'm not having much luck at all. </p>
  
  <p>With queries like this I can search for specific offenders, but not the general offenders I'm after.</p>
  
  <p>SELECT * FROM c
  WHERE ARRAY_CONTAINS(c.questions, { text: "My question text"}, true)</p>
  
  <p>Is there a way to search for c.questions text > 200? </p>
  
  <p>I also tried downloading each document locally using a migration tool supplied by Microsoft so I could perhaps try to iterate through them. </p>
  
  <pre><code>        // Set some common query options.
          FeedOptions queryOptions = new FeedOptions { MaxItemCount = -1 };
  
          IQueryable&lt;Forms&gt; formQuery = client.CreateDocumentQuery&lt;Forms&gt;(
              UriFactory.CreateDocumentCollectionUri(DatabaseId, CollectionId), queryOptions)
              .Where(f =&gt; 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&lt;Forms&gt; formQueryInSql = client.CreateDocumentQuery&lt;Forms&gt;(
              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}");
          }
  </code></pre>
  
  <p>This code works wonderfully with smaller datasets, but not at all well with the large datasets I'm using. It hangs and crashes</p>
  
  <p>Is there any simpler query I can use on the cosmosDB UI to get what I need? Or do I need to think about getting this through some clever code?</p>