Create column display number of image in MySQL

I want to create a column numberofimage. In which each cell in the numberofimage column will display the number of images in the addimage column

format of each cell in addimage column like:

[{"file":"vstorage.com\/sohoa\/han01\/\/123.jpg","name":"123.jpg","type":"image\/jpeg","size":239709},{"file":"storage.com\/sohoa\/han01\/\/456.jpg","name":"456.png","type":"image\/png","size":1030361}]

I would count the number of each cell like this, but wrong result:

 SELECT * FROM `surveytemplate1` 
WHERE addphoto LIKE '%.gif%' 
OR addphoto LIKE '"Type:"image\/jpeg"' 
OR addphoto LIKE '"Type":"image\/png"' 
OR addphoto LIKE '"Type":"image\/jpg"'

So in the example above there are 2 pictures because there are 2 words: "Type:"image\/jpeg" and "Type":"image\/png"

So what formula should I use to achieve my desired results? MySQL is v5.5

For MySQL 5.5 we can use ExtractValue function for XML instead parsing strings.

With data

1. Convert string to valid XML From

[{"file":"vstorage.com/sohoa/han01//123.jpg","name":"123.jpg","type":"image/jpeg","size":239709},{"file":"storage.com/sohoa/han01//456.jpg","name":"456.png","type":"image/png","size":1030361}]

To

<a>"file":"vstorage.com/sohoa/han01//123.jpg","name":"123.jpg","type":"image/jpeg","size":239709</a>,<a>"file":"storage.com/sohoa/han01//456.jpg","name":"456.png","type":"image/png","size":1030361</a>    

With request

select t.id
  ,replace(replace(substring(addphoto,2,length(addphoto)-2)
      ,'{','<a>'),'}','</a>') pxml
  ,addphoto
from surveytemplate1 t

2. Extract all image properties as string with ExtractValue

select id ,ExtractValue(replace(replace(substring(addphoto,2,length(addphoto)-2),'{','<a>'),'}','</a>'),'//a[1]') pxml 
from surveytemplate1

Output

3. Convert this string to XML

concat(replace(replace(replace(replace(pxml
      ,'"file":"','<file>'),'","name":"','</file><name>'),'","type":"','</name><type>'
      ),'","size":','</type><size>'
  ),'</size>')

From

"file":"vstorage.com/sohoa/han01//123.jpg","name":"123.jpg","type":"image/jpeg","size":239709

To

<file>vstorage.com/sohoa/han01//123.jpg</file><name>123.jpg</name><type>image/jpeg</type><size>239709</size>

Extract image attributes:

select id,count(imagename) cnt
from(
select id
  ,ExtractValue(pxml1,'//file') filepath
  ,ExtractValue(pxml1,'//name') imagename
  ,ExtractValue(pxml1,'//type') imagetype
  ,ExtractValue(pxml1,'//size') imagesize
from(
select id
  ,concat(replace(replace(replace(replace(pxml
      ,'"file":"','<file>'),'","name":"','</file><name>'),'","type":"','</name><type>'
      ),'","size":','</type><size>'
  ),'</size>') pxml1
from(
select id ,ExtractValue(replace(replace(substring(addphoto,2,length(addphoto)-2)
             ,'{','<a>'),'}','</a>'),'//a[1]') pxml 
from surveytemplate1
union all
select id ,ExtractValue(replace(replace(substring(addphoto,2,length(addphoto)-2)
               ,'{','<a>'),'}','</a>'),'//a[2]') pxml 
from surveytemplate1
union all
select id ,ExtractValue(replace(replace(substring(addphoto,2,length(addphoto)-2)
               ,'{','<a>'),'}','</a>'),'//a[3]') pxml 
from surveytemplate1
)a
)b
)c
WHERE imagetype in('image\/jpeg','image\/png','image\/jpg')
group by id

For clearity, before group by we have

select id
  ,ExtractValue(pxml1,'//file') filepath
  ,ExtractValue(pxml1,'//name') imagename
  ,ExtractValue(pxml1,'//type') imagetype
  ,ExtractValue(pxml1,'//size') imagesize
from(
select id
  ,concat(replace(replace(replace(replace(pxml
      ,'"file":"','<file>'),'","name":"','</file><name>'),'","type":"','</name><type>'
      ),'","size":','</type><size>'
  ),'</size>') pxml1
from(
select id ,ExtractValue(replace(replace(substring(addphoto,2,length(addphoto)-2),'{','<a>'),'}','</a>'),'//a[1]') pxml 
from surveytemplate1
union all
select id ,ExtractValue(replace(replace(substring(addphoto,2,length(addphoto)-2),'{','<a>'),'}','</a>'),'//a[2]') pxml 
from surveytemplate1
union all
select id ,ExtractValue(replace(replace(substring(addphoto,2,length(addphoto)-2),'{','<a>'),'}','</a>'),'//a[3]') pxml 
from surveytemplate1
)a
)b

fiddle