JSON connector

With the JSON connector, you can use commands to transform, fetch, and convert JavaScript Object Notation (JSON) data as part of a chain. For example, with this connector, you can:

• Convert a JSON array or object to CSV
• Convert CSV data to a JSON array
• Parse text as JSON with a validated schema

Note: Several connectors' commands generate a JSON output. To extract a JSON value as a string, list, or JSON for use with a command later in a chain, use a variable transformation.

Prerequisites

To use the JSON connector, we recommend a basic understanding of JSON syntax.

JSON stores and transmits structured data—from a single number to multiple strings, arrays, and objects—as plain text. A JSON string contains either:

• An array—or list—of values
• An object, which contains an associative array of name/value pairs

To support complex data structures, you can nest arrays and objects within your JSON.

Arrays

In JSON, arrays are surrounded by square brackets [] and contain a comma-separated list of values:

• Numbers
• Text strings, surrounded by double-quotes ""
• Booleans, true or false
• Arrays, surrounded by square brackets []
• Objects, surrounded by curly brackets {}
• NULL values

For example, an array of Wdata features would be [ "Tables", "Queries", "Chains" ].

Objects

Objects are surrounded by curly brackets {} and contain a comma-separated list of name/value pairs. Each pair contains a field name in double-quotes "", followed by a colon : and its value. Objects support the same types and syntax of values as arrays. For example:

{
"product":"Wdata",
"company":"Workiva".
"features":[
     "Tables",
     "Queries",
     "Chains"
     ]
}

Set up the JSON connector

Note: To make the connector available for use in your organization's chains, an org security administrator first enables it from Configuration.

1. From Chain Builder, click Connections  compare_arrows, and then Create at the top right.
2. Under BizApp Connection, select JSON and the default CloudRunner.
   
3. Under Basic Info, enter a unique name and description to help identify the connector.
4. Select the environments to use with the connection, and click Save.
5. To test the connection, create and run a chain with the connector's CSV to JSON command, and verify it returns a valid output.

Troubleshooting

If the connector's Array to CSV command fails to generate a valid output, check for these potential issues.

Array to CSV succeeds, but its output has no data

If the Array to CSV command indicates a successful conversion but its output has no data, ensure its Input File input contains JSON arrays. For example, this JSON would return no details about bicycles and balls:

{
 "store": {
 "bicycle": {
 "color": "red",
 "price": 19.95
 },
 "ball": {
 "color": "blue",
 "price": 21.95
 }
 }
}

Instead, to return details about bicycles and balls, wrap the details with square brackets [], like an array. For example:

{
 "store": {
 "bicycle": [{
 "color": "red",
 "price": 19.95
 }],
 "ball": [{
 "color": "blue",
 "price": 21.95
 }]
 }
}

Root array element path not found

If the Array to CSV error returns "Error finding the root array element, please check your path: Path not found," verify the key to isolate is in an array. For example, to capture usageUnit in this JSON, enter the JSONPath .pricingInfo[0].pricingExpression.usageUnit as the command's Path to Root input:

{
 "skus": [{
 "skuId": "0033-4F4C-36F1",
 "description": "Long Term Storage (us-east4)",
 "category": {
 "serviceDisplayName": "BigQuery",
 "usageType": "OnDemand"
 },
 "serviceRegions": [
 "us-east4"
 ],
 "pricingInfo": [{
 "summary": "",
 "pricingExpression": {
 "usageUnit": "GiBy.mo",
 "displayQuantity": 1,
 "tieredRates": [{
 "startUsageAmount": 0,
 "unitPrice": {
 "currencyCode": "USD",
 "units": "0"
 }
 }]
 },
 "currencyConversionRate": 1,
 "effectiveTime": "2019-08-18T02:30:55.193Z"
 }],
 "serviceProviderName": "Google"
 }]
}