POSTGRESQL return results as object without javascript

🤝 Community Show & Tell
1

I often see people ask about the return type of Retool DB (POSTGRESQL) and how to convert the array of column objects to a single JSON object. The normal solution is to add a transformer to the query and use return formatDataAsObject(data);. While this does work, you do have to remember to use it and if you aren't use to that it can get frustrating. There are pleanty of people that are not familiar with javascript but they understand the basics of POSTGRESQL and/or SQL. For them a pure database solution might make more sense and that's what I'll be sharing:

my original query:

SELECT *
FROM openai_assistants
    WHERE user_id = 0
    LIMIT 1

it's results:

data: {
    column_1: [row1_value, row2_value]
    column_2: [row1_value, row2_value]
    /* this continues for every column in the table */
}

after using formatDataAsObject():

data: [
{ 
    column_1: row1_value
    column_1: row1_value
},
{
    column_1: row2_value
    column_1: row2_value
},
]

Pure POSTGRESQL solution:

SELECT to_json(ret)
FROM(
    SELECT *
    FROM openai_assistants
        WHERE user_id = 0
        LIMIT 1
) ret

result:

data: {
  to_json: [
    {
      column_1: row1_value
      column_2: row1_value
    },
    {
      column_1: row2_value
      column_2: row2_value
    }
  ]
}

Since this is "transforms" the data on the database side it allows the database to make certain shortcuts when building the JSON objects. Using a transformer while it results in the same object the javascript is unable to make shortcuts and with large datasets it could be much slower (especially if the client browser is running the JS instead of the database server which is much more powerful)

4 Likes
2

I haven't ever thought of doing this in the query itself, @bobthebear! That's a great tip. :+1:

1 Like