Classic file types are no longer available for use as of January 2021. You can transition your classic files or download a PDF. Learn More

JSON Array of strings

Mostrato in alto Con risposta
Request Demo

Hi,

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

1

Commenti

6 commenti

Data Voti
  • Commento ufficiale
    Tony Scalese
    • Modificato

    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

  • Tony Scalese

    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
  • Martin Jul

    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
  • Martin Jul

    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
  • Tony Scalese

    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
  • Tony Scalese

    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.  

    0

Accedi per aggiungere un commento.