Philippe Philippe - 3 years ago 93
JSON Question

MySQL: Selecting boolean value from JSON field

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 Source

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;});
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download