Transform Typescript query of user sessions from @dynatrace-sdk/client-classic-environment-v1 in Notebooks and Dashboards to JSON

[heybeckerj]

SDK query used: https://developer.dynatrace.com/reference/sdks/client-classic-environment-v1/#getusqlresultastable

I have an api query that returns the result as a json payload with a field for columns and values (records) as arrays: 

import { rumUserSessionsClient } from "@dynatrace-sdk/client-classic-environment-v1";


export default async function () {
  
const response = await rumUserSessionsClient.getUsqlResultAsTable({
  query: "SELECT DISTINCT city, country FROM usersession",
  startTimestamp: 0 ,
  endTimestamp: 0,
  pageOffset: 0
});


  return response;

}

Im attempting to get this into JSON. However, the response is not valid JSON but rather arrays of arrays for the records and an array for columnNames:

Here is the documentation for the sdk query result type/promise: https://developer.dynatrace.com/reference/sdks/client-classic-environment-v1/#usqlresultastable 

Example response :

The fields are in the columnNames array and the records are in the values array of arrays:

columnNames	Array<string>	A list of columns in the result table.
values	Array<Array<any>>	A list of data rows. Each array element represents a row in the result table. The size of each data row and the order of the elements correspond to the columnNames content.

Example response :

{
  "extrapolationLevel": 1,
  "columnNames": [
    "city",
    "country"
  ],
  "values": [
    [
      "Burlington",
      "Canada"
    ],
    [
      "Cary",
      "United States"
    ],
    [
      "Johannesburg",
      "South Africa"
    ],
    [
      "Los Angeles",
      "United States"
    ],
    [
      "Manchester",
      "United Kingdom"
    ],
    [
      "Northampton",
      "United Kingdom"
    ],
    [
      "Stoke-on-Trent",
      "United Kingdom"
    ]
  ]
}

How can I transform this result into a usable JSON table that can be used in visualisations?

Even record list does not yeild the intended result: 

[rkdy · Accepted solution]

The following could be a possible workaround for this specific example, unsure if the output you are see is what is expected going forward where you are not mapping the values manually:

import { rumUserSessionsClient } from "@dynatrace-sdk/client-classic-environment-v1";

export default async function () {
  const response = await rumUserSessionsClient.getUsqlResultAsTable({
    query: "SELECT DISTINCT city, country FROM usersession",
    startTimestamp: 0 ,
    endTimestamp: 0,
    pageOffset: 0
  });

  // Extracting the column names and values from the response
  const columnNames = response.columnNames;
  const values = response.values;

  // Creating a table-like structure
  const table = values.map(row => {
    return {
      [columnNames[0]]: row[0],
      [columnNames[1]]: row[1]
    };
  });

  // console.table(table);

  return table;
}

[Fin_Ubels · Accepted solution]

Hey Heybeckerj,

To achieve the desired outcome you will need to reformat the response so that it can be parsed correctly. The best way to see how a response should be formatted for the tiles is by running a regular DQL query and viewing it in the raw format.

For the above query I came up with the following to make it work in table format:

import { rumUserSessionsClient } from "@dynatrace-sdk/client-classic-environment-v1";


export default async function () {
  
  const response = await rumUserSessionsClient.getUsqlResultAsTable({
    query: "SELECT DISTINCT city, country FROM usersession",
    startTimestamp: 1688343399000,
    endTimestamp: 1691021800000,
    pageOffset: 0
  });

  response.values.forEach(myFunction)
  
  function myFunction(item, index, arr) {
    arr[index] = { 'city' : item[0], 'country' : item[1] }
  }


  return response.values;

}

What this is doing is returning just the values but also looping through them all and adding the column name 'city' and 'country' as well as reformatting all of the records to be objects instead of arrays.

Hope this helps!

[heybeckerj · Accepted solution]

Thank you @Fin_Ubels and @rkdy , both of these great suggestions!

I managed to find a way to reconstruct a table form the result by iterating through the 2D array object as well as the columnNames array object instead of having the hardcode any headers. This way it is more dynamic.

Example:

import { rumUserSessionsClient } from "@dynatrace-sdk/client-classic-environment-v1";

function convertTableToJSON(headers,table) {
    // extract header as keys
    let keys = headers;
    // create JSON objects from the remaining data 
    let json = table.map(row => {
        let obj = {};
        // add each element of the row to the object with the corresponding key
        row.forEach((value, index) => {
            obj[keys[index]] = value;
        });
        return obj;
    });
    return json;
}

export default async function () {
  
const response = await rumUserSessionsClient.getUsqlResultAsTable({
  query: "SELECT DISTINCT city, country FROM usersession",
  startTimestamp: 1688184000000 ,
  endTimestamp: 0,
  pageOffset: 0
});
 console.log(response);
  const jsonData = convertTableToJSON(response.columnNames,response.values);
  console.log(jsonData);
  return jsonData;

}

Result:

But, now there are 3 documentated ways to do this. I've learned so much! 👍🏼💪🏼🛠🤘🚀