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 — Schwern
– Schwern, Commented Nov 9, 2020 at 8:36

Akina · Accepted Answer · 2020-11-09 08:32:21Z

4

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

answered Nov 9, 2020 at 8:32

Akina

43.2k6 gold badges17 silver badges30 bronze badges

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

1 Comment

Bisoux Over a year ago

Thanks! I will need time to process this

Collectives™ on Stack Overflow

How to identify null values using json_extract mysql 8.0

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related