Working with JSON Datasource

Hi,

Trying to query a json datasource but unable to split the array returned into rows as desired. I tried to create a query similar to as follows …

url: “https://app.cloudability.com/api/1/reporting/cost/run
params:
auth_token: ****************************
dimensions: vendor_account_name,vendor_account_identifier
start_date: 0 of last month
end_date: end of last month
metrics: cost
sort_by: cost
order: desc
fields: [results]
#path: [vendor_account_identifier, vendor_account_name, cost]

But the result seems to be a string array that when using the “path” parameter creates an error … Error running query: ‘list’ object has no attribute 'split’

Without the “path” parameter, I am able to get a single row back that has an array within it similar to the following …

results

[{“vendor_account_identifier”:“9999-9999-9999”,“vendor_account_name”:“acct1”,“cost”:"$51.00"},
{“vendor_account_identifier”:“9999-9999-9998”,“vendor_account_name”:“acct2”,“cost”:"$50.00"},
{“vendor_account_identifier”:“9999-9999-9997”,“vendor_account_name”:“acct3”,“cost”:"$52.00"}]

Any ideas on how to create individual rows for the elements within the array … the desired result is a table with 3 columns (vendor_account_identifier, vendor_account_name, cost) and 3 rows

This should work. The issue is you swapped the values of your path and fields keys. path must be a string. fields can be a list.

From this line in the source code:

The query runner tries to split the path key on periods (.).

Change your query as follows and it will work:

url: “https://app.cloudability.com/api/1/reporting/cost/run”
params:
auth_token: ****************************
dimensions: vendor_account_name,vendor_account_identifier
start_date: 0 of last month
end_date: end of last month
metrics: cost
sort_by: cost
order: desc
fields: [vendor_account_identifier, vendor_account_name, cost]
path: results 

I double checked this by pasting your sample data into a Github Gist and aiming the JSON data source at it. Only difference is I called my inner object array data instead of results.

Thank you. I did try swapping these before I raised the question but the issue was that I had the brackets around the path. Removing them did the trick. Appreciate your help on this.

Precisely.

Wrapping the value in brackets makes it a list in YAML syntax :wink: