Wie gehe ich mit komplexen JSON-Datenstrukturen mit PHP und MySQL um?

CREATE TABLE json_data (
  id INT(11) NOT NULL AUTO_INCREMENT,
  data JSON,
  PRIMARY KEY (id)
);

$json = '{
  "name": "John Doe",
  "age": 30,
  "location": {
    "city": "New York",
    "state": "NY",
    "country": "USA"
  },
  "hobbies": ["reading", "traveling", "cooking"]
}';

$data = json_decode($json, true);

$mysqli = new mysqli("localhost", "username", "password", "database_name");

$query = "INSERT INTO json_data (data) VALUES (?)";
$statement = $mysqli->prepare($query);
$statement->bind_param("s", json_encode($data));
$statement->execute();
$statement->close();

$mysqli->close();

$mysqli = new mysqli("localhost", "username", "password", "database_name");

$query = "SELECT data FROM json_data WHERE id = ? LIMIT 1";
$statement = $mysqli->prepare($query);
$id = 1; // 假设我们要提取id为1的数据
$statement->bind_param("i", $id);
$statement->execute();
$statement->bind_result($jsonData);
$statement->fetch();
$statement->close();

$mysqli->close();

$data = json_decode($jsonData, true);

// 访问JSON数据的字段
echo $data['name']; // 输出：John Doe
echo $data['location']['city']; // 输出：New York

// 循环访问JSON数组的元素
foreach ($data['hobbies'] as $hobby) {
  echo $hobby; // 输出：reading traveling cooking
}

$mysqli = new mysqli("localhost", "username", "password", "database_name");

$query = "SELECT data->'$.age' AS age FROM json_data WHERE data->'$.name' = ?";
$statement = $mysqli->prepare($query);
$name = "John Doe";
$statement->bind_param("s", $name);
$statement->execute();
$statement->bind_result($age);
$statement->fetch();
$statement->close();

$mysqli->close();

echo $age; // 输出：30