Convert API response JSON to Array to disaply it on table

Question

Hello there! 
 I am trying to put data from bigquery response API, into a table to create record types. 
 So far, I have been able to get the response from bigquery and convert it to appian values, the response looks like this:

then, I created a rule to narrow down to the values, but as you can see, the column name does not come from the response, so it displays all the values as rows but on the following way :

so as you see, I do not have columns there , I guess the best way to go on this, will be to get the JSON response converted to an array, and then pass it to a table but not sure if this is possible, any advise?

Mike Schmitt · Answer

Do the column names come back somewhere else in the API response, or are you just expected to know them? 
 Either way I suspect you'll have to loop over the response rows and manually construct the necessary dictionary from each row's results. If each row will always have the exact same number of columns and you can just hardcode the column/property names, then this should be pretty easy. If there's an expectation that the columns might be variable and/or will need to be named after data that's given in another part of the response, then it's still doable but might be a bit harder.

Mike Schmitt · Answer

I just mean that you'd loop over the response rows in your handler Expression Rule and create a formatted dictionary that gets passed back to wherever you need it. For starters: createDictionary(
 {"State", "Country", "Type", "Date"},
 {
 "Cali",
 "Argentina",
 "AA",
 today()
 }
)

Mike Schmitt · Answer

I believe it would end up being something along these lines, plus or minus: 
 a!forEach(
 rule!bigQuery().result.body.rows,
 
 createDictionary(
 {"State", "Country", ...}, /* array of all column/property names */
 fv!item.v
 )
)

Mike Schmitt · Answer

Try these permutations, just in case, and see if either happens to work any better: 
 first: a!forEach(
 rule!bigQuery().result.body.rows.f, /* adding ".f" at the end here */
 
 createDictionary(
 {"State", "Country", ...}, /* array of all column/property names */
 fv!item.v
 )
) 
 Second, somewhat more radical: a!forEach(
 rule!bigQuery().result.body.rows.f,
 
 createDictionary(
 {"State", "Country", ...}, /* array of all column/property names */
 a!forEach(
 /* create an inner forEach loop, attempt to loop over the inner array of dictionaries containing the "v:" property */
 fv!item,
 fv!item.v
 )
 )
)

Mike Schmitt · Answer

rodrigoe0001 said: What I need is to simply display that response as a table on a site, do I still need to create a db for that? 
 That depends mainly on whether you need to store the responses for some time, or whether it will be sufficient to call the API on-demand (and every time the user loads that form). Assuming you end up with an expression rule that returns a tidy dictionary of data though, you can simply feed that into the data parameter of the Read-Only Grid and use that live, without having to store it or retrieve it from a DB table.

Mike Schmitt · Answer

Assuming your expression rule now outputs an array of dictionaries with proper column names, try just setting that as the "Data" parameter of your read-only grid. After a few other fairly easy configuration items, you should be ready to go.

Mike Schmitt · Answer

Cool, I knew it was something like that. 
 So from here what you do depends on what your intended end result is. If your aim is to store the data exactly in a DB table, then in general my first step would be to create a DB table with the columns you need, then use the CDT creator in Appian (i.e. create CDT based on existing DB table). Then you would be able to use the above logic i.e. in an expression rule called in a process node, save it to a multiple-PV set as your new CDT type, then pass that into a WTDS node.

Convert API response JSON to Array to disaply it on table

Top Replies