CODEWITHSUNDEEP
mysqljsonmysql-5.7
miensol
miensol

Reputation: 41638

How to create a JSON object in MySql with a boolean value?

I would like my MySql query to return a JSON object that looks as follows:

{"name": "Piotr", "likesMysql": true}

This seems to be working fine when I do:

SELECT json_object(
    'name', 'Piotr',
    'likesMysql', TRUE
)

However when I try to derive the likesMysql from an if expression I get 0 and 1 instead of false and true e.g.:

SELECT json_object(
    'name', 'Piotr',
    'likesMysql', if(4 MOD 2 = 0, TRUE, FALSE)
)

results in 

{"name": "Piotr", "likesMysql": 1}

How do I use the json_object to construct a JSON object that has true or false as property value?

Upvotes: 6

Views: 10182

Answers (4)

sultonaka
sultonaka

Reputation: 21

SELECT json_object(
   'name', 'Piotr',
   'likesMysql', 4 MOD 2 != 0
)

Upvotes: 1

EricW
EricW

Reputation: 491

Aniket Bhansali's approach could be simplified as:

select json_object(
    'bool_true', (4 mod 2 = 0) is true,
    'bool_false', 0 is true) b;

which returns

{"bool_true": true, "bool_false": false}

Tested on mysql 8.

Upvotes: 2

miensol
miensol

Reputation: 41638

This seems to be a bug in MySql.

You can workaround it though with cast(true as json) e.g.:

SELECT json_object(
    'name', 'Piotr',
    'likesMysql', if(4 MOD 2 = 0, cast(TRUE as json), cast(FALSE as json))
)

Upvotes: 23

Aniket Bhansali
Aniket Bhansali

Reputation: 630

Simply go with following,

SELECT json_object( 'name', 'Piotr', 'likesMysql', if(5 MOD 2 = 0, TRUE, FALSE) is true )

Hope you get desired result with this :)

Upvotes: 5

Related Questions