title: Find length of Array within JSON column
body: <p>I am trying to give a score on my query results. Each <code>'categories'</code> array might contain many category integers, or it might be <code>null</code>. If it's null then it should be considered as found.</p>
  
  <p>In the query below I'm searching for category <code>3</code> which is found. However <strong>I don't know how I can check if the Array categories is</strong> <code>NULL</code> so I can return 1 as well which will solve my problem.</p>
  
  <p>I have tried:</p>
  
  <pre><code>WHERE Value IN(3) OR Value IS NULL)
  
  WHERE Value IN(3) OR Value.length =0)
  
  WHERE Value IN(3) OR Value = '') 
  </code></pre>
  
  <p>etc....</p>
  
  <p>For example</p>
  
  <ol>
  <li><code>{"categories":[1,2,3],"distance":[130,300],"companysize":[2,4],"budget":[5000,1000000]}</code></li>
  <li><code>{"categories":[],"distance":[60,200],"companysize":[1,2],"budget":[250,100]}</code></li>
  </ol>
  
  <p>SQL</p>
  
  <pre><code>     SELECT * FROM (SELECT 
               (CASE WHEN EXISTS (Select * FROM OPENJSON(Preference,'$.categories') 
                WHERE Value IN(3) OR Value IS NULL)  -- &lt; problem is here
                THEN 1 ELSE 0 END)
                matchscore, p.*
                FROM FinNotificationsPreferences p) x
  </code></pre>