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?

Ed Bangga · Accepted Answer · 2019-11-28 02:19:25Z

2

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

edited Nov 28, 2019 at 2:19

answered Nov 28, 2019 at 1:13

Ed Bangga

13k4 gold badges18 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Sean Holmesby Over a year ago

I'm specifically wanting to look within the Url Json property though. i.e imagine there was a third column called fruitType, of which 'apples' could be mentioned.... I don't want that to be included in the Count.

Ed Bangga Over a year ago

@SeanHolmesby, what mysql version you are using?

Sean Holmesby Over a year ago

This is SQL Server 2016. Updated the question. Sorry if the 'MySQLTable' name in my example suggested a MySQL database.

Ed Bangga Over a year ago

@SeanHolmesby, sql server use openjson, see updated answer

Collectives™ on Stack Overflow

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

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related