Is it possible to join retool database with json from REST via SQL?

Queries and Resources
1

Hey there.

I have a rest endpoint customers that returns an array of customers. I also have a retool database customer_views. Trying to join customers & customer_views on customer_id using the json sql resource but it doesn't seem like it's working quite right. Can anybody share the best way to do this?

Thanks!

2

Hey @ibgoldbergs! Would you mind sharing the code you're using in the query? It may also be helpful to see the result you're getting. Screenshots are great!

A common bump that folks run into is the fact that SQL queries return data as an object of arrays whereas Query JSON with SQL queries require data inputs to be an array of objects - likely more similar to what you'd get back from your REST endpoint.

If this is the case you can try using the formatDataAsArray helper function to format your SQL query data, e.g.

SELECT * FROM {{ formatDataAsArray(retoolDbQuery.data) }} retooldb JOIN {{ yourApiQuery.data.customers }} customers ON ...etc
3

Hey @Kabirdas -

I ended up writing a js transformer and a small query with Query JSON with SQL to handle this.

First the join:

SELECT *
FROM {{ customers.data }} as c
JOIN {{ formatDataAsArray(customerViews.data) }} as v 
on c.id = v.customer_id;

Then a flatten transformer data, since there were nested objects I wanted to display data from in a grid:

function flattenAttributes(data) {
  return data.map((obj) => {
    const flattenedObj = { ...obj };
    const attributes = flattenedObj.attributes;
    delete flattenedObj.attributes;
    return { ...flattenedObj, ...attributes };
  });
}

const originalData = {{customers.data}};

const flattenedData = flattenAttributes(originalData);
return flattenedData

And then the SQL to select only specific fields after flattened data returned:

SELECT 
  d.id,
  d.businessName,
  d.view1,
  d.view2
FROM {{transformer2.value}} as d

This ended up allowing me to both join the tables, and flatten nested data.

1 Like