Express中res.json()嵌套映射关联表数据返回空对象的问题

我正在开发一个提供电影信息的Express服务端应用，期望返回包含嵌套principals数组的JSON响应，示例如下：

{"title": "Star Trek: First Contact","year": 1996,"runtime": 111,"genres": ["Action","Adventure","Drama"],"country": "United States","principals": [{"id": "nm0005772","category": "cinematographer","name": "Matthew F. Leonetti","characters": []},...],"boxoffice": 92027888,"poster": "https://m.media-amazon.com/images/M/MV5BYzMzZmE3MTItODYzYy00YWI5LWFkNWMtZTY5NmU2MDkxYWI1XkEyXkFqcGdeQXVyMjUzOTY1NTc@._V1_SX300.jpg","plot": "The Borg travel back in time intent on preventing Earth's first contact with an alien species. Captain Picard and his crew pursue them to ensure that Zefram Cochrane makes his maiden flight reaching warp speed."}

但实际返回的JSON中principals字段为空对象：

{"title": "Star Trek: First Contact","year": 1996,"runtime": 111,"genres": ["Action","Adventure","Drama"],"country": "United States","principals": {},"boxoffice": 92027888,"poster": "https://m.media-amazon.com/images/M/MV5BYzMzZmE3MTItODYzYy00YWI5LWFkNWMtZTY5NmU2MDkxYWI1XkEyXkFqcGdeQXVyMjUzOTY1NTc@._V1_SX300.jpg","plot": "The Borg travel back in time intent on preventing Earth's first contact with an alien species. Captain Picard and his crew pursue them to ensure that Zefram Cochrane makes his maiden flight reaching warp speed."}

电影基础信息存储在basics表，principals人员信息存储在principals表，我的实现代码如下：

router.get('/movies/data/:imdbID', function(req, res, next) {
  const queryMovie = req.db.from('basics')
    .select('primaryTitle', 'year','runtimeMinutes', 'genres', 'country', 'boxoffice', 'poster', 'plot')
    .where('tconst', req.params.imdbID)
  const queryPrincipals = req.db.from('principals')
    .select('nconst', 'category', 'name', 'characters')
    .where('tconst', req.params.imdbID)

  queryMovie.then((movieData) => {
    const movie = movieData.map(data => {
      return {
        title: data.tconst,
        year: data.year,
        runtime: data.runtimeMinutes,
        country: data.country,
        principals: queryPrincipals.then((principals) => {
          principals.map(principal => {
            return {
              id: principal.nconst,
              category: principal.category,
              name: principal.name,
              characters: principal.characters
            }
          })
        }),
        boxoffice: data.genres,
        poster: data.genres,
        plot: data.plot
      }
    })
    res.json(movie)
  })
});

问题分析与修复 #

核心问题 #

1. 异步处理错误：在map函数里直接把Promise赋值给principals字段，res.json执行时Promise尚未完成，JSON序列化Promise会得到空对象{}。
2. 字段映射错误：title错误使用data.tconst（应为data.primaryTitle），boxoffice和poster错误绑定data.genres，导致数据异常。
3. 无返回值问题：queryPrincipals.then内的principals.map没有返回结果，Promise最终解析为undefined。

修正后的代码 #

使用async/await处理异步逻辑，确保数据全部获取后再构建响应：

router.get('/movies/data/:imdbID', async function(req, res, next) {
  try {
    // 并行执行两个查询，提升效率
    const [movieData, principalsData] = await Promise.all([
      req.db.from('basics')
        .select('primaryTitle', 'year','runtimeMinutes', 'genres', 'country', 'boxoffice', 'poster', 'plot')
        .where('tconst', req.params.imdbID),
      req.db.from('principals')
        .select('nconst', 'category', 'name', 'characters')
        .where('tconst', req.params.imdbID)
    ]);

    // 处理电影不存在的情况
    if (movieData.length === 0) {
      return res.status(404).json({ message: '电影不存在' });
    }

    const data = movieData[0];
    // 转换人员数据格式
    const principals = principalsData.map(principal => ({
      id: principal.nconst,
      category: principal.category,
      name: principal.name,
      characters: principal.characters || []
    }));

    // 构建正确的响应数据
    const movie = {
      title: data.primaryTitle,
      year: data.year,
      runtime: data.runtimeMinutes,
      genres: data.genres ? data.genres.split(',') : [], // 字符串转数组（若数据库存储为逗号分隔）
      country: data.country,
      principals: principals,
      boxoffice: data.boxoffice,
      poster: data.poster,
      plot: data.plot
    };

    res.json(movie);
  } catch (error) {
    next(error); // 传递错误给全局错误处理中间件
  }
});

关键改进点 #

• 用Promise.all并行查询，减少请求等待时间。
• async/await简化异步逻辑，避免嵌套回调。
• 修正所有字段映射错误，保证数据对应正确。
• 增加电影不存在的异常处理，返回标准404响应。
• 统一数据格式：genres字符串转数组，characters默认空数组，确保响应格式符合预期。

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