基于分类列构建频道JSON API：按分类组织频道数据

嘿，我来帮你搞定这个JSON API的结构设计！

首先，按照你的描述，最终的JSON应该是顶层为数组，数组内包含4个分类对象，每个分类对象里嵌套对应频道的数组。这里给你一个直观的结构示例：

[
  {
    "category": "music",
    "channels": [
      {
        "id": 1,
        "name": "全球流行音乐台",
        "url": "https://example.com/music-1",
        "cat": "music"
      },
      {
        "id": 2,
        "name": "爵士精选频道",
        "url": "https://example.com/music-2",
        "cat": "music"
      }
      // 更多music类频道数据...
    ]
  },
  {
    "category": "anime",
    "channels": [
      {
        "id": 15,
        "name": "经典动漫专区",
        "url": "https://example.com/anime-1",
        "cat": "anime"
      }
      // 更多anime类频道数据...
    ]
  },
  {
    "category": "series",
    "channels": [
      // 对应剧集类频道数据...
    ]
  },
  {
    "category": "movies",
    "channels": [
      // 对应电影类频道数据...
    ]
  }
]

从数据库生成该结构的实现思路 #

接下来你需要在后端处理数据库查询结果，按cat字段分组后组装成上述结构。这里给你两种常见后端场景的实现示例：

1. PHP + MySQL 实现 #

假设用PDO连接数据库：

// 数据库连接（省略基础连接代码）
$pdo = new PDO('mysql:host=localhost;dbname=your_db', 'username', 'password');

// 查询所有频道数据
$stmt = $pdo->query('SELECT * FROM channels ORDER BY cat');
$allChannels = $stmt->fetchAll(PDO::FETCH_ASSOC);

// 定义需要的分类列表
$targetCats = ['music', 'anime', 'series', 'movies'];
$result = [];

// 遍历分类，筛选对应频道并组装结构
foreach ($targetCats as $cat) {
    $filteredChannels = array_filter($allChannels, function($channel) use ($cat) {
        return $channel['cat'] === $cat;
    });
    $result[] = [
        'category' => $cat,
        'channels' => array_values($filteredChannels)
    ];
}

// 输出JSON响应
header('Content-Type: application/json');
echo json_encode($result, JSON_PRETTY_PRINT);

2. Python Flask 实现 #

如果用Flask框架快速搭建API：

from flask import Flask, jsonify
import sqlite3

app = Flask(__name__)

@app.route('/api/channels', methods=['GET'])
def get_categorized_channels():
    # 连接数据库并获取数据
    conn = sqlite3.connect('your_database.db')
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()
    cursor.execute('SELECT * FROM channels ORDER BY cat')
    all_channels = [dict(row) for row in cursor.fetchall()]
    conn.close()

    # 组装目标结构
    target_cats = ['music', 'anime', 'series', 'movies']
    result = []
    for cat in target_cats:
        category_channels = [ch for ch in all_channels if ch['cat'] == cat]
        result.append({
            'category': cat,
            'channels': category_channels
        })
    
    return jsonify(result)

if __name__ == '__main__':
    app.run(debug=True)

优化小技巧 #

如果后续频道数量持续增长，你可以直接用MySQL 8.0+的JSON函数在数据库层面完成分组组装，减少后端处理压力：

SELECT 
    cat AS category,
    JSON_ARRAYAGG(JSON_OBJECT('id', id, 'name', name, 'url', url)) AS channels
FROM channels
GROUP BY cat
ORDER BY FIELD(cat, 'music', 'anime', 'series', 'movies');

这个查询会直接返回每个分类对应的频道数组，后端只需要把结果转成JSON响应即可。

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