Check if JSON value empty in MySQL?

Question

Imagine a table which tracks baseball pitchers like so...

     +------------+--------------------+-------+
     | id | name               | secondary_pitch |
     +------------+--------------------+-------+
     | 13 | Chris Sale         | ['Curveball','Slider'] |
     | 14 | Justin Verlander   | ['Fastball','Changeup'] |
     | 15 | CC Sabathia        | ['Fastball','Curveball'] |
     | 16 | Sonny Grey         |    ['Slider'] |
     | 17 | Aldoris Chapman    |    [] |
     +------------+--------------------+-------+

Notice the secondary_pitch column has a JSON value. So if a pitcher, like Chapman, has no secondary pitch, it will not return null, instead it returns an empty JSON string ('[]').

How then can I get a count of the number of pitchers who have no secondary pitch?

I can't do...

  select count(*) from pitchers where secondary_pitch is null

Answer 1

I think you can just use json_length():

where json_length(secondary_pitch) = 0

Answer 2

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...

Answer 3

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