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.

Answer 1

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