Is there any way to reach raw JSON data through Stream Analytics Job into Azure SQL?

Question

I have some sensors which sending data in JSON formated string to Azure IoT Hub. Until now I processed data via Function Apps over Hub Trigger.

Now I'm trying to find way, how to save "raw" JSON data into Azure SQL database through Azure Stream Analytics Job. Problem is that there is no setting for "raw" data, SA always parse (deserialise) input JSON into data fields. I want just take "raw" JSON and save it into database.

Let me explain this by example ... Raw JSON (catching by IoT Hub) looks like this:

{
"gtwid": "0013A200419F2BAA",
"devid": "0013A200418975CC",
"telemetry": {
  "t1": {
    "id": "a698ab4d2001",
    "avg": 26,
    "max": 26,
    "min": 26
  }
}

When I try use following Query code in SA, I got partially wanted result, but only for "telemetry" tree:

WITH s AS
(
SELECT
        gtwid
      , devid 
      , telemetry.*      
FROM
    [iothub]
)

SELECT gtwid,devid,gtwtime,timestamp,t1 INTO [db] FROM s

It produce this result in db:

RecID  gtwid             devid             t1
1      0013A200419F2BAA  0013A200418975CC  {"id":"a698ab4d2001","avg":26,"max":26,"min":26}

I'd like to get a result like this:

RecID  JsonValue
1      {"gtwid": "0013A200419F2BAA","devid": "0013A200418975CC","telemetry": {"t1": {"id": "a698ab4d2001","avg": 26,"max": 26,"min": 26}}

How I can do it please?

Answer 1

At this time it's not possible to bypass the deserialization.

Instead you can use a JavaScript UDF to regenerate the original payload. It's obviously not the same thing, but it can help depending on why you need it.