MySQL: Selecting boolean value from JSON field

Question

I'm using MySQL 5.7.19

I'm trying to retrieve a class that looks as follows

class Task
{
    public $title;
    public $done;
}

the title and done properties are saved in a JSON column.

The code that I use to retrieve looks as follows:

    $tasksSql = <<<'EOT'
    SELECT  JSON_UNQUOTE(JSON_EXTRACT(data, '$.title')) AS title,
            JSON_EXTRACT(data, '$.done') AS done
                FROM Tasks WHERE TaskListId = ?;
EOT;
    $tasksStatement = $connection->prepare($tasksSql);
    $tasksStatement->execute([$id]);
    $tasksStatement->setFetchMode(PDO::FETCH_CLASS, "Task");
    $taskList->tasks = $tasksStatement->fetchAll(PDO::FETCH_CLASS, "Task");

It fills out all the fields, but the done property gets set the string values "true" or "false" instead of a boolean value.

what string value do you get back? "1" or "true" ? – nl-x Jul 25 '17 at 13:27 — nl-x, Jul 25 '17 at 13:27

nl-x · Accepted Answer · 2017-07-25T13:57:21.210

5

I think PDO is not recognizing the Boolean value being returned, and it is just assuming its all just string. This has nothing to do with JSON being used. You could change in your query

JSON_EXTRACT(data, '$.done') AS done

into

JSON_EXTRACT(data, '$.done') = true AS done

to change it into a integer 0/1 instead.

Or change your PHP code to parse the returned value into a Boolean. For example by adding a constructor for the tasks object:

class Task {
    public $title; // string
    public $done; // bool
    // ...

    public function __construct() {
        $this->done = $done == 'true';
    }
}

Or even let PDO use an anonymous function for the conversion:

$taskList->tasks = $tasksStatement->fetchAll(PDO::FETCH_FUNC, function($title, $done) { $task = new Task(); $task->title = $title; $task->done = $done == 'true'; return $task;});

edited Jul 25 '17 at 13:57

answered Jul 25 '17 at 13:31

nl-x

11,762
7
33
61

Sadly that doesn't work either. The properties get set to the string values "1" or "0". – Philippe Jul 25 '17 at 13:38
i assume the PDO is not recognizing the Boolean. You can confirm this by quickly justing the query into `select 'foo' as title, true as done;` ... I think you will still get string 'true'... – nl-x Jul 25 '17 at 13:40
1

That is right. Does that mean you can't parse any boolean value using PDO? I've never been a fan of PHP, but this language never stops disappointing. – Philippe Jul 25 '17 at 13:44
@Philippe I've edited the answer to add in some options. – nl-x Jul 25 '17 at 13:57
Thank you, I ended up using the constructor work around. – Philippe Jul 25 '17 at 14:03

MySQL: Selecting boolean value from JSON field

1 Answers1