Mysql: get json array length and ignore null values

Question

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?

Parse your array to the rowset then count non-null values. See dbfiddle.uk/… — Akina
– Akina, Commented Apr 22, 2021 at 11:50
@Scratte I am not sure that my code is applicable to OP's server (its version may be too old) - in this case the solution must be altered. — Akina
– Akina, Commented Apr 22, 2021 at 13:04
@Akina I see. I hadn't thought of that. If they reply that it's not working for them, or if you'd rather post an Answer yourself. I'll delete it. I just came upon your comment and played with it and learned something new myself :) — Scratte
– Scratte, Commented Apr 22, 2021 at 13:07
@Akina seems like your solution works for MySQL 8.0.24+. I have 8.0.16 and get 8 as a result — Andriy Lozynskiy
– Andriy Lozynskiy, Commented Apr 22, 2021 at 21:53

Andriy Lozynskiy · Accepted Answer · 2021-04-22 21:41:23Z

2

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

edited Apr 22, 2021 at 21:41

answered Apr 22, 2021 at 21:35

Andriy Lozynskiy

2,6142 gold badges23 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Scratte · Accepted Answer · 2021-04-22 12:47:28Z

1

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

answered Apr 22, 2021 at 12:47

community wiki

Scratte

2 Comments

Andriy Lozynskiy Over a year ago

I've tested this on MySQL 8.0.16 and the result is 8

Scratte Over a year ago

@AndriyLozynskiy You may want to ping Akina in a comment on the Question and let them know. You should update your Question with the version, you are using as well.

Collectives™ on Stack Overflow

Mysql: get json array length and ignore null values

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related