CODEWITHSUNDEEP
mysqlmysql-json
Andriy Lozynskiy
Andriy Lozynskiy

Reputation: 2604

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?

Upvotes: 2

Views: 1602

Answers (2)

Andriy Lozynskiy
Andriy Lozynskiy

Reputation: 2604

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

Upvotes: 2

Scratte
Scratte

Reputation: 3166

A comment from Akina says to

Parse your array to the rowset then count non-null values. See https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=dd0a37bb25d526f029a7a82c6a1fe0cc

The SQL in the fiddle is:

WITH cte AS (SELECT '["J00.00", "V01.00", "G00.00", null, null, null, null, null]' jstr)
SELECT COUNT(val)
FROM cte
JOIN JSON_TABLE(cte.jstr,
                '$[*]' COLUMNS (val VARCHAR(255) PATH '$')) jtable

Results in

COUNT(val)
3

Upvotes: 1

Related Questions