JSON Array of strings
Vastgemaakt BeantwoordHi,
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
-
Officiële opmerking
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
0Hi,
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
}
}0Hi 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
0Hi 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
0One 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.
0U moet u aanmelden om een opmerking te plaatsen.
Opmerkingen
6 opmerkingen