Get items count by key name

Question

I have these records in a MySQL DB. On a JSON type column.

Record1: {"images": ["image.jpeg", "image.jpeg"]}
Record2: {"images": ["image.jpeg", "image.jpeg", "image.jpeg"]}
Record3: {"images": ["image.jpeg"]}

How can I get the total count of images by the key name of the JSON property which in my case is 'images'?

I am trying to achieve the same thing as running this below query, but counting the array items from the images key.

SELECT
  `field`,
  count( * ) AS total 
FROM
  `table_name` 
WHERE
  `condition`
GROUP BY
  `something`

which will give you this result:

field      total
------------------
field1       5
field2       2

What I am trying to achieve:

 field      total
 -----------------
 images       6

Table structure

Table data

do you want it by php or query ?

HamzaNig
– HamzaNig

2018-12-14 16:21:01 +00:00
Commented Dec 14, 2018 at 16:21 — HamzaNig
– HamzaNig, Commented Dec 14, 2018 at 16:21
Just mysql query will be ok, thanks

codrin
– codrin

2018-12-14 16:41:59 +00:00
Commented Dec 14, 2018 at 16:41 — codrin
– codrin, Commented Dec 14, 2018 at 16:41

wchiquito · Accepted Answer · 2018-12-21 16:43:10Z

1

Try:

SELECT
  `type`,
  SUM(
    JSON_LENGTH(`media`, '$.images')
  ) `media`
FROM
  `table_name`
GROUP BY
  `type`;

See dbfiddle.

answered Dec 21, 2018 at 16:43

wchiquito

16.6k2 gold badges37 silver badges46 bronze badges

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

1 Comment

codrin Over a year ago

Yes, this is what I was after. Thank you so much!

HamzaNig · Accepted Answer · 2018-12-17 17:30:06Z

0

ok sure You can use this query :

SELECT type,JSON_LENGTH(media) FROM `tabl4` group by type

check the execution from here : Demo

edited Dec 17, 2018 at 17:30

answered Dec 14, 2018 at 16:53

HamzaNig

1,0271 gold badge10 silver badges33 bronze badges

8 Comments

codrin Over a year ago

It is not doing what is supposed to do. I just want to get the count of all images inside the 'media->images' grouped by type.

codrin Over a year ago

No, 8 actually because there is the last record wich is type 1 and has 2 images

HamzaNig Over a year ago

i think that what this query make check the demo to understand

codrin Over a year ago

I don't understand why it's not working on my local machine. Could it be because the media column is JSON type on my local? Also image.jpeg was just an example. The data will have different image names with different extensions and this query will not work with that.

HamzaNig Over a year ago

ah yeh imm i think yes try to change it to varchar or text its better

|

Collectives™ on Stack Overflow

Get items count by key name

Table structure

Table data

2 Answers 2

1 Comment

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Table structure

Table data

2 Answers 2

1 Comment

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related