How to get length of an array in MySQL column?

Question

I have an array in column Temp and I am trying to find the length of an array. I tried JSON_LENGTH but it's not working.

So far I tried this

SELECT JSON_LENGTH(Temp)from tbl_name;

And got this

Id  Temp
1   ['53682', '66890', '53925', '54847']
2   ['53682', '66890', '53925', '54843','54890']

Expected Output:

Id  Temp                                             Count
1   ['53682', '66890', '53925', '54847']             4
2   ['53682', '66890', '53925', '54843','54890']     5

What do you mean by "not working?" Does it give an error? If so, what is the error? Are you using an older version of MySQL that doesn't have JSON functions? — Bill Karwin
– Bill Karwin, Commented Mar 25, 2021 at 19:49
That isn't valid JSON, by the way (use of single-quotes where double-quotes are required in valid JSON). I suspect you aren't using the JSON data type, which would reject the data you show above. — Bill Karwin
– Bill Karwin, Commented Mar 25, 2021 at 19:50
@BillKarwin I tried SELECT JSON_LENGTH(Chance)from table_name; and I got null for all rows. — sheel
– sheel, Commented Mar 25, 2021 at 19:55
@BillKarwin I am using 8.0.23 version and I have JSON function. — sheel
– sheel, Commented Mar 25, 2021 at 19:58
I tested it using MySQL 8.0.23, and it works if you use double-quotes instead of single-quotes. — Bill Karwin
– Bill Karwin, Commented Mar 25, 2021 at 20:00

nbk · Accepted Answer · 2021-03-25 20:17:10Z

you have two possibilities

the old fashion ways
Replace the ' with '

CREATE TABLE table1 (
  `Id` INTEGER,
  `Temp` VARCHAR(44)
);

INSERT INTO table1
  (`Id`, `Temp`)
VALUES
  ('1', "['53682', '66890', '53925', '54847']"),
  ('2', "['53682', '66890', '53925', '54843','54890']");

seLECT `Id`, `Temp`, CHAR_LENGTH (`Temp`) - CHAR_LENGTH (REPLACE(`Temp`,',','')) + 1  as cnt FROM table1

Id | Temp                                         | cnt
-: | :------------------------------------------- | --:
 1 | ['53682', '66890', '53925', '54847']         |   4
 2 | ['53682', '66890', '53925', '54843','54890'] |   5

CREATE TABLE table2 (
  `Id` INTEGER,
  `Temp` VARCHAR(44)
);

INSERT INTO table2
  (`Id`, `Temp`)
VALUES
  ('1', '["53682", "66890", "53925", "54847"]'),
  ('2', '["53682", "66890", "53925", "54843","54890"]');

SELECT `Id`, `Temp`, JSON_LENGTH(`Temp`) AS cnt FROM table2

Id | Temp                                         | cnt
-: | :------------------------------------------- | --:
 1 | ["53682", "66890", "53925", "54847"]         |   4
 2 | ["53682", "66890", "53925", "54843","54890"] |   5

db<>fiddle here

Collectives™ on Stack Overflow

How to get length of an array in MySQL column?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related