How do I transform an array into new columns for a table?

I have a response like this for every key in a resource query:

{"date": "2023-03-01", "task": "Collect RSVPs for dietary restrictions"},
{"date": "2023-03-05", "task": "Confirm menu options with Jenna"},
{"date": "2023-03-10", "task": "Finalize menu and provide updated list of allergies for guests"}

that I want to convert to columns in a table, e.g. "date", "task". How would I transform this?

I believe that you need to just take the query results and turn them into the object that contains the properties:


My query:

thanks - i tried this, but got the following TypeError. is it because the array i'm trying to access is coming in as a string from the table?

Your entries for the task_array are arrays, and the query returns an array of them so you'll need to do some extra transformation. What does a full task_array entry look like? Just a single date and task, or is it an array of several dates/tasks?

How is the table meant to display the arrays? For just a single task_array value or for all of them?

it's an array of several dates and asks - ideally, the table displays each date and task as a separate line for each task_array value.

Is your query returning rows of arrays? You may need to UNNEST them in the query if so.

@DoingMyBest yeah, it's returning one array in task_array column for entry in the data object - added an example of what it looks like

1 Like

UNNEST should work if that column is an actual array data type.

select unnest(task_array), * from your_table

That should plug directly into the table data source input without any additional transformation.

1 Like