CODEWITHSUNDEEP
mysqlarraysmysql-json
sheel
sheel

Reputation: 509

How to get length of an array in MySQL column?

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

enter image description here

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

Upvotes: 1

Views: 3961

Answers (1)

nbk
nbk

Reputation: 49375

you have two possibilities

  1. the old fashion ways
  2. 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

Upvotes: 3

Related Questions