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, Mar 25 '21 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, Mar 25 '21 at 19:50
@BillKarwin I tried SELECT JSON_LENGTH(Chance)from table_name; and I got null for all rows. — sheel, Mar 25 '21 at 19:55
@BillKarwin I am using 8.0.23 version and I have JSON function. — sheel, Mar 25 '21 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, Mar 25 '21 at 20:00
Can you paste the query I am trying but still getting error? I think I am doing something wrong. @BillKarwin — sheel, Mar 25 '21 at 20:04
@sheel The question isn't whether you have JSON functions, it's whether you used the JSON datatype when creating the table. What does `SHOW CREATE TABLE tbl_name` say that datatype of the `Temp` column is? — Barmar, Mar 25 '21 at 20:14

score 3 · Accepted Answer · answered Mar 25 '21 at 20:17

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

How to get length of an array in MySQL column?

1 Answers1