Convert a query result into Object

[CoderNadir]

Hi,

I'm creating queries which fetch data directly from database (PostgeSQL).
I have 2 cases I want to ask for help with!

1- Getting all records from a Table (let's name this query getEmployee),
ex 1: SELECT * FROM employee;
how can I convert the records into an array of objects (simple one without the need to use JS to covert it or something like this, maybe some retool tools or external libraries or at least less code to do it)
ex 2: SELECT * FROM employee WHERE id = 'uuid';
So how can I convert a single and many records into a single object or array of objects?

2- Same example above, but what if I have 2/3 relations with the same table how can I do it?
Ex 3: SELECT * FROM "employee" INNER JOIN "user" ON "employee"."userId" = "user"."id" INNER JOIN "user_image" ON "user"."imageId" = "user_image"."id" WHERE "employee"."id" = "uuid";
-- Please the SQL query I wrote might have some mistakes, but I guess it's clear to understand what I want to ask about!
So what I want to do and have in the second case: is when I want to use the fetched data from the query, I can easily read that data like the following:

• Employee Badge: getEmployee.data.badgeNumber
• Employee Name: getEmployee.data.user.name
• Employee Image: getEmployee.data.user.image.url

[jg80]

Hi @CoderNadir - welcome to the forum!

1. When you execute the query in Retool, you can natively access it two ways, either an an object of arrays, or an array consisting of an object of arrays (showing it here in a JSON explorer component; as you can see I filtered the demo data set to the first three records to make it easier to see):
   
   
2. Assuming we just want to get the information for the user with id = 2, update the query and directly access the information from it (i.e., as shown below the JSON similar to your stated second use case:)
   
   

As to expanding the query to include other tables, use standard SQL queries. If you want to pass variables into the SQL, you just need to swap out the criteria with the source of the variable, for example:
select * from users where id = 2
becomes something like
select * from users where id = {{componentName.value}}
or
select * from users where name = '{{componentName.data}}' (don't forget the quotes for strings)

Hope that helps.

[CoderNadir]

@jg80 thank you for replying!
and sorry for the delay!

I've come up with a way to use json_agg() and json_build_object() to achieve what I was looking for!

I might share an example soon!
I'm just a bit busy