SQL提取JSON字段中数组中子字符串的实例总数

Question

如何让 SQL 查询进入列的 JSON，并在整个列中找到子字符串的实例数？

我正在使用 SQL Server 2016。

IE

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"}]

所以
SELECT COUNT(*)
FROM FruitPageTable
WHERE [PageData] LIKE '%apples%'
给我 3 ......即其中包含“苹果”的记录数量。

但是如何获得 Url 中“苹果”的数量JSON 中的属性 PageData ，跨越所有记录？即总共提及 6 次。
是否有一些我应该使用的 JSON_QUERY 语法？

Answer 1

我觉得你不需要json_query查看'apples'的号码, string和 mathematical函数将执行此操作。

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

见 dbfiddle

sqlserver , 使用 openjson

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

见 sqlfiddle