Bug #79813 Boolean values are returned inconsistently with JSON_OBJECT
Submitted: 30 Dec 2015 18:33 Modified: 2 Jan 2016 5:11
Reporter: Bear Limvere Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: JSON Severity:S3 (Non-critical)
Version:5.7.10, 8.0.0 OS:Any
Assigned to: CPU Architecture:Any

[30 Dec 2015 18:33] Bear Limvere
Description:
BOOL values seem to be inconsistent in returns from JSON_OBJECT, sometimes showing as true/false, others as 1/0.

SELECT
  JSON_OBJECT(
    'can_edit', IF(user.is_active = "1", TRUE, FALSE),
    'is_true', TRUE,
    'is_false', FALSE
  ) AS exception_comments
FROM temp.user;

-- returns
{"is_true": true, "can_edit": 1, "is_false": false}
{"is_true": true, "can_edit": 0, "is_false": false}
{"is_true": true, "can_edit": 0, "is_false": false}

How to repeat:
CREATE DATABASE temp;
USE temp;
CREATE TABLE temp.user (
  user_id INT(11) UNSIGNED NOT NULL,
  department_id INT(11) UNSIGNED NOT NULL,
  user_name VARCHAR(32) DEFAULT NULL,
  is_active BOOL NOT NULL DEFAULT 1
 );
INSERT INTO temp.user (user_id, department_id, user_name, is_active)
VALUES
  (1, 1, 'test user 1', TRUE),
  (2, 1, 'test user 2', FALSE),
  (3, 2, 'test user 3', TRUE);
[2 Jan 2016 5:11] Umesh Shastry
Hello Bear Limvere,

Thank you for the report and test case.
Verified as described.

Thanks,
Umesh
[18 Jun 2016 21:35] Omer Barnir
Posted by developer:
 
Reported version value updated to reflect release name change from 5.8 to 8.0
[15 Dec 2017 9:57] Florian Reinhart
Is there any workaround for this? I want to create a JSON object with a boolean property in my query.
[15 Dec 2017 11:28] Florian Reinhart
I found a workaround: Casting TRUE/FALSE to JSON, e.g.

SELECT
  JSON_OBJECT(
    'can_edit', IF(user.is_active = "1", CASR(TRUE AS JSON), CAST(FALSE AS JSON)),
    'is_true', TRUE,
    'is_false', FALSE
  ) AS exception_comments
FROM temp.user;