MySQL JSON_SEARCH with Boolean value not working

Question

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.

please read meta.stackoverflow.com/questions/333952/… and edit your question — nbk
– nbk, Commented Nov 27, 2019 at 19:04
Please add the relevant details from the link you have provided in case the link is no longer available in future — Josh
– Josh, Commented Nov 27, 2019 at 19:26
just quote true after orphan as "orphan": "true" within the json column. Refer this — Barbaros Özhan
– Barbaros Özhan, Commented Nov 27, 2019 at 22:08
@Vicky, You mean to say you can't replace true with "true" ? Because you post says you can replace. If that is the case you have already got the answer from Barbaros Özhan. — Arun Palanisamy
– Arun Palanisamy, Commented Nov 28, 2019 at 6:08
@ArunPalanisamy Sorry for typo mistake, I can't replace as data coming from an external source. Also, this is just a small node of actual JSON data. — Vicky
– Vicky, Commented Nov 29, 2019 at 17:26

Community · Accepted Answer · 2020-06-20 09:12:55Z

3

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

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Nov 28, 2019 at 7:13

Arun Palanisamy

5,4898 gold badges34 silver badges57 bronze badges

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

2 Comments

Vicky Over a year ago

JSON_REPLACE will replace all occurrences '$.orphan' to '"true"', in case of the array of value, some '$.orphan' with false will also replace will '"true"'. Is that correct?

Arun Palanisamy Over a year ago

@Vicky, Correct. But we can avoid that using case and JSON_CONTAINS. I have updated my answer.

Collectives™ on Stack Overflow

MySQL JSON_SEARCH with Boolean value not working

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related