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

What's the benefit of putting a Json file in your column? (I would redesign the table) — Hooman Bahreini
– Hooman Bahreini, Commented Jun 30, 2018 at 12:10
All the "array types" like ['Curveball','Slider'] are invalid JSON so NONE of MySQL's native JSON (like JSON_LENGTH, JSON_SEARCH or JSON_EXTRACT saw a couple of answers with it) functions would work... ["Curveball","Slider"] is valid JSON then the MySQL native JSON functions should work just fine. — Raymond Nijland
– Raymond Nijland, Commented Jun 30, 2018 at 12:21

Gordon Linoff · Accepted Answer · 2018-06-30 12:09:42Z

60

I think you can just use json_length():

where json_length(secondary_pitch) = 0

answered Jun 30, 2018 at 12:09

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

4 Comments

Raymond Nijland Over a year ago

Wont work the data (for example ['Curveball','Slider']) is invalid JSON..

suvtfopw Over a year ago

@RaymondNijland Why? It works perfectly. List with 0 items have 0 length in json_length

Raymond Nijland Over a year ago

"Why? It works perfectly" Why? @jsnceo because ["Curveball", "Slider"] is valid JSON check it on jsonlint.com ['Curveball','Slider'] does not valid here you should not risk using invalid JSON annywhere in anny programming language even if it "works" MySQL should have given a error on ['Curveball','Slider'].. In short the string values and key in a JSON array or Object needs to be double quoted thats the JSON standard

grantwparks Over a year ago

What you're looking at is how mySQL is representing the result of a select. Everything here is valid.

M Khalid Junaid · Accepted Answer · 2018-06-30 12:17:19Z

13

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

answered Jun 30, 2018 at 12:17

M Khalid Junaid

64.6k10 gold badges93 silver badges120 bronze badges

2 Comments

M Khalid Junaid Over a year ago

@RaymondNijland check the demo link, it will work with double quotes not with single quote

Raymond Nijland Over a year ago

The data with double qoutes is indeed valid JSON then the JSON function works. You should mention in the answer that the topicstarters current JSON data is invalid JSON

R. Simac · Accepted Answer · 2019-12-12 16:48:41Z

6

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

answered Dec 12, 2019 at 16:48

R. Simac

80710 silver badges9 bronze badges

1 Comment

Vael Victus Over a year ago

Thank you! Updating to proper nulls: update items set tags = null where tags = JSON_OBJECT();

Ismail Altunören · Accepted Answer · 2018-06-30 12:10:10Z

1

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

edited Jun 30, 2018 at 12:10

answered Jun 30, 2018 at 12:06

Ismail Altunören

1711 silver badge11 bronze badges

4 Comments

Raymond Nijland Over a year ago

a JSON ([]) empty json array element is not equal to NULL or ' ' so this won't work..

Ismail Altunören Over a year ago

So you can use it like this "LIKE '%[]%' "

AbuMariam Over a year ago

I chose this answer because all the other answers required a JSON function which I don't have access to since I have read only permissions on that DB[Code: 1370, SQL State: 42000] execute command denied to user 'jjreadonly'@'%' for routine 'turnto_summary.JSON_SEARCH'

arueckauer Over a year ago

Downvoted because this is not working (tested with MySQL 8.0.15).

Gary Mendonca · Accepted Answer · 2018-06-30 12:11:43Z

0

You can use the below function

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

answered Jun 30, 2018 at 12:11

Gary Mendonca

2,2331 gold badge15 silver badges21 bronze badges

1 Comment

Raymond Nijland Over a year ago

Wont work the data (for example ['Curveball','Slider']) is invalid JSON..

Collectives™ on Stack Overflow

Check if JSON value empty in MySQL?

5 Answers 5

4 Comments

2 Comments

1 Comment

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

4 Comments

2 Comments

1 Comment

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related