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 '%[]%'

Tags:

Mysql

Sql

Null

Json