Postgres query returns object of arrays instead of array of objects

Queries and Resources
1
  • Goal:
    I'd like the option to get my query results back as an array of objects like this
{
    "data": [
        {
            "id": 1,
            "name": "foobar"
        },
        {
            "id": 2,
            "name": "barbaz"
        }
    ]
}

But by default Retool seems to be giving me this

{
    "data": {
        "id": [
            1,
            2
        ],
        "name": [
            "foobar",
            "barbaz"
        ]
    }
}

This is actually mad convenient when passing the data to something like table tags column because I can just do {{ item.name }} to get the value out, however if I want to do some filtering or transformation within a Javascript resource query, I can't do that as easily. I'd like to be able to do something like

const match = query.data.find(row => row.name === 'foobar');

But obviously this sort of thing is not possible if the query doesn't return an array of objects. I noticed that Retool exposes a dataArray property, but this property has the identical structure where the top level object has the column names, and then each column has an array of values. The only difference is that this object is always at the first index in the dataArray property.

2

Try return formatDataAsArray(data) in your transformer.