Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

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')
  sample
where
  id = 1
                It became an error.  ``` #1305 - FUNCTION json_array_length does not exist ```  MySQL version is 5.7.xx, is there any other way?
– saburouta
                Jun 7, 2022 at 7:48
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.
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
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.