SQL表转JSON格式不符，如何调整为指定嵌套结构？

我用PHP从SQL表生成JSON文件，但输出格式不符合预期，以下是相关代码和输出：

原PHP代码 #

<?php
include 'config.php';

$sql = "SELECT table_id, table_name,GROUP_CONCAT(price) price FROM tables INNER JOIN table_orders ON table_id = res_table_id GROUP BY table_id";
$result = mysqli_query($mysqli,$sql);

while($data1 = $result->fetch_assoc()){
    $data[] = $data1;
}

$encoded_data = json_encode($data,JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE);
file_put_contents('data_json',$encoded_data);

?>

当前输出的JSON #

[
    {
        "table_id": "1",
        "table_name": "Table 1",
        "price": "20.00,15.00,7.90"
    },
    {
        "table_id": "2",
        "table_name": "Table 2",
        "price": "7.90,15.00"
    }
]

期望的JSON格式 #

{
    "table": [
        {
            "table_id": "1",
            "table_name": "Table 1",
            "price": [
                "20.00","15.00","7.90"
            ]
        },
        {
            "table_id": "2",
            "table_name": "Table 2",
            "price": [
                "7.90","15.00"
            ]
        }
    ]
}

修改后的PHP代码 #

<?php
include 'config.php';

$sql = "SELECT table_id, table_name,GROUP_CONCAT(price) price FROM tables INNER JOIN table_orders ON table_id = res_table_id GROUP BY table_id";
$result = mysqli_query($mysqli,$sql);

while($data1 = $result->fetch_assoc()){
    // 将逗号分隔的price字符串转为数组
    $data1['price'] = explode(',', $data1['price']);
    $data[] = $data1;
}

// 将数据包裹在table键名下
$encoded_data = json_encode(['table' => $data], JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE);
file_put_contents('data_json',$encoded_data);

?>

关键改动说明 #

• 处理price字段：使用explode(',', $data1['price'])把GROUP_CONCAT生成的逗号分隔字符串拆分成数组，确保JSON输出时price为数组格式。
• 调整外层结构：把原数组$data放入关联数组的table键下，让最终JSON外层是包含table键的对象结构。

内容的提问来源于stack exchange，提问作者user21185507