Application Insights and Azure Stream Analytics Query export the whole custom dimensions as string

Question

I have setup a continuous export from Application Insights into Blog. With a data stream I'm able to get out the JSON files into SQL DB. So far so good. Also with help from Phani Rahul Sivalenka I'm able to query the individual properties of custom dimensions as described here: Application Insights and Azure Stream Analytics Query a custom JSON property

My custom dimensions looks like this when exporting manually into CSV file: "{""OperatingSystemVersion"":""10.0.18362.418"",""OperatingSystem"":""WINDOWS"",""RuntimePlatform"":""UWP"",""Manufacturer"":""LENOVO"",""ScreenHeight"":""696"",""IsSimulator"":""False"",""ScreenWidth"":""1366"",""Language"":""it"",""IsTablet"":""False"",""Model"":""LENOVO_BI_IDEAPAD4Q_BU_idea_FM_""}"

Additionally to the single columns I like to have the whole custom dimensions as a string in a SQL Table column (varchar(max)). In the "Test results" of my Data Stream Output Query I see the column as formated above - but when really exporting / wrinting into SQL DB all my tests ended having only the value "Array" or "Record" as value in my SQL Table column.

What do I have to do in the Data Stream Query to get the whole custom dimensions value as a string and I'm able to write this into SQL Table as a whole string?

Answer 1

What do I have to do in the Data Stream Query to get the whole custom dimensions value as a string and I'm able to write this into SQL Table as a whole string?

You could use UDF to merge all key-values of single raw into one single json format string.

UDF:

function main(raw) {
    let str = "{";
    for(let key in raw) {
      str = str + "\""+ key+"\":\""+raw[key]+"\",";
    }
    str += "}";
    return str;
}

SQL:

SELECT udf.jsonstring(INPUT1) FROM INPUT1

Output:

Answer 2

The answer brought me on the right track. The above script don't include the values as expected. So I modified the script to get it work as needed:

function main(dimensions) {
    let str = "{";
    for (let i in dimensions)
    {
      let dim = dimensions[i];
      for (let key in dim)
      {
        str = str + "\"" + key+ "\":\""+dim[key]+"\",";
      }
    }
    str += "}";
    return str;
}

Selecting:

WITH pageViews as (
SELECT
    V.ArrayValue.name as pageName
    , *
    , customDimensions       = UDF.flattenCustomDimensions(A.context.custom.dimensions)
    , customDimensionsString = UDF.createCustomDimesionsString(A.context.custom.dimensions)
    FROM [AIInput] as A
    CROSS APPLY GetElements(A.[view]) as V
)

With this I'm getting the custom dimensions string as follow in my SQL table:

{"Language":"tr","IsSimulator":"False","ScreenWidth":"1366","Manufacturer":"Hewlett-Packard","OperatingSystem":"WINDOWS","IsTablet":"False","Model":"K8K51ES#AB8","OperatingSystemVersion":"10.0.17763.805","ScreenHeight":"696","RuntimePlatform":"UWP",}