Postgres or Supabase Edge Function to fetch a user and its items

Question

I am using Flutter and I have three tables in Supabase:

• user (user_id, name)
• user_items (user_item_id, user_id, item_id)
• items (item_id, cost)

Is it possible to make one single query, maybe in an Edge Function, to get a result looking something like this:

{
  user_id: 1,
  name: 'John',
  items: [
    {item_id: 1, cost: 15},
    {item_id: 2, cost: 25}
  ]
}

Answer 1

Postgres have a function called json_agg().

SELECT
  users.*,
  json_agg(items.*) as items
FROM
  users LEFT JOIN user_items ON users.user_id = user_items.user_id
  LEFT JOIN items ON user_items.item_id = items.item_id
GROUP BY
  users.*;