CODEWITHSUNDEEP
mysqlsqljsonnull
AbuMariam
AbuMariam

Reputation: 3698

Check if JSON value empty in MySQL?

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

Upvotes: 27

Views: 57273

Answers (5)

R. Simac
R. Simac

Reputation: 797

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

Upvotes: 6

M Khalid Junaid
M Khalid Junaid

Reputation: 64496

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

Upvotes: 13

Gary Mendonca
Gary Mendonca

Reputation: 2183

You can use the below function

SELECT * FROM TABLE_NAME WHERE 
JSON_SEARCH(secondary_pitchers, 'all', '') 
    IS NOT NULL;

Upvotes: 0

Ismail Altunören
Ismail Altunören

Reputation: 171

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

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271171

I think you can just use json_length():

where json_length(secondary_pitch) = 0

Upvotes: 60

Related Questions