topic Re: Pass through JSON "string" from Database query in Workato Pros Discussion Board

Pass through JSON "string" from Database query

andreasFe — Fri, 11 Apr 2025 15:50:48 GMT

I'm querying a database from an API-Endpoint trigger and want to return the result as JSON.
So far nothing out of the ordinary, and it works for other usecases.

The data i do get back from the database though already contains a JSON string in one column.
An (modified) example of the result (as "raw JSON" from the job-detail view) looks like this:

{ "rows": [ { "id": 55540, "number": "7200", "quantity": "2037", "description": "Something 1/2", "history": "[{\"car_id\":\"493\", \"position\":\"left\", \"added\":\"20181101\", \"removed\":\"20240827\", \"ref_id\":\"55\"}]" }, { "id": 4802, "number": "2600", "serial_num": "2306", "description": "Something 2/2", "history": "[{\"car_id\":\"84\", \"position\":\"left\", \"added\":\"20180930\", \"removed\":\"20230223\", \"ref_id\":\"846\"},{\"car_id\":\"84\", \"position\":\"left\", \"added\":\"20230320\", \"removed\":\"20250318\", \"ref_id\":\"84\"}]" }, { "id": 55536, "number": "7100", "quantity": "2037", "description": "Desc 3", "history": "[{\"car_id\":\"493\", \"position\":\"right\", \"added\":\"20181101\", \"removed\":\"20240827\", \"ref_id\":\"55\"}]" }, { "id": 55531, "number": "7600", "quantity": "2315", "description": "Desc 3", "history": "[{\"car_id\":\"493\", \"position\":\"right\", \"added\":\"20181101\", \"removed\":\"20240827\", \"ref_id\":\"49\"}]" } ], "rows_count": 4 }

When returning the result in the API I created a return schema that anticipates, that history should be a list (and I also tried object), with nested attributes of car_id, position, added, removed, ref_id.

The problem now is that i can not manage to make the history-column be interpreted as a list-pill to use in the return schema.
As the result set is thousands of rows, I can't manually iterate through them to convert the history-json "string" to a list that I could use afterwards (without creating thousands of tasks).
When using a schema that just uses a history-field with string type - the output is just like the example above. It contains all information, but the JSON is embedded & escaped instead of passed through.

Any idea on how to get this working as desired and simply "pass through" the JSON from the DB, without escaping it as a string?

Re: Pass through JSON "string" from Database query

gary1 — Fri, 11 Apr 2025 16:08:59 GMT

I've dealt with similar situations before. You can't build a parser into the response schema that will "de-stringify" the string. You need to parse it in a separate action. There's a bunch of ways to do it, but to avoid loops/tasks I recommend dumping your output into a JS action and using JSON.parse like this:

exports.main = async ({ foo }) => { return JSON.parse("{\"hello\": \"hello\"}"); };

This outputs a parsed object.

Re: Pass through JSON "string" from Database query

gary1 — Fri, 11 Apr 2025 16:45:29 GMT

Here's the full JS snippet for returning the full array with history parsed:

exports.main = async ({ rows }) => { parsed = rows.map(item => ( { ...item, history: JSON.parse(item.history) })); return parsed; };

Re: Pass through JSON "string" from Database query

andreasFe — Fri, 11 Apr 2025 17:28:40 GMT

Tanks, that works great!
I had to modify just a tiny bit:

return {rows: parsed};

as otherwise it would just return an array (although in the job history UI of course it looks like an Object with an "Output: " array property) and I'm not sure how to map that to an output schema / capture it to return it in the API response.