CODEWITHSUNDEEP
mysqlmysql-8.0
Bisoux
Bisoux

Reputation: 522

How to identify null values using json_extract mysql 8.0

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

Upvotes: 0

Views: 793

Answers (1)

Akina
Akina

Reputation: 42844

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

Upvotes: 4

Related Questions