在 MySQL 中创建图像的列显示数量

可能的解决方案：

select count(*)
from
json_table(
  '[{"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}]',
  '$[*]' COLUMNS( file varchar(64) PATH '$.file' ERROR ON ERROR )
) as jt

解释：

• json_table将您的 JSON 转换为名为jt
• fromwhich selecting `count(*) 产生结果
• 输入是你的 JSON
• 的第二个参数json_table指定列，这里我只感兴趣的是file

小提琴：https://www.db-fiddle.com/

要计算图像数量，您可以使用json_length(addphoto)。
或者通过 JSON_TABLE 扩展 json 并计算行数。

看例子：
测试来源：

create table surveytemplate1 (id int, addphoto varchar(1000));
insert into surveytemplate1 values
 (1,'[{"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}]')
;

简单查询

select t.id,json_length(addphoto) cnt
  ,addphoto
from surveytemplate1 t
WHERE addphoto LIKE '%.gif%' 
OR addphoto LIKE '"Type:"image\/jpeg"' 
OR addphoto LIKE '"Type":"image\/png"' 
OR addphoto LIKE '"Type":"image\/jpg"'
;

输出

例如JSON_TABLE

select t.id,jv.*
  ,json_length(addphoto) cnt
from surveytemplate1 t
cross join json_table(addphoto
  , "$[*]" 
      COLUMNS(
          idx FOR ORDINALITY
        ,filepath VARCHAR(100) PATH "$.file"
        ,imagename VARCHAR(100) PATH "$.name"
        ,imagetype VARCHAR(100) PATH "$.type"
        ,imagesize VARCHAR(100) PATH "$.size"
      ) 
 ) jv
;

如果你只想计算选定类型的图像

select id,count(*) as numberofuploadimage
from(
select t.id,jv.*
  ,json_length(addphoto) cnt
from surveytemplate1 t
cross join json_table(addphoto
  , "$[*]" 
      COLUMNS(
          idx FOR ORDINALITY
        ,filepath VARCHAR(100) PATH "$.file"
        ,imagename VARCHAR(100) PATH "$.name"
        ,imagetype VARCHAR(100) PATH "$.type"
        ,imagesize VARCHAR(100) PATH "$.size"
      ) 
 ) jv
)t
WHERE imagetype in('image\/jpeg','image\/png','image\/jpg')
group by id

您只能从 json ( imagetype VARCHAR(100) PATH "$.type") 中提取图像类型。

小提琴

更新1。

在使用 JSON_TABLE 进行转换之前，检查 JSON 格式的字符串有效性可能会很有用。
我在示例中添加了几行可能出现在您的数据中的数据。

select s.id,counts.numberofuploadimage,s.addphoto
from surveytemplate1 s
left join(
  select id,count(*) as numberofuploadimage
  from(
    select t.id,jv.*
     ,json_length(addphoto) cnt
    from surveytemplate1 t
    cross join json_table(addphoto
     , "$[*]" 
        COLUMNS(
            idx FOR ORDINALITY
          ,filepath VARCHAR(100) PATH "$.file"
          ,imagename VARCHAR(100) PATH "$.name"
          ,imagetype VARCHAR(100) PATH "$.type"
          ,imagesize VARCHAR(100) PATH "$.size"
        ) 
     ) jv
    where json_valid(addphoto)
  ) as t
  WHERE imagetype in('image\/jpeg','image\/png','image\/jpg')
  group by id
) as counts on counts.id=s.id

小提琴

对于 MySQL 5.5，我们可以使用ExtractValueXML 函数来解析字符串。

有数据

1. 将字符串转换为有效的 XML 来自

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

到

<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>    

根据要求

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

2. 使用以下方法将所有图像属性提取为字符串ExtractValue

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

输出

3. 将此字符串转换为 XML

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

从

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

到

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

提取图像属性：

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

为了清楚起见，在 group by 之前我们有

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

小提琴