多SQL表转NoSQL数据模型：机场数据库JSON嵌套处理问询

根据你的业务场景（机场页面展示多关联数据、其他页面查看航司/航线详情），这里有几个实用的方案来完成JSON数据的嵌套处理，适配你的需求：

既然数据源头是SQL Server，直接在数据库层面生成符合要求的嵌套JSON是最高效的方式，避免导出后二次处理的麻烦。SQL Server的FOR JSON PATH语法支持嵌套结构，你可以通过关联查询+子查询来生成嵌套数组。

假设你的5张表结构大致如下（如果实际字段不同，调整关联条件即可）：

• Airports：机场基础信息（AirportID, Name, CityID, ...）
• Cities：城市时区/夏令时信息（CityID, Name, TimeZone, DSTEnabled, ...）
• Airlines：航空公司信息（AirlineID, Name, ...）
• AirportAirlines：机场-航司关联表（AirportID, AirlineID）
• Routes：航线信息（RouteID, DepartureAirportID, ArrivalAirportID, AirlineID, ...）

下面是生成机场详情嵌套JSON的SQL示例：

SELECT
    a.AirportID,
    a.Name AS AirportName,
    -- 嵌套城市时区与夏令时信息
    c.Name AS CityName,
    c.TimeZone,
    c.DSTEnabled,
    -- 嵌套合作航空公司列表
    (SELECT 
         al.AirlineID, 
         al.Name AS AirlineName
     FROM Airlines al
     INNER JOIN AirportAirlines aa ON al.AirlineID = aa.AirlineID
     WHERE aa.AirportID = a.AirportID
     FOR JSON PATH) AS PartnerAirlines,
    -- 嵌套通航目的地列表（含目的地城市信息）
    (SELECT 
         ar.AirportID AS DestinationAirportID,
         ar.Name AS DestinationAirportName,
         ar_city.Name AS DestinationCityName,
         ar_city.TimeZone AS DestinationTimeZone,
         ar_city.DSTEnabled AS DestinationDSTEnabled
     FROM Routes r
     INNER JOIN Airports ar ON r.ArrivalAirportID = ar.AirportID
     INNER JOIN Cities ar_city ON ar.CityID = ar_city.CityID
     WHERE r.DepartureAirportID = a.AirportID
     FOR JSON PATH) AS Destinations
FROM Airports a
INNER JOIN Cities c ON a.CityID = c.CityID
FOR JSON PATH, INCLUDE_NULL_VALUES;

说明： #

• FOR JSON PATH会自动将子查询结果转为JSON数组，实现嵌套。
• INCLUDE_NULL_VALUES确保即使某些字段为空，也会在JSON中保留键名，避免前端展示出错。
• 针对其他页面（航司/航线详情），可以用类似逻辑生成：比如航司详情嵌套其运营的航线列表，航线详情嵌套出发/到达机场及城市信息。

如果你已经导出了5个独立的JSON文件，用Python的json库可以快速完成嵌套处理，适合非数据库管理员的开发者操作。

步骤与代码示例： #

import json

# 1. 加载所有JSON文件到内存
with open('airports.json', 'r', encoding='utf-8') as f:
    airports = json.load(f)
with open('cities.json', 'r', encoding='utf-8') as f:
    cities = json.load(f)
with open('airlines.json', 'r', encoding='utf-8') as f:
    airlines = json.load(f)
with open('airport_airlines.json', 'r', encoding='utf-8') as f:
    airport_airlines = json.load(f)
with open('routes.json', 'r', encoding='utf-8') as f:
    routes = json.load(f)

# 2. 建立索引，提升关联效率（避免多次遍历列表）
city_index = {city['CityID']: city for city in cities}
airline_index = {airline['AirlineID']: airline for airline in airlines}
# 机场-航司关联索引：key=AirportID，value=对应的航司ID列表
airport_airline_map = {}
for aa in airport_airlines:
    aid = aa['AirportID']
    airport_airline_map.setdefault(aid, []).append(aa['AirlineID'])
# 航线索引：key=出发机场ID，value=到达机场ID列表
route_map = {}
for route in routes:
    dep_id = route['DepartureAirportID']
    route_map.setdefault(dep_id, []).append(route['ArrivalAirportID'])

# 3. 遍历机场数据，组装嵌套结构
processed_airports = []
for airport in airports:
    # 关联城市信息
    city_info = city_index.get(airport['CityID'], {})
    # 关联合作航司
    partner_airlines = [airline_index.get(aid, {}) for aid in airport_airline_map.get(airport['AirportID'], [])]
    # 关联通航目的地（含目的地城市信息）
    destinations = []
    for dest_aid in route_map.get(airport['AirportID'], []):
        dest_airport = next((a for a in airports if a['AirportID'] == dest_aid), {})
        dest_city = city_index.get(dest_airport.get('CityID'), {})
        destinations.append({
            'DestinationAirportID': dest_aid,
            'DestinationAirportName': dest_airport.get('Name'),
            'DestinationCityName': dest_city.get('Name'),
            'DestinationTimeZone': dest_city.get('TimeZone'),
            'DestinationDSTEnabled': dest_city.get('DSTEnabled')
        })
    # 组装最终的机场嵌套数据
    processed_airport = {
        **airport,
        'CityInfo': city_info,
        'PartnerAirlines': partner_airlines,
        'Destinations': destinations
    }
    processed_airports.append(processed_airport)

# 4. 保存处理后的嵌套JSON
with open('processed_airports.json', 'w', encoding='utf-8') as f:
    json.dump(processed_airports, f, indent=2, ensure_ascii=False)

如果你的团队更熟悉JavaScript/Node.js，也可以用类似的逻辑处理：

代码示例： #

const fs = require('fs');
const path = require('path');

// 加载所有JSON文件
const loadJson = (filename) => JSON.parse(fs.readFileSync(path.join(__dirname, filename), 'utf8'));
const airports = loadJson('airports.json');
const cities = loadJson('cities.json');
const airlines = loadJson('airlines.json');
const airportAirlines = loadJson('airport_airlines.json');
const routes = loadJson('routes.json');

// 建立映射索引
const cityMap = new Map(cities.map(c => [c.CityID, c]));
const airlineMap = new Map(airlines.map(a => [a.AirlineID, a]));
const airportAirlineMap = new Map();
airportAirlines.forEach(aa => {
    const list = airportAirlineMap.get(aa.AirportID) || [];
    list.push(aa.AirlineID);
    airportAirlineMap.set(aa.AirportID, list);
});
const routeMap = new Map();
routes.forEach(route => {
    const list = routeMap.get(route.DepartureAirportID) || [];
    list.push(route.ArrivalAirportID);
    routeMap.set(route.DepartureAirportID, list);
});

// 处理嵌套结构
const processedAirports = airports.map(airport => {
    const cityInfo = cityMap.get(airport.CityID) || {};
    const partnerAirlines = (airportAirlineMap.get(airport.AirportID) || []).map(id => airlineMap.get(id) || {});
    const destinations = (routeMap.get(airport.AirportID) || []).map(destId => {
        const destAirport = airports.find(a => a.AirportID === destId) || {};
        const destCity = cityMap.get(destAirport.CityID) || {};
        return {
            DestinationAirportID: destId,
            DestinationAirportName: destAirport.Name,
            DestinationCityName: destCity.Name,
            DestinationTimeZone: destCity.TimeZone,
            DestinationDSTEnabled: destCity.DSTEnabled
        };
    });
    return { ...airport, CityInfo: cityInfo, PartnerAirlines: partnerAirlines, Destinations: destinations };
});

// 保存结果
fs.writeFileSync('processed_airports.json', JSON.stringify(processedAirports, null, 2), 'utf8');

• 字段适配：以上示例基于假设的表结构，实际使用时请根据你的真实字段名（比如可能用AirportCode代替AirportID）调整关联条件和索引键。
• 性能优化：如果数据量很大，优先选择SQL方案（数据库引擎的关联查询效率远高于内存遍历）；用Python/Node.js处理时，一定要建立索引（比如字典/Map），避免多次遍历列表导致性能下降。
• 其他页面适配：对于航司详情页面，可以用类似逻辑生成包含其运营航线的嵌套JSON；航线详情页面则可以关联出发/到达机场及对应城市的时区信息。

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