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
• Status: Verified
• Category: MySQL Server: JSON
• Severity: S3 (Non-critical)
• Version: 5.7.10, 8.0.0, 5.7.43, 8.0.34, 8.1.0
• OS: Any
• 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] MySQL Verification Team

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;

[10 Oct 2023 7:50] Knut Anders Hatlen

Another workaround is this:

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