Stream Analytics GetArrayElements as String

Question

I have a Stream analytics job that gets the data from an external source (I do not have a say on how the data is being formatted). I am trying to import the data into my data lake, storing as a JSON. This works fine, but I also want to get the output in a CSV, this is where I am having trouble.

As the input data has an array as one of the column, when importing in JSON it recognizes it and provides the right data i.e. places them in brackets [A, B, C], but when I use it in CSV I get the column represented as the word "Array". I thought I would convert it to XML, use STUFF and get them in one line, but it does not like using a SELECT statement in a CROSS APPLY.

Has anyone worked with Stream Analytics importing data into CSV, that has array column? If so, how did you manage to import the array values?

Sample data:

[
    {"GID":"10","UID":1,"SID":"5400.0","PG:["75aef","e5f8e"]}, 
    {"GID":"10","UID":2,"SID":"4400.0","PG:["75aef","e5f8e","6d793"]}
]

PG is the column I am trying to extract, so the output CSV should look something like.

GID|UID|SID|PG
10|1|5400.0|75aef,e5f8e
10|2|4400.0|75aef,e5f8e,6d793

This is the query I am using,

SELECT 
    D.GID ,
    D.UID ,
    D.SID ,
    A.ArrayValue
FROM 
    dummy AS D
    CROSS APPLY GetArrayElements(D.PG) AS A

As you could imagine, this gives me results in this format.

GID|UID|SID|PG
10|1|5400.0|75aef
10|1|5400.0|e5f8e
10|2|4400.0|75aef
10|2|4400.0|e5f8e
10|2|4400.0|6d793

Answer 1

As Pete M said, you could try to create a JavaScript user-defined function to convert an array to a string, and then you could call this User-defined function in your query.

JavaScript user-defined function:

function main(inputobj) {
    var outstring = inputobj.toString();
    return outstring;
}

Call UDF in query:

SELECT
    TI.GID,TI.UID,TI.SID,udf.extractdatafromarray(TI.PG)
FROM
   [TEST-SA-DEMO-BLOB-Input] as TI

Result: