Search code examples
mysqlmysql-json

Mysql: get json array length and ignore null values

I have this query

select json_length(data->"$.row.*.code") as count from hospitalization_history where id = 238

The result in count is 8, because data->"$.row.*.code" returns ["J00.00", "V01.00", "G00.00", null, null, null, null, null];

How can I a get number of not null values in json array?

Solution

  • Finally, found this solution for MySQL 8+:

    SELECT JSON_LENGTH(
    JSON_SEARCH('["J00.00", "V01.00", "G00.00", null, null, null]','all','%')
) AS count;

    Try it here