How to identify null values using json_extract mysql 8.0

Question

What would be the right way to identify null value when using JSON_EXTRACT,

I want to use a case statement to identify null values in a json field and if the value is null replace it with another value in this case 1.

CREATE TABLE `Log` (
  `change` json DEFAULT NULL
) ENGINE=InnoDB AUTO_INCREMENT=345 DEFAULT CHARSET=utf32 COLLATE=utf32_unicode_ci;


insert into `Log` (`change`) values (JSON_OBJECT('documentNumber', 2));
insert into `Log` (`change`) values (JSON_OBJECT('documentNumber', null));

 select case 
    when isnull(JSON_EXTRACT(`change`, '$.documentNumber')) = 1 then '1'
    else JSON_EXTRACT(`change`, '$.documentNumber') 
    end as 'result'
 from  `Log`;

For this query i am getting result,

result
2
null

but i am expecting,

result
2
1

dbfiddle

Does this answer your question? [Can't detect null value from JSON\_EXTRACT](https://stackoverflow.com/questions/41171636/cant-detect-null-value-from-json-extract) — Schwern, Nov 09 '20 at 08:36

score 3 · Accepted Answer · answered Nov 09 '20 at 08:32

JSON 'null' literal is not NULL value.

See this demo:

CREATE TABLE `Log` (
  `change` json DEFAULT NULL
) ENGINE=InnoDB AUTO_INCREMENT=345 DEFAULT CHARSET=utf32 COLLATE=utf32_unicode_ci;


insert into `Log` (`change`) values (JSON_OBJECT('documentNumber', 2));
-- JSON 'null'
insert into `Log` (`change`) values (JSON_OBJECT('documentNumber', null));  
-- no key, this will produce regular NULL
insert into `Log` (`change`) values (JSON_OBJECT('documentNumberX', null));

 select `change`,
    case 
    when isnull(JSON_EXTRACT(`change`, '$.documentNumber')) = 1 then '1'
    else JSON_EXTRACT(`change`, '$.documentNumber') 
    end as 'result',
    JSON_EXTRACT(`change`, '$.documentNumber') IS NULL test1,
    JSON_EXTRACT(`change`, '$.documentNumber') = CAST('null' AS JSON) test2,
    case when JSON_EXTRACT(`change`, '$.documentNumber') = CAST('null' AS JSON) 
         then '1'
         else JSON_EXTRACT(`change`, '$.documentNumber') 
         end as 'result2'
 from  `Log`;

fiddle

Thanks! I will need time to process this – Bisoux Nov 09 '20 at 08:34 — Bisoux, Nov 09 '20 at 08:34

How to identify null values using json_extract mysql 8.0

1 Answers1