title: count values in json array per ID on multiple rows w/ OPENJSON
body: <p>I have a table containing Id and JsonData columns (table has 100's of rows)</p>
  <p>JsonData contains an array with various contact ID's for each Id</p>
  <pre><code>{&quot;contacts&quot;:[{&quot;id&quot;:&quot;7d18e3c1-6849-48d4-956b-3f3f578077f4&quot;,&quot;legacy&quot;:null,&quot;type&quot;:&quot;test&quot;},{&quot;id&quot;:&quot;b2af7726-0e7b-492d-b489-c2fe1fe09bd2&quot;,&quot;legacy&quot;:null,&quot;type&quot;:&quot;test&quot;}]}
  </code></pre>
  <p>I need to loop over each Id from Id column and count the number of contacts each Id has in the JsonData array.  I believe I need to use OPENJSON and CROSS APPLY but I have no idea how to do this.</p>
  <p>Expected output for this example (let's say Id value for this row is 1234) would be something like:</p>
  <p>1234: 2 (since this user has 2 contacts in the array)</p>