JavaScript Transformation

SQL excels in data extraction, but sometimes results need further refinement. By integrating JavaScript, you can enhance dashboard data manipulation, offering flexibility beyond SQL's scope.

Basics of Transforming with JavaScript

In Dashboards, the data returned from an SQL query can be transformed using a JavaScript function. This function is triggered every time new data arrives from a SQL query. The JavaScript function can manipulate the incoming data in various ways and must return the transformed data.

Merging New Data into Existing Results

Understanding the transformation script:

async (existingDataByQuery, newDataByQuery, lastRun) => {
  return Object.entries(newDataByQuery).reduce((result, [queryIndex, newData]) => {
    return {
      [queryIndex]: result[queryIndex].concat(newData)
  }, existingDataByQuery)

This script seamlessly merges incoming new data with the already available data for each query result.

Detailed Explanation

  1. Initialization: The script begins with two primary inputs - existingDataByQuery, which contains the current set of results, and newDataByQuery, which holds the new results fetched.

  2. Iterate Over New Data: Using Object.entries(newDataByQuery), the script iterates over each data entry in the newly fetched results.

  3. Reduce Function: The main action is performed within a reducer. The reducer's purpose is to build up a result object by combining the new data with the existing one.

  4. Appending New Data: For every specific queryIndex in the new results, the corresponding data from newData is concatenated to the already existing data using the concat method.

  5. Return Merged Data: The reducer finally returns an object with all the merged data sets, which combines both existing and new data for each query index.

By the end of the script, you have a consolidated dataset that includes both previous and new results for each query, ensuring continuity and completeness in your data representation.

Transforming Log Statuses into Log Levels

In another example, raw log data might not always conform to standard log levels. Instead, logs might contain various codes or statuses that need to be categorized into recognizable levels. Using SQL and JavaScript transforms, you can effectively convert these statuses into standard log levels.

SQL Query

To start, use SQL to extract the log statuses (in our case from Heroku App):

SELECT toStartOfInterval(dt, INTERVAL '10 minute') AS time, 
       count(*) as value, 
       JSONExtractString(json, 'heroku', 'status') as series
FROM {{source}}
WHERE time BETWEEN {{start_time}} AND {{end_time}}
AND JSONExtractString(json, 'heroku', 'status') != ''
GROUP BY time, JSONExtractString(json, 'heroku', 'status')

This SQL query fetches log statuses at 10-minute intervals. The result will include time, count of logs, and the status (series).

JavaScript Transform

Post the SQL query; you can use the following JavaScript transformation to categorize these statuses into log levels:

async (existingDataByQuery, newDataByQuery, lastRun) => {
    '439': 'Warn',
    '404': 'Error',
    '200': 'Info',
    'debug_query_optimization': 'Debug',     
    'trace_protocol_data': 'Trace',          
    'fatal_out_of_memory': 'Fatal',          
    // ... add other status mappings as needed

  return Object.entries(newDataByQuery).reduce((result, [queryIndex, newData]) => {
    const logLevelData = => ({
      series: STATUS_CATEGORIES[entry.series] || 'Other'

    return {
      [queryIndex]: result[queryIndex].concat(logLevelData)
  }, existingDataByQuery);

The transformation maps various statuses to their corresponding log levels, and then updates the series with this log level.

By combining SQL and JavaScript transformations, you can ensure that the data visualization accurately reflects the severity and nature of the logged events.

Need help?

Please let us know at
We're happy to help! 🙏