使用PDO从两个表中获取JSON

$dsn = 'mysql:host=localhost;dbname=your_database';
$username = 'your_username';
$password = 'your_password';

try {
    $pdo = new PDO($dsn, $username, $password);
} catch (PDOException $e) {
    echo 'Connection failed: ' . $e->getMessage();
}

$sql = "SELECT JSON_OBJECT('table1', JSON_ARRAYAGG(JSON_OBJECT('column1', table1.column1, 'column2', table1.column2))), 
               JSON_OBJECT('table2', JSON_ARRAYAGG(JSON_OBJECT('column3', table2.column3, 'column4', table2.column4)))
        FROM table1
        JOIN table2 ON table1.id = table2.table1_id";

$stmt = $pdo->query($sql);
$result = $stmt->fetch(PDO::FETCH_ASSOC);