title: T-SQL - search in filtered JSON array
body: <p>SQL Server 2017.</p>
  
  <p>Table <code>OrderData</code> has column <code>DataProperties</code> where JSON is stored. JSON example stored there:</p>
  
  <pre><code>{
   "Input": {
     "OrderId": "abc",
     "Data": [
       {
         "Key": "Files",
         "Value": [
           "test.txt",
           "whatever.jpg"
         ]
       },
       {
         "Key": "Other",
         "Value": [
           "a"
         ]
       }
     ]
   }
  }
  </code></pre>
  
  <p>So, it's an object with Input object, which has Data array that's KVP - full of objects with Key string and Value array of strings.</p>
  
  <p>And my problem - I need to query for rows based on values in Files in example JSON - simple LIKE that matches <code>%text%</code>.</p>
  
  <p>This query works:</p>
  
  <pre><code>SELECT TOP 10 *
  FROM OrderData CROSS APPLY OPENJSON(DataProperties,'$.Input.Data') dat
  WHERE JSON_VALUE(dat.value, '$.Key') = 'Files' and dat.[key] = 0 
  AND JSON_QUERY(dat.value, '$.Value') LIKE '%2%'
  </code></pre>
  
  <p>Problem is that this query is very slow, unsurprisingly. </p>
  
  <p>How to make it faster?</p>
  
  <ol>
  <li>I cannot create computed column with JSON_VALUE, because I need to filter in an array.</li>
  <li>I cannot create computed column with JSON_QUERY on "$.Input.Data" or "$.Input.Data[0].Values" - because I need specific array item in this array with <code>Key == "Files"</code>.</li>
  </ol>
  
  <p>I've searched, but it seems that you cannot create computed column that also filters data, like with this attempt:</p>
  
  <pre><code>ALTER TABLE OrderData
   ADD aaaTest AS (select JSON_QUERY(dat.value, '$.Value')
   OPENJSON(DataProperties,'$.Input.Data') dat
  WHERE JSON_VALUE(dat.value, '$.Key') = 'Files' and dat.[key] = 0 );
  </code></pre>
  
  <p>Error: <code>Subqueries are not allowed in this context. Only scalar expressions are allowed.</code></p>
  
  <hr>
  
  <p>What are my options?</p>
  
  <ol>
  <li>Add Files column with an index and use INSERT/UPDATE triggers that populate this column on inserts/updates? </li>
  <li>Create a view that "computes" this column? Can't add index, will still be slow</li>
  </ol>
  
  <p>So far only option 1. has some merit, but I don't like triggers and maybe there's another option?</p>