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

Question

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?

miensol · Accepted Answer · 2018-03-06 13:30:13Z

23

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))
)

answered Mar 6, 2018 at 13:30

miensol

41.9k9 gold badges127 silver badges116 bronze badges

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

3 Comments

tfrancois Over a year ago

Having just come across this post, I respectfully have to comment that this is probably the most correct answer, both programmatically and for readability purposes. Although the answer provided by Aniket may work under most conditions. IMHO.

cramhead Over a year ago

Works with 5.7.34

Павел П Apr 9 at 10:13

SELECT CAST( FALSE AS json ) doesnt work for 11.7.2-MariaDB-log and 10.2.11-MariaDB-log

Aniket Bhansali · Accepted Answer · 2018-03-06 13:29:08Z

5

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 :)

answered Mar 6, 2018 at 13:29

Aniket Bhansali

64013 silver badges34 bronze badges

Comments

EricW · Accepted Answer · 2020-03-17 01:17:48Z

2

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.

answered Mar 17, 2020 at 1:17

EricW

5214 silver badges7 bronze badges

Comments

sultonaka · Accepted Answer · 2020-05-19 12:54:05Z

1

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

answered May 19, 2020 at 12:54

sultonaka

211 bronze badge

Collectives™ on Stack Overflow

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

4 Answers 4

3 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related