Azure Data Factory V2 - Parsing JSON HTTP GET Dataset
I am trying to consume the API response below using the HTTP GET connector in Azure Data Factory V2. I am unable to get the Dataset properly parsed and I keep getting an error that the activity failed during deserialization as the data is not in proper JSON object nor array format.
I have tried to leverage JSONPath to isolate specific key-value pairs but I keep getting the same error. I have successfully done this with many other APIs but can't seem to figure this one out. Ultimately, I just want to create a table with a column date, and another one for Revenue, another one for Cost of Revenue, and so on.
Any ideas on the best way to parse this API response using Azure Data Factory V2? Thanks in advance.
{
"AAPL": {
"Revenue": {
"2013-09": "170910",
"2014-09": "182795",
"2015-09": "233715",
"2016-09": "215639",
"2017-09": "229234",
"TTM": "239176"
},
"Cost of revenue": {
"2013-09": "106606",
"2014-09": "112258",
"2015-09": "140089",
"2016-09": "131376",
"2017-09": "141048",
"TTM": "147254"
}
}
}
json
azure azure-data-factory asked Nov 20 '18 at 2:13
CasousadcCasousadc
57313
add a comment |
I am trying to consume the API response below using the HTTP GET connector in Azure Data Factory V2. I am unable to get the Dataset properly parsed and I keep getting an error that the activity failed during deserialization as the data is not in proper JSON object nor array format.
I have tried to leverage JSONPath to isolate specific key-value pairs but I keep getting the same error. I have successfully done this with many other APIs but can't seem to figure this one out. Ultimately, I just want to create a table with a column date, and another one for Revenue, another one for Cost of Revenue, and so on.
Any ideas on the best way to parse this API response using Azure Data Factory V2? Thanks in advance.
{
"AAPL": {
"Revenue": {
"2013-09": "170910",
"2014-09": "182795",
"2015-09": "233715",
"2016-09": "215639",
"2017-09": "229234",
"TTM": "239176"
},
"Cost of revenue": {
"2013-09": "106606",
"2014-09": "112258",
"2015-09": "140089",
"2016-09": "131376",
"2017-09": "141048",
"TTM": "147254"
}
}
}
json
azure azure-data-factory asked Nov 20 '18 at 2:13
CasousadcCasousadc
57313
add a comment |
I am trying to consume the API response below using the HTTP GET connector in Azure Data Factory V2. I am unable to get the Dataset properly parsed and I keep getting an error that the activity failed during deserialization as the data is not in proper JSON object nor array format.
I have tried to leverage JSONPath to isolate specific key-value pairs but I keep getting the same error. I have successfully done this with many other APIs but can't seem to figure this one out. Ultimately, I just want to create a table with a column date, and another one for Revenue, another one for Cost of Revenue, and so on.
Any ideas on the best way to parse this API response using Azure Data Factory V2? Thanks in advance.
{
"AAPL": {
"Revenue": {
"2013-09": "170910",
"2014-09": "182795",
"2015-09": "233715",
"2016-09": "215639",
"2017-09": "229234",
"TTM": "239176"
},
"Cost of revenue": {
"2013-09": "106606",
"2014-09": "112258",
"2015-09": "140089",
"2016-09": "131376",
"2017-09": "141048",
"TTM": "147254"
}
}
}
json
azure azure-data-factory asked Nov 20 '18 at 2:13
CasousadcCasousadc
57313
I am trying to consume the API response below using the HTTP GET connector in Azure Data Factory V2. I am unable to get the Dataset properly parsed and I keep getting an error that the activity failed during deserialization as the data is not in proper JSON object nor array format.
I have tried to leverage JSONPath to isolate specific key-value pairs but I keep getting the same error. I have successfully done this with many other APIs but can't seem to figure this one out. Ultimately, I just want to create a table with a column date, and another one for Revenue, another one for Cost of Revenue, and so on.
Any ideas on the best way to parse this API response using Azure Data Factory V2? Thanks in advance.
{
"AAPL": {
"Revenue": {
"2013-09": "170910",
"2014-09": "182795",
"2015-09": "233715",
"2016-09": "215639",
"2017-09": "229234",
"TTM": "239176"
},
"Cost of revenue": {
"2013-09": "106606",
"2014-09": "112258",
"2015-09": "140089",
"2016-09": "131376",
"2017-09": "141048",
"TTM": "147254"
}
}
}
json
azure azure-data-factory json
azure azure-data-factory asked Nov 20 '18 at 2:13
CasousadcCasousadc
57313
asked Nov 20 '18 at 2:13
CasousadcCasousadc
57313
asked Nov 20 '18 at 2:13
CasousadcCasousadc
57313
asked Nov 20 '18 at 2:13
CasousadcCasousadc
57313
asked Nov 20 '18 at 2:13
CasousadcCasousadc
57313
57313
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
I think your issue is more of a data structure nature. The JSON format used when importing can be used with the jsonPathDefinition parameter to map paths as columns, the thing is, the formatter cannot perform a correlation between an item under Revenue and an item under Cost of revenue (meaning take item index 0 from both locations and create a single row for them).
I think you'll need to due some custom pre-processing on the JSON file before importing.
Hope it helps!
answered Nov 20 '18 at 5:54
Itay PodhajcerItay Podhajcer
1,9891412
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%2f53385256%2fazure-data-factory-v2-parsing-json-http-get-dataset%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
I think your issue is more of a data structure nature. The JSON format used when importing can be used with the jsonPathDefinition parameter to map paths as columns, the thing is, the formatter cannot perform a correlation between an item under Revenue and an item under Cost of revenue (meaning take item index 0 from both locations and create a single row for them).
I think you'll need to due some custom pre-processing on the JSON file before importing.
Hope it helps!
answered Nov 20 '18 at 5:54
Itay PodhajcerItay Podhajcer
1,9891412
add a comment |
I think your issue is more of a data structure nature. The JSON format used when importing can be used with the jsonPathDefinition parameter to map paths as columns, the thing is, the formatter cannot perform a correlation between an item under Revenue and an item under Cost of revenue (meaning take item index 0 from both locations and create a single row for them).
I think you'll need to due some custom pre-processing on the JSON file before importing.
Hope it helps!
answered Nov 20 '18 at 5:54
Itay PodhajcerItay Podhajcer
1,9891412
add a comment |
I think your issue is more of a data structure nature. The JSON format used when importing can be used with the jsonPathDefinition parameter to map paths as columns, the thing is, the formatter cannot perform a correlation between an item under Revenue and an item under Cost of revenue (meaning take item index 0 from both locations and create a single row for them).
I think you'll need to due some custom pre-processing on the JSON file before importing.
Hope it helps!
answered Nov 20 '18 at 5:54
Itay PodhajcerItay Podhajcer
1,9891412
I think your issue is more of a data structure nature. The JSON format used when importing can be used with the jsonPathDefinition parameter to map paths as columns, the thing is, the formatter cannot perform a correlation between an item under Revenue and an item under Cost of revenue (meaning take item index 0 from both locations and create a single row for them).
I think you'll need to due some custom pre-processing on the JSON file before importing.
Hope it helps!
answered Nov 20 '18 at 5:54
Itay PodhajcerItay Podhajcer
1,9891412
answered Nov 20 '18 at 5:54
Itay PodhajcerItay Podhajcer
1,9891412
answered Nov 20 '18 at 5:54
Itay PodhajcerItay Podhajcer
1,9891412
answered Nov 20 '18 at 5:54
Itay PodhajcerItay Podhajcer
1,9891412
1,9891412
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%2f53385256%2fazure-data-factory-v2-parsing-json-http-get-dataset%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
