[SOLVED] count values in json array per ID on multiple rows w/ OPENJSON

count values in json array per ID on multiple rows w/ OPENJSON

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)

Solution

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