How can I count the number of arrays in json with a MySQL select statement?

For example, in the following case, I want 2 to be returned.

sample
 ----------- ----------- ---------------------------------- 
| id | json                                                |
 ----------- ----------- ---------------------------------- 
| 1  | { items: [{name: a, age: 20}, {name: b, age: 30}] } |
...

I was able to get the contents with json_extract.

but I want count the number.

select
  json_extract(json, '$.items')
from
  sample
where
  id = 1

CodePudding user response：

select
  json_array_length(json_extract(json, '$.items')) as size
from
  sample
where
  id = 1

json_array_length() is use to count size of json array

Try This

CodePudding user response：

You can use JSON_LENGTH function, which is compatible with MySQL 5.7:

SELECT JSON_EXTRACT(json, '$.items'),
       JSON_LENGTH(json, '$.items')
FROM sample 
WHERE id = 1

Check the demo here.

CodePudding user response：

Here is a trick to count, you can use a combination of LENGTH() and REPLACE() functions.

db<>fiddle

SELECT id, json, ROUND((LENGTH(json)- LENGTH(REPLACE(json, 'name', '')))/4,0) AS array_count
FROM (
    SELECT 1 AS id, '{ items: [{name: a, age: 20}, {name: b, age: 30}] }' AS json
) tmp