JSON Array of strings
Fäst BesvaratHi,
I have a requirement to retrieve data from a source system to Workiva.
Data is exposed through API and in JSON format.
I can just fine retrieve most of the values but not when the values are in an array.
As an example below by using 'Array to CSV' I can retrieve OrgUnitLabel with root =Items and JSONpath .node.OrgUnitLabel
But I cannot retrieve any OrgUnitTags:
How to retrieve those?
Br
Martin
-
Officiell kommentar
Hi Martin,
The problem with your JSON payload is that it contains an OBJECT (square brackets [ ]) nested within an ARRAY (curly brackets { }). The JSON Connector Commands do not accommodate for these kind of JSON payloads. But all is not lost as Handlebars gives you the capability to parse this payload. Here is a screenshot of the Command I configured using the sample you provided (thank you).
And the result:
For ease of copying the code as a starting point, here is the Handlebars text:
{{#each items}}
{{#each node}}
{{DataPointControlName}}:{{#each OrgUnitTags}}{{this}}{{#unless @last}},{{/unless}}{{/each}}
{{/each}}
{{/each}}I hope this helps!
Tony
Hi Martin,
Would you mind posting a sample json payload (redacted) so we can work with the data and try to help you get it figured out?
Tony
0
Hi,
Can you use the following:
{
"items": [
{
"cursor": "",
"node": {
"OrgUnitPrettyId": "SOME_PRETTY_ID",
"OrgUnitLabel": "Organisation unit 1",
"OrgUnitTags": ["tag1", "tag2", "tag3"],
"ReportingYearLabel": "2023",
"ReportingYearId": "9cagdfg31a-efg6-4747-9bjrt-49dc4e34563448",
"PeriodName": "January",
"PeriodMoniker": "M1",
"PeriodStartDate": "2023-01-31T00:00:00.0000000+01:00",
"AspectName": "Aspect name 1",
"AspectId": "e0265592-dsd7-44hf-b433-fd9a5ty3bdc7",
"MeasureName": "Measure name 1",
"MeasureId": "ed454f906-9999-48fc-9c44-420we65654f8",
"MeasureGlobalId": "1hfghdeac5-c297-7gfd-95ad-4b43543534552",
"MeasurePrettyId": "Measure pretty name 1",
"MeasureTags": ["tag1", "tag2", "tag3"],
"MeasurePointId": "8basd90-d884-4asd8-8fads1-1a6da0e0df",
"MeasurePointTags": ["tag1", "tag2", "tag3"],
"RegistrationResponsibleEmail": "test+John.Doe+D310D6@testmail.com",
"RegistrationResponsibleName": "John Doe",
"RegistrationLastUpdateUtc": 1677680020228,
"RegistrationConfirmed": true,
"ControlType": "Number",
"DataPointControlName": "measure_dfd"
"DataDisplayValue": "Summary of first measure",
"DataUnit": "ton",
"DataValueType": "Numeric",
"DataValue": "0",
"RowId": null
}
}
{
"cursor": "",
"node": {
"OrgUnitPrettyId": "SOME_PRETTY_ID",
"OrgUnitLabel": "Organisation unit 1",
"OrgUnitTags": ["tag1", "tag2", "tag3"],
"ReportingYearLabel": "2023",
"ReportingYearId": "9cagdfg31a-efg6-4747-9bjrt-49dc4e34563448",
"PeriodName": "January",
"PeriodMoniker": "M1",
"PeriodStartDate": "2023-01-31T00:00:00.0000000+01:00",
"AspectName": "Aspect name 1",
"AspectId": "e0265592-dsd7-44hf-b433-fd9a5ty3bdc7",
"MeasureName": "Measure name 1",
"MeasureId": "ed454f906-9999-48fc-9c44-420we65654f8",
"MeasureGlobalId": "1hfghdeac5-c297-7gfd-95ad-4b43543534552",
"MeasurePrettyId": "Measure pretty name 1",
"MeasureTags": ["tag1", "tag2", "tag3"],
"MeasurePointId": "8basd90-d884-4asd8-8fads1-1a6da0e0df",
"MeasurePointTags": ["tag1", "tag2", "tag3"],
"RegistrationResponsibleEmail": "test+John.Doe+D310D6@testmail.com",
"RegistrationResponsibleName": "John Doe",
"RegistrationLastUpdateUtc": 1677680020228,
"RegistrationConfirmed": true,
"ControlType": "Number",
"DataPointControlName": "measure_xyz"
"DataDisplayValue": "Summary of first measure",
"DataUnit": "m3",
"DataValueType": "Numeric",
"DataValue": "10",
"RowId": "201b9192-e565-4680-ad64-30fd78dc4b6d"
}
}
],
"totalCount": 1,
"pageInfo": {
"startCursor": "cGFnZTs5",
"endCursor": "cGFnZTs5",
"hasNextPage": true
}
}0
Hi Tony,
Thanks for your help.
The handlebar could extract the data. I found that the output format didn't really fit without making transformations to the output.
I managed to use JSON Array with: .node.OrgUnitTags[0] as JSONPath.
Then I can handle it a bit easier.
For your reference:
Br
Martin Jul
0
Hi Martin,
Glad you found a solution as well! Thanks for sharing back the approach you were able to use. I see from your output that you are only getting 1 row of data. Did you use a different payload than the example you posted above that only had 1 item?
The Handlebars code I shared can be modified as needed. I was trying to give you an example of several of the capabilities of handlars including the use of @last. But by all means, feel free to use either command to get the result you need!
Tony
0
One word of caution, the approach you used will work fine of the tag list is always 3 elements but if it ever has more then you would be missing the additional items.
0Du måste logga in om du vill lämna en kommentar.
Kommentarer
6 kommentarer