Check if JSON value empty in MySQL?
I think you can just use json_length()
:
where json_length(secondary_pitch) = 0
You could use JSON_EXTRACT
to get first value from your column and check for not null
where JSON_EXTRACT(`secondary_pitch`, '$[0]') is not null
Demo
I see this is not answering original question of matching against empty array ([]
) but this has worked for me, matching against empty dictionary ({}
), at mysql 5.7.20-0ubuntu0.16.04.1 - (Ubuntu)
.
I used JSON_OBJECT function but it is very likely the JSON_ARRAY will also work in similar way, creating the 'empty' object when called without arguments.
If I wanted to match against the json column vmkeys
value of {}
(empty dictionary), I used the following query:
SELECT vmkeys FROM `labinstances` WHERE vmkeys=JSON_OBJECT()
To match against the vmkeys
value of NULL, I used this:
SELECT vmkeys FROM `labinstances` WHERE vmkeys is NULL
Hope this helps...
This will check where secondary_pitch is (null) or '' (empty string)
SELECT count(*) from pitchers WHERE secondary_pitch IS NULL OR secondary_pitch = '';
also you can use like this.
SELECT count(*) from pitchers WHERE secondary_pitch LIKE '%[]%'