Create a json body from sql query result

Queries and Resources
, ,
1

  • Goal: send REST request to API with data from several tables, some of them with multiple lines

  • Details:

** i have an app where i show a table called Documents.
** after selecting a row from this table app populates several other tables visible to user - like document-details, document-suggestions, document-comments and so on. Some of this tables contain multiple lines (e.g. several comments),
** i created a query that gets single pieces of data from populated tables (like document-details.date, document-details.author) and sends them to API in json format, in simple flat structure with all fields - it works well,
** now i am trying to add a new json part to this request that would represent a multiple rows with data about the selected document

someting like
"comments":
[
{
"comment-author": "Adam",
"comment-date": "2024-01-22",
"comment-text": "great doc!"
},
{
"comment-author": "Edward",
"comment-date": "2024-01-21",
"comment-text": "poor doc!"
}
]

so when i select a document i can send an API request with some basic values but also structured multiline values

so basically i think i need something that will convert sql query to json structure and save it into text variable?

I tried creating variable and using initial value for testing (until i find out how to dynamically set this variable to json from my tables) but when i put this variable in body of REST request retool adds \ characters before every " character,

it is rather complicated REST call and i need several structures in it but now i am struggling with adding first one,

any suggestions?

2

I am not 100% confident this is relevant here, but have you checked out the QueryJsonWithSQL resource?

Maybe you could use it to bring all the different results together and query them as if they were sql and do whatever you need with the output.

3

Sorry, but it is not relevant,

what i need to do is to create a JSON structure containing data from several SQL requests, some of them returning tabular data with multiple rows (from PostgreSQL database), and then send this JSON (with some additional body and headers) to API that generates a PDF document

4

Regarding the escaping characters being added, are you stringifying the object prior to submitting it? It sounds like Retool’s stringification is seeing the quotes as part of the string and escaping them. If so trying removing that step.

Are your SQL queries all coming from the same database? If so you may be able to simplify what you have to do on the frontend by moving some of the join logic into the query itself. You can still used transformed versions of the response in your different tables but you’d only have to reach into one object to find the data that you need for your request body.

5

I don't see why each database call can't be their own query (db), and then have one more aggregator query (javascript) that does something like:

return {
  ...otherQuerty.data,
  ...anotherQuery.data,
  thing: thirdQuery.data.map(...)
};

and then in your saving query (db), reference the output of that combining query (the javascript)?

6

it looks like a good idea, that I haven't tried yet :slight_smile: I am reading docs and some jscript tutorials and will check it out