© 2026 Hedgehog Software, LLC

TwitterGitHubDiscord
More
CommunitiesDocsAboutTermsPrivacy
Search
Star
Setup for Free
SupabaseS
Supabase•4y ago•
2 replies
Xzight

How can I join from a many to many into an array of values?

Sorry the title is bad it is hard to explain in one line. Basically I have tasks, tags, and a tags_to_task join table and I want to query for tasks and all their related tags. if I run this function I get the output from the image:
create or replace function getTasksWithTags(input_owner_id uuid) returns table(id int8, title text, description text, priority text, due_by timestamptz, is_completed bool, tag_ids int8, tag_titles text) as $$
begin
  return query select tasks.id, tasks.title, tasks.description, tasks.priority, tasks.due_by, tasks.is_completed, tags_to_tasks.tag_id, tags.title from tasks
  left join tags_to_tasks on tasks.id = tags_to_tasks.task_id
  left join tags on tags_to_tasks.tag_id = tags.id
  where tasks.owner_id = input_owner_id;
end;
$$ language plpgsql;
create or replace function getTasksWithTags(input_owner_id uuid) returns table(id int8, title text, description text, priority text, due_by timestamptz, is_completed bool, tag_ids int8, tag_titles text) as $$
begin
  return query select tasks.id, tasks.title, tasks.description, tasks.priority, tasks.due_by, tasks.is_completed, tags_to_tasks.tag_id, tags.title from tasks
  left join tags_to_tasks on tasks.id = tags_to_tasks.task_id
  left join tags on tags_to_tasks.tag_id = tags.id
  where tasks.owner_id = input_owner_id;
end;
$$ language plpgsql;

What I am wondering is instead of having a row for each
tag
tag
that is on a
task
task
how can I make the output 1 row per task with an array for
tag_ids
tag_ids
and
tag_titles
tag_titles
unknown.png
Supabase banner
SupabaseJoin
Supabase gives you the tools, documentation, and community that makes managing databases, authentication, and backend infrastructure a lot less overwhelming.
45,816Members
Resources

Similar Threads

Was this page helpful?
Recent Announcements

Similar Threads

how can I pass an array of objects to an rpc call?
SupabaseSSupabase / help-and-questions
4y ago
how to have an array of foreign keys (one to many relationship)?
SupabaseSSupabase / help-and-questions
4y ago
many-to-many returns empty array
SupabaseSSupabase / help-and-questions
13mo ago
How to unpack an array of arrays with an RPC
SupabaseSSupabase / help-and-questions
4y ago