Can I get a property instead of array of records using supabase select() in join table?

Question

Take a look at the schema visualizer:

This is my request:

const { data } = await supabase.from('teams').select(`
  *,
  members:profiles (
    *,
    roles (
      name
    ),
    members(
      isTeamLeader
    )
  )
`)

The response I get is an array of objects of following structure:

{
    "id": "team_id",
    "created_at": "timestamp",
    "name": "team_name",
    "members": [
        {
            "first_name": "profile_first_name",
            "last_name": "profile_last_name",
            "user_id": "user_id",
            "created_at": "timestamp",
            "id": "profile_id",
            "avatar_id": null,
            "bio": "profile_bio",
            "roles": [
                {
                    "name": "role_name"
                }
            ],
            "members": [
                {
                    "isTeamLeader": true
                }
            ]
        }
    ]
}

I would like to get other response structure. In particular:

{
  "id": "team_id",
  "created_at": "timestamp",
  "name": "team_name",
  "members": [
      {
          "first_name": "profile_first_name",
          "last_name": "profile_last_name",
          "user_id": "user_id",
          "created_at": "timestamp",
          "id": "profile_id",
          "avatar_id": null,
          "bio": "profile_bio",
          "role_name": "role_name",
          "isTeamLeader": true
      }
  ]
}

How would I change my request in order to achieve this?

Answer 1

The supabase-js uses PostgREST. According to PostgREST doc you can spread the embedded resource with an ellipsis ... spread operator:

const { data } = await supabase.from('teams').select(`
  *,
  members:profiles (
    *,
    ...roles (
      name
    )&roles.limit=1,
    ...members(
      isTeamLeader
    )&members.limit=1
  )
`)