Azure Stream analytics default field values for missing fields
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
azure-stream-analytics
asked Jan 18 at 14:16
user2903089user2903089
18510
add a comment |
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
azure-stream-analytics
asked Jan 18 at 14:16
user2903089user2903089
18510
add a comment |
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
azure-stream-analytics
asked Jan 18 at 14:16
user2903089user2903089
18510
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
azure-stream-analytics
azure-stream-analytics
asked Jan 18 at 14:16
user2903089user2903089
18510
asked Jan 18 at 14:16
user2903089user2903089
18510
asked Jan 18 at 14:16
user2903089user2903089
18510
asked Jan 18 at 14:16
user2903089user2903089
18510
asked Jan 18 at 14:16
user2903089user2903089
18510
18510
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
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.
answered yesterday
Jay GongJay Gong
8,4121512
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54255833%2fazure-stream-analytics-default-field-values-for-missing-fields%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
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.
answered yesterday
Jay GongJay Gong
8,4121512
add a comment |
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.
answered yesterday
Jay GongJay Gong
8,4121512
add a comment |
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.
answered yesterday
Jay GongJay Gong
8,4121512
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.
answered yesterday
Jay GongJay Gong
8,4121512
answered yesterday
Jay GongJay Gong
8,4121512
answered yesterday
Jay GongJay Gong
8,4121512
answered yesterday
Jay GongJay Gong
8,4121512
8,4121512
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54255833%2fazure-stream-analytics-default-field-values-for-missing-fields%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
