Question 415188 in Mysql Question posted in
The official MySQL documentation can be found here.

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?

enter image description here

Tags:

2

Answers


  1. 0

    A possible solution:

    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

    Explanation:

    • json_table converts your JSON into a relation called jt
    • from which selecting `count(*) yields your result
    • the input is your JSON
    • the second parameter of json_table specifies the columns, here I was only interested of file

    Fiddle: https://www.db-fiddle.com/

    Login or Signup to reply.
  2. 0

    To count images you can use json_length(addphoto).
    Or expand json by JSON_TABLE and count rows.

    See example:
    Test source:

    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}]')
;
    id addphoto
    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}]

    Simple query

    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"'
;

    Output

    id cnt addphoto
    1 2 [{"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}]

    Example with 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
;
    id idx filepath imagename imagetype imagesize cnt
    1 1 vstorage.com/sohoa/han01//123.jpg 123.jpg image/jpeg 239709 2
    1 2 storage.com/sohoa/han01//456.jpg 456.png image/png 1030361 2

    If you whant count only images of selected types

    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

    You can extract only image type from json (imagetype VARCHAR(100) PATH "$.type").

    fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search