how to count json in mysql5.5?

Question

I stored a field of records as JSON "[1,2,3,14,4]"

In MySQL 5.7, I used JSON_LENGTH to count it.

But How can I count in MySQL 5.5.

This is my example query in MySQL 5.7.

SELECT JSON_LENGTH(view_cnt) AS view_cnt FROM `tbl` WHERE `published`=1

Shadow · Accepted Answer · 2017-05-05 05:21:24Z

1

Since mysql v5.5 does not have any json support, you need to get creative using string functions.

If your json is simple and does not have values that themselves contain comma, then just count then number of commas within your string and add 1 to it:

select char_length(json_field)-char_length(replace(json_field,',',''))+1 from table

If your json values contain commas, then you would have to write a json parser in mysql to get the length.

answered May 5, 2017 at 5:21

Shadow

34.5k10 gold badges67 silver badges76 bronze badges

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

2 Comments

Thu Ra Over a year ago

LENGTH(view_cnt)- LENGTH(REPLACE(view_cnt, ',', ''))+1 AS view_cnt2

Shadow Over a year ago

Length returns length in bytes, not in number of characters.

Collectives™ on Stack Overflow

how to count json in mysql5.5?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related