Azure Stream analytics default field values for missing fields

Question

I have some json values coming in from an IOT datasource to stream analytics. They want to change the json in a later version to have extra fields but older versions will not have these fields. Is there a way I can detect the field is missing and set up a default value for it before it gets to the output? for example they would like to add an e.OSversion which if it did not exist would default to "unknown". The output is a sql database as it happens.

WITH MetricsData AS
(
    SELECT * FROM [MetricsData]
    PARTITION BY LID
    WHERE RecordType='UseList'
)

SELECT
    e.LID as LID
    ,e.EventEnqueuedUtcTime AS SubmitDate
    ,CAST (e.UsedDate as DateTime) AS UsedDate
    ,e.Version as Version
    ,caUsedList.ArrayValue.Module AS Module
    ,caUsedList.ArrayValue.UsageCount AS UsedCount
INTO
    [ModuleUseOutput]
FROM
    Usagedata as e
CROSS APPLY getElements (e.UsedList) as caUsedList

Answer 1

Please use case..when.. operator.

Example:

select j.id, case when j.version is null then 'unknown' else j.version end as version
from jsoninput as j

Output:

Or you could just set the default value in the sql database column directly.