Problem with querying Retool database table

[Mike_Beattie]

hi
I'm having trouble doing what seems to be a simple job. I have a Retool Database table with two fields : "name" and "status". It has 580 records.

I have a "Query JSON with SQL query" called "notion_sync" . This is a query of type "Retool database", and the sql is "select name, status from notion_import".

When I query this "notion_sync" using "Query JSON with SQL query", instead of returning an array of 580 records objects, it returns 2 objects, one for each field , and each containing an array of 580 values :

I'm having trouble seeing why this isn't returning an array of 580 objects, each with 2 key value pairs, one for each field.

Any Ideas ?

Thanks, Mike.

[Kabirdas]

Hey @Mike_Beattie!

Can you try using select * from {{ formatDataAsArray(notion_sync.data) }} and let me know if that works?

Query JSON with SQL queries expect data to be formatted as an array of objects but other SQL queries, by default, don't return data in that format so it can be helpful to use the formatDataAsArray helper function. Either directly in the Query JSON with SQL query (as above) or as a query transformer on the notion_sync query.

[Mike_Beattie]

Perfect !!! That was it. It's all working now, thanks so much for this.