如何在Sequelize.js中无需外键关联获取嵌套JSON数组

Alright, let's figure out how to get that nested JSON structure with Sequelize even without foreign key associations set up between your user and region tables. There are two solid approaches here—using raw SQL queries (efficient for direct database-level aggregation) or fetching data separately and manually nesting it in JavaScript. Let's break both down.

Approach 1: Use Raw SQL Queries with Database JSON Functions #

Since you don't have model associations, raw queries let you leverage your database's built-in JSON aggregation functions to directly generate the nested structure. The exact syntax varies slightly by database, so here are examples for PostgreSQL and MySQL:

For PostgreSQL #

We'll use JSON_AGG and JSON_BUILD_OBJECT to aggregate users under their respective regions:

const { sequelize } = require('./your-sequelize-instance'); // Import your Sequelize instance

async function getNestedRegionsWithUsers() {
  const rawQuery = `
    SELECT 
      r.region_id,
      r.region_name,
      JSON_AGG(
        JSON_BUILD_OBJECT(
          'user_id', u.user_id,
          'user_name', u.user_name
        )
      ) AS users
    FROM region r
    LEFT JOIN "user" u ON r.region_id = u.region_id
    GROUP BY r.region_id, r.region_name;
  `;

  // Execute the query and return the result
  const nestedData = await sequelize.query(rawQuery, { type: sequelize.QueryTypes.SELECT });
  return nestedData;
}

// Test the function
getNestedRegionsWithUsers().then(data => console.log(JSON.stringify(data, null, 2)));

For MySQL #

Use JSON_ARRAYAGG and JSON_OBJECT instead. Note that MySQL returns null for regions with no users, so we'll map the result to replace null with an empty array:

const { sequelize } = require('./your-sequelize-instance');

async function getNestedRegionsWithUsers() {
  const rawQuery = `
    SELECT 
      r.region_id,
      r.region_name,
      JSON_ARRAYAGG(
        JSON_OBJECT(
          'user_id', u.user_id,
          'user_name', u.user_name
        )
      ) AS users
    FROM region r
    LEFT JOIN user u ON r.region_id = u.region_id
    GROUP BY r.region_id, r.region_name;
  `;

  const rawResult = await sequelize.query(rawQuery, { type: sequelize.QueryTypes.SELECT });
  // Clean up null users arrays
  return rawResult.map(item => ({
    ...item,
    users: item.users || []
  }));
}

// Test the function
getNestedRegionsWithUsers().then(data => console.log(JSON.stringify(data, null, 2)));

Approach 2: Fetch Data Separately & Manually Nest in JavaScript #

If you prefer to avoid raw SQL, you can fetch regions and users separately, then use JavaScript to group users under their regions. This approach is database-agnostic:

const Region = require('./models/Region'); // Import your Region model
const User = require('./models/User'); // Import your User model

async function getNestedRegionsWithUsers() {
  // Step 1: Fetch all regions (only the fields we need)
  const regions = await Region.findAll({
    attributes: ['region_id', 'region_name']
  });

  // Step 2: Fetch all users (include region_id to map later)
  const users = await User.findAll({
    attributes: ['user_id', 'user_name', 'region_id']
  });

  // Step 3: Group users by their region_id
  const usersByRegion = users.reduce((grouped, user) => {
    const regionId = user.region_id;
    if (!grouped[regionId]) grouped[regionId] = [];
    grouped[regionId].push({
      user_id: user.user_id,
      user_name: user.user_name
    });
    return grouped;
  }, {});

  // Step 4: Nest users into their corresponding regions
  const nestedResult = regions.map(region => ({
    region_id: region.region_id,
    region_name: region.region_name,
    users: usersByRegion[region.region_id] || [] // Fallback to empty array if no users
  }));

  return nestedResult;
}

// Test the function
getNestedRegionsWithUsers().then(data => console.log(JSON.stringify(data, null, 2)));

Bonus: Set Up Model Associations (Recommended for Future Use) #

While you asked for a solution without associations, setting them up will make this process much cleaner long-term. Here's how to define the one-to-many relationship:

In your Region model: #

Region.hasMany(User, {
  foreignKey: 'region_id',
  sourceKey: 'region_id',
  as: 'users' // Alias for the association
});

In your User model: #

User.belongsTo(Region, {
  foreignKey: 'region_id',
  targetKey: 'region_id',
  as: 'region' // Alias for the association
});

Now you can fetch nested data directly with include: #

const nestedResult = await Region.findAll({
  attributes: ['region_id', 'region_name'],
  include: [{
    model: User,
    as: 'users',
    attributes: ['user_id', 'user_name'] // Only fetch the user fields we need
  }]
});

console.log(JSON.stringify(nestedResult, null, 2));

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