如何从车辆时序数据构建嵌套对象（Node.js+SQL场景）

To convert your raw vehicle position data into the aggregated structure you need, we can use a JavaScript approach that groups entries by carID, collects static fields once, and builds the nested timestamp/speed objects incrementally. Here's a step-by-step implementation:

Basic Implementation (For Your Example) #

First, let's fix the syntax in your raw data (you used = instead of : for Speed), then implement the aggregation:

const rawposdata = [
  { carID: 1234, contact: 'john doe', TimeStamp: '10:00', Speed: 2.3 },
  { carID: 1234, contact: 'john doe', TimeStamp: '11:00', Speed: 2.4 },
  { carID: 1234, contact: 'john doe', TimeStamp: '12:00', Speed: 2.5 },
  { carID: 9876, contact: 'bob wills', TimeStamp: '10:00', Speed: 1.1 },
  { carID: 9876, contact: 'bob wills', TimeStamp: '11:05', Speed: 1.1 },
  { carID: 9876, contact: 'bob wills', TimeStamp: '12:00', Speed: 3.2 }
];

function aggregateCarData(rawData) {
  // Use a map to track each car's aggregated data
  const carMap = {};

  rawData.forEach(item => {
    const { carID, contact, TimeStamp, Speed } = item;

    // Initialize entry if carID isn't in the map yet
    if (!carMap[carID]) {
      carMap[carID] = {
        carID,
        contact,
        timestamp: {},
        speed: {},
        _count: 0 // Internal counter to generate ts0/s0 keys
      };
    }

    const currentCar = carMap[carID];
    const index = currentCar._count;

    // Add the current timestamp and speed to the nested objects
    currentCar.timestamp[`ts${index}`] = TimeStamp;
    currentCar.speed[`s${index}`] = Speed;

    // Increment counter for the next entry
    currentCar._count++;
  });

  // Convert map values to an array and remove the internal counter
  return Object.values(carMap).map(({ _count, ...cleanedCar }) => cleanedCar);
}

// Generate the desired structure
const mypositiondata = aggregateCarData(rawposdata);
console.log(mypositiondata);

Scalable Version (For Additional Static/Dynamic Fields) #

If you have more static fields (like carModel, color) or dynamic fields (like Altitude, FuelLevel), this flexible version lets you specify which fields are dynamic and handles them automatically:

function aggregateCarData(rawData, dynamicFieldNames) {
  const carMap = {};

  rawData.forEach(item => {
    const { carID, contact, ...allOtherFields } = item;
    const staticFields = {};
    const dynamicValues = {};

    // Separate static vs dynamic fields
    Object.entries(allOtherFields).forEach(([key, value]) => {
      if (dynamicFieldNames.includes(key)) {
        dynamicValues[key] = value;
      } else {
        staticFields[key] = value;
      }
    });

    // Initialize car entry if not exists
    if (!carMap[carID]) {
      carMap[carID] = {
        carID,
        contact,
        ...staticFields,
        _count: 0,
        // Create empty objects for each dynamic field (lowercase keys)
        ...dynamicFieldNames.reduce((acc, field) => {
          acc[field.toLowerCase()] = {};
          return acc;
        }, {})
      };
    }

    const currentCar = carMap[carID];
    const index = currentCar._count;

    // Populate each dynamic field's nested object
    Object.entries(dynamicValues).forEach(([field, value]) => {
      // Generate key prefix (ts for TimeStamp, first letter lowercase for others)
      const keyPrefix = field === 'TimeStamp' ? 'ts' : field.charAt(0).toLowerCase();
      currentCar[field.toLowerCase()][`${keyPrefix}${index}`] = value;
    });

    currentCar._count++;
  });

  // Clean up and return final array
  return Object.values(carMap).map(({ _count, ...cleanedCar }) => cleanedCar);
}

// Usage with your dynamic fields (add more as needed)
const mypositiondata = aggregateCarData(rawposdata, ['TimeStamp', 'Speed']);

How It Works #

1. Grouping by CarID: We use an object (carMap) to keep track of each car's data as we iterate through the raw array.
2. Static Fields: Fields like carID and contact are set once when the car is first encountered.
3. Dynamic Fields: For each time-series entry, we add the value to the corresponding nested object using an incrementing index (e.g., ts0, s0).
4. Final Conversion: Convert the map values to an array and remove the internal counter to get the clean desired structure.

This approach efficiently handles 50-150 cars and scales well with additional fields, as you mentioned.

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