CODEWITHSUNDEEP
mysqljson
Vicky
Vicky

Reputation: 1

MySQL JSON_SEARCH with Boolean value not working

I am facing an issue with MySQL JSON_SEARCH function, which does not support the boolean value search.

Please refer this SQL: https://rextester.com/DYLPJG17389

Here is database schema:

create table if not exists superheroes (
    name varchar(32),
    attributes JSON
    );

insert into superheroes 
values ('Batman', '{"dead": "false", "orphan": true, "billionaire": "true", "goodboy" : "true"}');

SELECT JSON_SEARCH(attributes, 'all', 'true')
FROM superheroes
WHERE name = 'Batman';

drop table superheroes;

Right now result has: ["$.goodboy", "$.billionaire"]

I need my result should have "$.orphan" I can't replace true with "true" as JSON data are coming from the external source.

Thank you in advance.

Upvotes: 0

Views: 1495

Answers (1)

Arun Palanisamy
Arun Palanisamy

Reputation: 5459

JSON_SEARCH will work only for Strings as the Docmentation says

JSON_SEARCH(json_doc, one_or_all, search_str[, escape_char[, path] ...])

Returns the path to the given string within a JSON document

Also JSON values should be enclosed in double quote for that to a string

JSON value can be a string in double quotes, or a number, or true or false or null, or an object or an array

So in your case one possible solution could be converting the Boolean true to String "true". If you cannot replace manually, you can use JSON_REPLACE to change true to "true". Since you already know the key for which you have to change the value use below query to get your desired result.

SELECT JSON_SEARCH((JSON_REPLACE(attributes,'$.orphan',"true")), 'all', 'true') 
FROM superheroes
WHERE name = 'Batman';

Check your Demo here

OUTPUT:

["$.orphan", "$.goodboy", "$.billionaire"]

Update

If your key orphan contains both true and false, you can just replace only the true value using case and JSON_CONTAINS as below.

SELECT JSON_SEARCH(( CASE 
                       WHEN JSON_CONTAINS(attributes, "true", '$.orphan') = 1 
                     THEN 
                       JSON_REPLACE(attributes, '$.orphan', "true") 
                       ELSE attributes 
                     END ), 'all', 'true') 
FROM   superheroes 
WHERE  NAME = 'Batman';

DEMO

Upvotes: 3

Related Questions