SQL extract total number of instances of a substring in an array in JSON fields

Question

How can I get a SQL Query to go within the JSON of a Column, and find the number of instances of a substring within that entire Column?

I am using SQL Server 2016.

i.e

FruitPageTable
[Id, PageData (nvarchar(max))]
[1 , [{"id":"0","Url":"/i-like-apples/ok-cool"}, {"id":"1","Url":"/where-are-apples/nowhere"} ] 
[2 , [{"id":"0","Url":"/where-are-oranges/everywhere"}]
[3 , [{"id":"0","Url":"/where-are-apples/somewhere"}]
[4 , [{"id":"0","Url":"/apples"},{"id":"1","Url":"/yay-apples"},{"id":"2","Url":"/apples"}]

So

SELECT COUNT(*) FROM FruitPageTable WHERE [PageData] LIKE '%apples%'

gives me 3...i.e the amount of records that have 'apples' in it.

But how to I get the count of 'apples' in the Url property within the JSON in PageData, across all records? i.e 6 mentions in total. Is there some syntax with JSON_QUERY that I should use?

Answer 1

I think you don't need json_query to check number of 'apples', string and mathematical functions will do this.

select
  sum(round((length(val) - length(replace(val, 'apples', '')))/length('apples'))) AS count    
from
  MySQLTable

See dbfiddle

sqlserver, use openjson

select count(1) from
MySQLTable t1
cross apply openjson(t1.val)
with (
   url varchar(200) '$.Url'
) as i
where url like '%apples%'

see sqlfiddle