Node.js+OracleDB如何将外连接查询结果转换为嵌套JSON对象

方案1：SQL层直接生成嵌套JSON（推荐，Oracle 12cR2及以上版本可用） #

从查询层面直接返回目标嵌套结构，完全避免外连接带来的重复行问题，性能比应用层拼接更高。
直接通过Oracle自带的JSON函数构造结构，示例SQL如下：

SELECT
  JSON_OBJECT(
    'userId' VALUE u.user_id,
    'userName' VALUE u.user_name,
    'loginRecords' VALUE JSON_ARRAYAGG(
      JSON_OBJECT(
        'logId' VALUE l.log_id,
        'loginTime' VALUE l.login_time,
        'loginIp' VALUE l.login_ip
      ) RETURNING CLOB
    ) RETURNING CLOB
  ) AS user_json
FROM sys_user u
LEFT JOIN login_log l ON u.user_id = l.user_id
GROUP BY u.user_id, u.user_name

使用注意：

• 若用户无关联登录记录时需要返回空数组而非null，可在JSON_ARRAYAGG中增加NULL ON NULL配置，搭配COALESCE函数处理空值即可
• oracledb读取CLOB类型结果时，可配置fetchAsString: [oracledb.CLOB]直接拿到字符串内容，再通过JSON.parse转成JS对象即可

方案2：Node.js应用层聚合扁平结果集 #

若使用的Oracle版本较低不支持JSON函数，可在拿到外连接返回的扁平结果后，按用户唯一主键分组聚合生成嵌套结构，实现逻辑：

• 用Map结构存储已处理的用户，以用户唯一ID作为key
• 逐行遍历结果集，遇到新用户时初始化基础结构和空的登录记录数组
• 提取当前行的登录记录信息，非关联空值的情况下追加到对应用户的登录记录列表
• 遍历完成后将Map的value转为数组即为目标结构

示例代码：

const oracledb = require('oracledb');
const fs = require('fs/promises');

// 扁平结果转嵌套结构工具方法
function groupUserWithLogs(flatRows) {
  const userMap = new Map();
  for (const row of flatRows) {
    const userId = row.USER_ID;
    // 初始化新用户条目
    if (!userMap.has(userId)) {
      userMap.set(userId, {
        userId: userId,
        userName: row.USER_NAME,
        loginRecords: []
      });
    }
    // 左连接无匹配记录时，登录表关联字段为null，跳过空记录追加
    if (row.LOG_ID !== null) {
      userMap.get(userId).loginRecords.push({
        logId: row.LOG_ID,
        loginTime: row.LOGIN_TIME,
        loginIp: row.LOGIN_IP
      });
    }
  }
  return Array.from(userMap.values());
}

// 业务调用示例
async function exportUserWithLoginLogs() {
  let conn;
  try {
    conn = await oracledb.getConnection({
      // 数据库连接配置
      user: 'your_db_user',
      password: 'your_db_pwd',
      connectString: 'your_db_host/service'
    });
    const queryResult = await conn.execute(`
      SELECT u.user_id, u.user_name, l.log_id, l.login_time, l.login_ip
      FROM sys_user u
      LEFT JOIN login_log l ON u.user_id = l.user_id
      ORDER BY u.user_id
    `, [], {
      outFormat: oracledb.OUT_FORMAT_OBJECT // 直接返回对象格式结果，方便字段取值
    });
    const nestedResult = groupUserWithLogs(queryResult.rows);
    // 结果写入JSON文件
    await fs.writeFile('./user_login_records.json', JSON.stringify(nestedResult, null, 2));
    return nestedResult;
  } finally {
    if (conn) await conn.close();
  }
}

实现注意：

• 分组key必须使用用户表的唯一主键（即user_id），不要使用姓名等可能重复的字段，避免重名用户数据合并错误
• 判断是否为有效登录记录时，建议使用登录表的非空主键（如log_id）或者关联外键字段判断，不要用可能为空的业务字段判断，避免漏记数据

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