Error using postgres function "arr agg" in a knex query

I am running into a problem when trying to run the following query. I am getting

missing FROM-clause entry for table \"s\"".

I have four tables here: grants, users, regions, and states.

grants can have multiple rows per user
regions can have multiple states per grant
states is just id and state name

I am trying to get a nested "state" object so I don't get duplicated records when grants are available in multiple states. Regions table is innerJoin because there will always be a value matching the grants table: "state_wide" is a boolean.

return db("grants as G")
  .innerJoin("users AS U", "G.user_id", "U.id")
  .innerJoin("regions AS R", "G.id", "R.grant_id")
  .leftJoin("states AS S", "R.state_id", "=", "S.id")
  .select(
    "G.id",
    "G.grant_title",
    "G.grant_number",
    "G.grant_status",
    "G.grant_description",
    "G.grant_amount",
    "G.due_date",
    "U.first_name",
    "U.last_name",
    "U.email",
    "U.telephone",
    "U.department",
    "U.organization_name",
    "U.address_one",
    "U.address_two",
    "U.zip_code",
    "R.country_wide",
    "R.state_id",
    "R.county_id",
    db.raw("array_agg(S.state_name) AS state")
  )
  .groupBy("G.id", "G.grant_title");

Entire Error message

    "Message": "There was an error with your request",
    "Error": "select \"G\".\"id\", \"G\".\"grant_title\", \"G\".\"grant_number\", \"G\".\"grant_status\", \"G\".\"grant_description\", \"G\".\"grant_amount\", \"G\".\"due_date\", \"U\".\"first_name\", \"U\".\"last_name\", \"U\".\"email\", \"U\".\"telephone\", \"U\".\"department\", \"U\".\"organization_name\", \"U\".\"address_one\", \"U\".\"address_two\", \"U\".\"zip_code\", \"R\".\"country_wide\", \"R\".\"state_id\", \"R\".\"county_id\", ARRAY_AGG(S.state_name) as state from \"grants\" as \"G\" inner join \"users\" as \"U\" on \"G\".\"user_id\" = \"U\".\"id\" inner join \"regions\" as \"R\" on \"G\".\"id\" = \"R\".\"grant_id\" left join \"states\" as \"S\" on \"R\".\"state_id\" = \"S\".\"id\" - missing FROM-clause entry for table \"s\""
}```

Solution

Aha. It took me awhile, but the issue is (as richyen intimated) the fact that you're using uppercase aliases. As I noted in the comments, it felt strange that the error used a lowercase 's'.

Postgres is case-insensitive when it comes to keywords and unquoted identifiers.

Normally, Knex surrounds your table names with double quotes, as you can see in your error message, so case is respected. However, it can't surround the alias in your knex.raw statement, because to Knex it's just a string. This leads Postgres to go looking for s.state_name, for which it is indeed missing a FROM clause.

You therefore need to either quote it yourself:

db.raw('array_agg("S".state_name) AS state')

or (much simpler) get into the habit of using lowercase aliases throughout!