MySQL多表关联查询：生成嵌套JSON结构结果求助

问题描述 #

现有三张表：

• formoptionslist：包含id、name、active字段
• formoptionslistformoptionsrelates（中间关联表）：包含id、formoptionslist_id、formoptions_id字段
• formoptions：包含id、name、value字段

各表数据如下：

formoptionslist #

id |   name   | active
---------------------
1  | statuses | 1

formoptionslistformoptionsrelates #

id | formoptionslist_id | formoptions_id
------------------------------------
1  | 1                  | 1
2  | 1                  | 2
3  | 1                  | 3

formoptions #

id | name                   | value
------------------------------------
1  | Successfully Purchased | success
2  | Shipping Error         | shipping_error
3  | Failed Payment         | failed_payment

需要通过一次MySQL查询生成如下嵌套JSON结构：

[{
    "name": "statuses", 
    "options": [
        {"name": "Successfully Purchased", "value": "success"},
        {"name": "Shipping Error", "value": "shipping_error"},
        {"name": "Failed Payment", "value": "failed_payment"}
    ]
}]

当前使用PHP+PDO实现时，只能通过错误的查询得到formoptions_id的拼接字符串：
PHP代码：

$this->db->select(
    'GROUP_CONCAT(DISTINCT formoptions_id) as options'.self::from.self::formoptionslistformoptionsrelates
        .self::leftJoin.self::formoptions
        .self::on
        .self::formoptionslistformoptionsrelates.'.formoptions_id'
        .self::equals.self::formoptionslistformoptionsrelates.'.formoptions_id'
    .self::where.'formoptionslist_id = 1'
    .' GROUP BY formoptionslist_id'
);

生成的SQL（存在关联条件错误）：

SELECT GROUP_CONCAT(DISTINCT formoptions_id) as options 
FROM formoptionslistformoptionsrelates 
LEFT JOIN _formoptions ON formoptionslistformoptionsrelates.formoptions_id = formoptionslistformoptionsrelates.formoptions_id 
WHERE formoptionslist_id = 1 
GROUP BY formoptionslist_id

得到的结果：

[{"options":"1,2,3,"}]

解决方案 #

1. 修正关联查询逻辑并使用MySQL JSON函数（MySQL 5.7+） #

首先修正关联条件，然后利用MySQL的JSON_OBJECT和JSON_ARRAYAGG函数直接生成嵌套JSON结构：

方式一：获取单条记录，外层由PHP组装

SELECT 
    fol.name,
    JSON_ARRAYAGG(
        JSON_OBJECT(
            'name', fo.name,
            'value', fo.value
        )
    ) AS options
FROM formoptionslist fol
INNER JOIN formoptionslistformoptionsrelates folfr 
    ON fol.id = folfr.formoptionslist_id
INNER JOIN formoptions fo 
    ON folfr.formoptions_id = fo.id
WHERE fol.id = 1
GROUP BY fol.id, fol.name;

查询结果会返回一行数据，PHP端处理：

$stmt = $this->db->prepare($sql);
$stmt->execute();
$result = $stmt->fetch(PDO::FETCH_ASSOC);
// 组装成目标结构
$finalResult = [
    [
        'name' => $result['name'],
        'options' => json_decode($result['options'], true)
    ]
];
// 输出JSON
echo json_encode($finalResult);

方式二：直接让MySQL生成完整的嵌套JSON数组

SELECT JSON_ARRAYAGG(
    JSON_OBJECT(
        'name', fol.name,
        'options', JSON_ARRAYAGG(
            JSON_OBJECT(
                'name', fo.name,
                'value', fo.value
            )
        )
    )
) AS result
FROM formoptionslist fol
INNER JOIN formoptionslistformoptionsrelates folfr 
    ON fol.id = folfr.formoptionslist_id
INNER JOIN formoptions fo 
    ON folfr.formoptions_id = fo.id
WHERE fol.id = 1
GROUP BY fol.id, fol.name;

直接查询得到的result字段就是目标的JSON字符串，PHP端直接取出解码即可。

2. 低版本MySQL兼容方案（PHP端组装嵌套结构） #

如果使用低于5.7版本的MySQL，可先查询出扁平关联数据，再在PHP端组装嵌套结构：

SQL语句：

SELECT fol.name, fo.name AS option_name, fo.value AS option_value
FROM formoptionslist fol
INNER JOIN formoptionslistformoptionsrelates folfr 
    ON fol.id = folfr.formoptionslist_id
INNER JOIN formoptions fo 
    ON folfr.formoptions_id = fo.id
WHERE fol.id = 1;

PHP端处理：

$stmt = $this->db->prepare($sql);
$stmt->execute();
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

$finalResult = [];
$currentList = null;
foreach ($rows as $row) {
    if (!$currentList || $currentList['name'] !== $row['name']) {
        $currentList = [
            'name' => $row['name'],
            'options' => []
        ];
        $finalResult[] = $currentList;
    }
    $currentList['options'][] = [
        'name' => $row['option_name'],
        'value' => $row['option_value']
    ];
}

echo json_encode($finalResult);

关键说明 #

• 原有查询的关联条件错误：formoptionslistformoptionsrelates.formoptions_id = formoptionslistformoptionsrelates.formoptions_id是自关联，未关联到formoptions表，必须改为formoptionslistformoptionsrelates.formoptions_id = formoptions.id。
• MySQL 5.7及以上版本支持JSON函数，能直接在数据库层生成嵌套结构，减少PHP端处理逻辑。
• 低版本MySQL只能通过查询扁平数据，在PHP端循环组装嵌套结构。

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