1

I have a table containing Id and JsonData columns (table has 100's of rows)

JsonData contains an array with various contact ID's for each Id

{"contacts":[{"id":"7d18e3c1-6849-48d4-956b-3f3f578077f4","legacy":null,"type":"test"},{"id":"b2af7726-0e7b-492d-b489-c2fe1fe09bd2","legacy":null,"type":"test"}]}

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.

Expected output for this example (let's say Id value for this row is 1234) would be something like:

1234: 2 (since this user has 2 contacts in the array)

im-devops
  • 27
  • 4

1 Answers1

0

Assuming contacts is the array, you can use a CROSS APPLY in concert with OPENJSON

Example

Select A.ID 
      ,B.*
 From  YourTable A
 Cross Apply ( Select Cnt=count(*) From OpenJson(A.JSONData,'$.contacts') ) B

Or you can simply do the following

Select ID 
      ,Cnt = ( Select count(*) From OpenJson(JSONData,'$.contacts') )
 From  YourTable

Results

ID      Cnt
1234    2
John Cappelletti
  • 79,615
  • 7
  • 44
  • 66
  • [Bad Habits to Kick : Using table aliases like (a, b, c) or (t1, t2, t3)](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-table-aliases-like-a-b-c-or-t1-t2-t3) – Charlieface Aug 11 '22 at 21:01
  • @Charlieface I couldn't disagree with that article more. Don't get me wrong, I'm a fan, but this falls into the personal dogma bucket. – John Cappelletti Aug 11 '22 at 21:04