How can I update json column with a correct data type with PDO?

I have MySQL 5.7 and a query with PDO:

    $data['id'] = 1;
    $data['key'] = 'new_field';
    $data['value'] = 1;
            
    $query= "
        UPDATE `table`
        SET `data` =  JSON_MERGE_PATCH(`data`, JSON_OBJECT(:key, :value))
        WHERE `id` = :id
    ";
    ...
    prepare($query);
    execute($data);

But after it I’m having:

id	data
1	{“old_field”: 2, “new_field”: “1”}

Why does my new value have a string type? Expected table:

id	data
1	{“old_field”: 2, “new_field”: 1}

I have success without using PDO(JSON_OBJECT('new_field', 1)), but I have a string type with using placeholders.

Answer

When you pass an array to PDOStatement::execute() method, all the values will be cast to string. If you want PDO to keep them as integers you must bound them using bindValue() and provide the type hint marking it as an integer.

$stmt = $pdo->prepare($sql);
$stmt->bindValue('key', 'new_field');
$stmt->bindValue('value', 1, PDO::PARAM_INT);
$stmt->execute();

Alternatively, you can cast the value to an integer in SQL, either by adding a zero to it or by using CAST(:value AS SIGNED).