CODEWITHSUNDEEP
mysql
Fr33dan
Fr33dan

Reputation: 4327

Can't detect null value from JSON_EXTRACT

I have a database that has an array of data stored in a JSON column. I need to find all values that have a null value at a particular position in the JSON array. While pulling out the data with JSON_EXTRACT seemed trivial, none of my comparisons to null have worked, all of them claiming the value is null.

Here is the example code that should work as far as I can tell:

SELECT JSON_EXTRACT(`COLUMNS_HEADERS`, '$[1]') , (JSON_EXTRACT(`COLUMNS_HEADERS`, '$[1]') is null)
FROM ate.readings_columns_new;

The first few rows of my results table look like this:

null                    |   0
"INTERNALTEMPERATURE"   |   0
"INPUT_VOLTAGE"         |   0
null                    |   0
null                    |   0
"AH1"                   |   0

I have tried every comparison I can think of, and they all result in a 0:

(JSON_EXTRACT(`COLUMNS_HEADERS`, '$[1]') is null)
(JSON_EXTRACT(`COLUMNS_HEADERS`, '$[1]') <=> null)
ISNULL(JSON_EXTRACT(`COLUMNS_HEADERS`, '$[1]'))
(JSON_EXTRACT(`COLUMNS_HEADERS`, '$[1]') <=> 'null')

Is there some key to comparing null values pulled from a JSON_EXTRACT?

Upvotes: 31

Views: 42866

Answers (5)

Rahul Palve
Rahul Palve

Reputation: 86

I have recently faced this issue, I wanted use JSON_EXTRACT for filtering out records with null as json value.

I tried

WHERE JSON_EXTRACT(ex_table.json_data,'$."new_data"."key"') = 'null'
WHERE JSON_EXTRACT(ex_table.json_data,'$."new_data"."key"') = 'NULL'  
WHERE JSON_EXTRACT(ex_table.json_data,'$."new_data"."key"') IS NULL

but this wont work MySQL cast such fields to JSON TYPE, I don't know if this is optimal or best approach to solve this issue but the way i solved this in WHERE clause is


    SELECT
        id, JSON_EXTRACT(ex_table.json_data,'$."new_data"."key"') as sample_key
    FROM
        ex_table
    WHERE JSON_TYPE(JSON_EXTRACT(ex_table.json_data,'$."new_data"."key"')) = 'NULL'

Upvotes: 3

AdamKent
AdamKent

Reputation: 386

Another trick is MySQL's NULLIF function

SELECT COLUMNS_HEADERS->>"$[1]", NULLIF(COLUMNS_HEADERS->>"$[1]",'null') IS NULL)

(I'm also using ->> which is an alias for JSON_UNQUOTE(JSON_EXTRACT())

That way querying a column containing {"id":1},{"id":2},{"id":null} & {"name":4} for the JSON path $.id will return 1,2,NULL,NULL instead of 1,2,null,NULL

Upvotes: 3

Brenton Thomas
Brenton Thomas

Reputation: 638

A bit of a belated answer but I just hit this problem and couldn't find anything reasonably documented. The solution I ended ended up using was the json_type function as 'abl' pointed out above.

The trick was to compare with the string 'NULL' not null or NULL.

As a test throw the following into a mysql prompt and play around with the values

(if using phpMyAdmin don't forget to check 'show this query here again' and 'retain query box' - the universe is frustrating enough without losing edits..)

set @a='{"a":3,"b":null}';

select if(json_type(json_extract(@a,'$.b')) = 'NULL',1,0);

I ended up with the following.

mysql> set @a='{"a":3,"b":null}';
Query OK, 0 rows affected (0.00 sec)

mysql> select if(json_type(json_extract(@a,'$.b')) = 'NULL',1,0);
+----------------------------------------------------+
| if(json_type(json_extract(@a,'$.b')) = 'NULL',1,0) |
+----------------------------------------------------+
|                                                  1 |
+----------------------------------------------------+
1 row in set (0.00 sec)

mysql> set @a='{"a":3,"b":1}';
Query OK, 0 rows affected (0.00 sec)

mysql> select if(json_type(json_extract(@a,'$.b')) = 'NULL',1,0);
+----------------------------------------------------+
| if(json_type(json_extract(@a,'$.b')) = 'NULL',1,0) |
+----------------------------------------------------+
|                                                  0 |
+----------------------------------------------------+
1 row in set (0.00 sec)

As the bare bones of a stored procedure - which is what I needed it for - using the 'if' statements rather than the if() function.

drop procedure if exists test;
delimiter $$

create procedure test(in x json)
begin

if json_type(json_extract(x,'$.b')) = 'NULL' then
  select 1;
else 
  select 0;
end if;

end$$

delimiter;

mysql> call test('{"a":3,"b":1}');
+---+
| 0 |
+---+
| 0 |
+---+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> call test('{"a":3,"b":null}');
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Upvotes: 5

abl
abl

Reputation: 5958

SELECT 
  JSON_EXTRACT(`COLUMNS_HEADERS`, '$[1]'), 
  (JSON_EXTRACT(`COLUMNS_HEADERS`, '$[1]') = CAST('null' AS JSON))
FROM ate.readings_columns_new;

or

SELECT 
  JSON_EXTRACT(`COLUMNS_HEADERS`, '$[1]'), 
  (JSON_TYPE(JSON_EXTRACT(`COLUMNS_HEADERS`, '$[1]')) = 'NULL')
FROM ate.readings_columns_new;

See the docs for JSON_TYPE.

Upvotes: 57

Fr33dan
Fr33dan

Reputation: 4327

Well I had a suspicion but I found a workaround that confirms that a JSON null value is not the same as a MySQL null value.

I tried various methods to get a similar null value but the only one that works is to extract a null JSON value from an array like the value I'm attempting to check against:

SELECT JSON_EXTRACT(`COLUMNS_HEADERS`, '$[1]') , (JSON_EXTRACT(`COLUMNS_HEADERS`, '$[1]') = JSON_EXTRACT('[null]', '$[0]'))
FROM ate.readings_columns_new;

This seems like bad form, but was the only way I could get a value that evaluated as equal to the null values in my array.

Upvotes: 0

Related Questions