Help with transform on data

Queries and Resources
1

Hi all, hoping for a bit of help with what's probably a very simple one. Filtering a query based on a field "category" being equal to "Internal Detection".

I've got a query returning data from my database and it looks to be 4 arrays (the columns) (description, buy_price, sell_price, category), each with 32 items (the rows). I'm sure I should just be able to use the js filter function but i'm struggling.

So I'm trying something like the below but I'm clearly getting this wrong, the problem is I can't really debug in retool very well.

const data1 = {{selectAllEquipment.data}} //returns 4 arrays, each with 32 items
var newArray = data1.filter(obj => obj.category === "Internal Detection")
return var

This doesn't return any results. Any ideas greatly appreciated.

2

Hey @Lewis_Foster,

you can apply the filter directly within your data variable, e.g. {{selectAllEquipment.data.filter (obj => obj.category === "Internal Detection")}}

Ah, but it need to be an array, so you can use {{ formatDataAsArray(selectAllEquipment.data).filter(obj => obj.category === "Internal Detection") )}}

In alternative, you can use your "Transform Results" section within your selectAllEquipment query and add formatDataAsArray (data), so that you don't have to add this helper elsewhere.

2 Likes
3

Hey Lewis,

like Miguel said you need to transform your data to an array first.

Database queries return an object with key names that correspond to the column names. Each key contains an array of values. (see docs).
formatDataAsArray takes an object of that format and returns an array with objects. The objects in the array have the same keys, and each key contains a single value.

E.g:

x = {
    id: [1, 2, 3],
    value: [100, 400, 900],
};
formatDataAsArray(x);

will return [{key: 1, value: 100}, {key: 2, value: 400}, {key: 3, value: 900}]

If you just need the filtered list you should consider to apply the filter in your sql query.

1 Like
4

Thanks so much. Appreciate your time.