如何避免BigQuery在Cloud Functions返回的JSON中嵌套日期值？

I've run into this exact issue before—here's what's happening and how to fix it:

Why the Nested Structure Happens #

When you use the BigQuery Node.js client library to fetch results, it returns native JavaScript objects for BigQuery's specialized data types (like DATETIME). Unlike the direct JSON export from BigQuery (which serializes these types to plain strings), the client wraps them in an object with a value property to preserve type information. That's why your DateEST field shows up as { "value": "2018-05-26T23:57:58" } instead of a plain string.

Solution 1: Convert Dates to Strings in Your BigQuery Query #

The simplest fix is to explicitly convert the DATETIME result to a string directly in your SQL query. Use the STRING() function to ensure BigQuery returns a plain string value instead of a typed datetime:

SELECT 
  STRING(DATETIME(salesData.date_utc, "EST")) AS DateEST, 
  salesData.serial_no AS MachineID 
FROM sales.sales_all AS salesData 
WHERE 
  salesData.date_utc > "2018-05-26T05:00:00" 
  AND salesData.date_utc < "2018-05-27T04:59:59" 
ORDER BY salesData.date_utc DESC

With this change, the Cloud Functions code will receive DateEST as a plain string, matching the structure you get from the direct JSON download.

Solution 2: Flatten Date Objects in Your Cloud Functions Code #

If you can't modify the BigQuery query (e.g., it's reused elsewhere), you can transform the results in your Node.js code to extract the value property from date objects.

Basic Field-Specific Fix

If you only need to handle the DateEST field, update your results processing like this:

const options = { 
  query: sqlQuery, 
  useLegacySql: false, // Use standard SQL syntax for queries. 
}; 
bigquery 
  .query(options) 
  .then(results => { 
    const rows = results[0].map(row => ({
      ...row,
      DateEST: row.DateEST.value // Extract the plain string value
    })); 
    response.json(rows); 
  }) 
  .catch(err => { 
    console.error('ERROR:', err); 
    response.send(500); 
  });

Generalized Fix for Multiple Date Fields

If you have multiple date/datetime fields, use a helper function to flatten all nested value objects automatically:

// Helper function to flatten any fields with a "value" property
function flattenTypedFields(row) {
  const flattenedRow = {...row};
  for (const key in flattenedRow) {
    const value = flattenedRow[key];
    // Check if the value is an object with a "value" property
    if (typeof value === 'object' && value !== null && 'value' in value) {
      flattenedRow[key] = value.value;
    }
  }
  return flattenedRow;
}

// Use the helper in your results processing
const options = { 
  query: sqlQuery, 
  useLegacySql: false, 
}; 
bigquery 
  .query(options) 
  .then(results => { 
    const rows = results[0].map(flattenTypedFields); 
    response.json(rows); 
  }) 
  .catch(err => { 
    console.error('ERROR:', err); 
    response.send(500); 
  });

Which Solution to Choose? #

• Solution 1 is cleaner if you control the query—it shifts the work to BigQuery and keeps your Cloud Functions code simple.
• Solution 2 is better if you need to keep the query unchanged, or if you're dealing with dynamic result sets that might include multiple typed fields.

内容的提问来源于stack exchange，提问作者Marc H.