topic Re: Transform Typescript query of user sessions from @dynatrace-sdk/client-classic-environment-v1 in Notebooks and Dashboards to J in DQL

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

heybeckerj — Wed, 02 Aug 2023 18:13:12 GMT

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:

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:

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

rkdy — Thu, 03 Aug 2023 00:59:13 GMT

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; }

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

Fin_Ubels — Thu, 03 Aug 2023 02:03:46 GMT

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!

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

heybeckerj — Thu, 03 Aug 2023 17:40:21 GMT

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! 👍🏼💪🏼🛠🤘🚀