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
– nl-x

2017-07-25 13:27:42 +00:00
Commented Jul 25, 2017 at 13:27 — nl-x
– nl-x, Commented Jul 25, 2017 at 13:27
"true" or "false"

Philippe
– Philippe

2017-07-25 13:29:07 +00:00
Commented Jul 25, 2017 at 13:29 — Philippe
– Philippe, Commented Jul 25, 2017 at 13:29

nl-x · Accepted Answer · 2017-07-25 13:57:21Z

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, 2017 at 13:57

answered Jul 25, 2017 at 13:31

nl-x

11.9k7 gold badges36 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Philippe Over a year ago

Sadly that doesn't work either. The properties get set to the string values "1" or "0".

nl-x Over a year ago

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'...

Philippe Over a year ago

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.

nl-x Over a year ago

@Philippe I've edited the answer to add in some options.

Philippe Over a year ago

Thank you, I ended up using the constructor work around.

Collectives™ on Stack Overflow

MySQL: Selecting boolean value from JSON field

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related