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.

json-ex1784×1398 128 KB

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

Hi,
I am getting error " Error running query: Couldn’t find path labels in response. " .
I am trying to access object label in an array in a json using path parameter. Below is the syntax am using.

Doesn’t work :- I am getting. Couldn’t find path labels in response
url: https://api.github.com/repos/getredash/redash/issues
path: labels

Works :-
url: https://api.github.com/repos/getredash/redash/issues/3495
path: assignees

Can you help me understand between assignees data and labels data for which the path works only for assignees but not for labels. I am facing same issue with my real data inhouse.

Hi there and welcome to the forum. Can you share some sample data that reproduces this issue?

Hi Jesse,
The urls am using are github repo urls which you have access.
But below is the sample data am seeing where one works and one doesn’t work.

labels data that doesn’t work :-
[
{
“id”:1728224861,
“node_id”:“MDU6TGFiZWwxNzI4MjI0ODYx”,
“url”:“httpsurl://api.github.com/repos/getredash/redash/labels/Skip%20CI”,
“name”:“Skip CI”,
“color”:“c2c5cc”,
“default”:false,
“description”:""
}
]

assignees data that works :-
[
{
“login”:“redacted”,
“id”:redacted,
“node_id”:“MDQ6VXNlcjI4OTQ4OA==”,
“avatar_url”:“httpsurl://avatars.githubusercontent.com/u/289488?v=4”,
“gravatar_id”:"",
“url”:“httpsurl://api.github.com/users/rauchy”,
“html_url”:“httpsurl://github.com/rauchy”,
“followers_url”:“httpsurl://api.github.com/users/rauchy/followers”,
“following_url”:“httpsurl://api.github.com/users/rauchy/following{/other_user}”,
“gists_url”:“httpsurl://api.github.com/users/rauchy/gists{/gist_id}”,
“starred_url”:“httpsurl://api.github.com/users/rauchy/starred{/owner}{/repo}”,
“subscriptions_url”:“httpsurl://api.github.com/users/rauchy/subscriptions”,
“organizations_url”:“httpsurl://api.github.com/users/rauchy/orgs”,
“repos_url”:“httpsurl://api.github.com/users/rauchy/repos”,
“events_url”:“httpsurl://api.github.com/users/rauchy/events{/privacy}”,
“received_events_url”:“httpsurl://api.github.com/users/rauchy/received_events”,
“type”:“User”,
“site_admin”:false
}
]

Hi Jesse,

The urls am using are github repo public urls which you have access too to get you the sample data.
I tried to get you the sample data but the redash’s website spam filter bot has blocked my response since my response has so many links from the sample data that I was trying to get you.

Gotcha, I understand and I have reproduced the behaviour you described.

The behaviour of path is admittedly nuanced, but it’s currently working as specified. However, if your target API requires a different behaviour, it is pretty straightforward to duplicate the JSON query runner and modify it to meet your needs.

What’s happening is that path assumes the API returns a json object (with keys and values). If the API response is an array of objects, then path won’t work.

Assuming the API response is a json object, path should specify the location of an array of objects within the response that will be projected into a table. For example:

{
  "title": "this is a title",
  "users": [
    {
      "name": "Sara",
      "title": "CEO"
    },
    {
      "name": "Yoval",
      "title": "CFO"
    },
    {
      "name": "Soleil",
      "title": "CTO"
    }
  ]
}

If you received this network response with no path set, your table would look like this:

Now if you set path to users, your table would look like this:

But if the API response looks like this instead (notice the square brackets)

[
{
  "title": "this is a title",
  "users": [
    {
      "name": "Sara",
      "title": "CEO"
    },
    {
      "name": "Yoval",
      "title": "CFO"
    },
    {
      "name": "Soleil",
      "title": "CTO"
    }
  ]
}
]

Setting path to users will fail because the top-level of the response does not contain a key users. This is what you are seeing with your calls to Github’s API.

Jesse,
Firstly, thank you for your time and effort trying to help us all out here.

Both the calls were returning same json data with [ ] square brackets. But still the path works for one of them. Looks like there is something else causing the behavior of the path. Will work on alternative.
Thank you once again.

For anyone else looking to recreate this, I made a couple of gists that you can use instead of the github API to demonstrate the behavior. And a couple videos demonstrating what happens when we pass a path setting like in the example above:

When API returns array

Here is an “api response” that returns an array. When I query it in Redash v10, the path settings fails because the top-level of the response does not include a users key.

When API returns an object

Here is an “api response” that returns an object. Notice the initial table view is identical to the above. But now passing a path setting works because the top-level response includes a ‘users’ key.

I’m happy to help if you have any questions along the way. Good luck!