Pass through JSON "string" from Database query

[andreasFe]

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?

[gary1]

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.

[gary1 · Accepted solution]

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;
};

[andreasFe]

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.