CODEWITHSUNDEEP
mysql-json
Kavita Shinde
Kavita Shinde

Reputation: 11

Select clause on json key having value 0 returns the rows for both "" and 0 with MySQL column of type JSON

table definition.

create table testJsonValue(
    id int primary key auto_increment not null,
    attributes json 
);

Table data preparation script:

insert into testJsonValue values (1,'{"major":[{"score": "1"}] }');
insert into testJsonValue values (2,'{"major":[{"score": "0"}] }');
insert into testJsonValue values (3,'{"major":[{"score": ""}] }');

Data:

id    attributes
1     {"major": [{"score": "1"}]}
2     {"major": [{"score": "0"}]}
3     {"major": [{"score": ""}]}

For following select query:

select testJsonValue.* from testJsonValue, 
JSON_TABLE(testJsonValue.attributes, '$.major[*]' 
COLUMNS (score DOUBLE PATH '$.score')) 
major WHERE major.score = 0;

I assume above query should only return rows where major.score = 0. But the query returns the rows for both major.score having empty value i.e. "" & major.score having value 0. I assume it should have returned rows only having value major.score = 0.

To ensure my query only returns records where major is explicitly 0 and not empty strings, I tried to modify the condition to specifically check for the value I want.
but it didn't work.

Here’s the query i have tried:

select testJsonValue.* from testJsonValue, 
JSON_TABLE(testJsonValue.attributes, '$.major[*]'   
COLUMNS (score DOUBLE PATH '$.score')) major WHERE major.score = 0  
and major.score = 0 IS NOT NULL and  major.score = 0 != '';

Upvotes: 0

Views: 28

Answers (0)

Related Questions