Support json_agg(column_ref).

[igalklebanov]

Hey 👋

Currently eb.fn.jsonAgg supports table names and expressions as inputs. In such cases, the return type is a JSON object array.

json_agg also supports passing a column reference. In such cases, the return type is a literal array.
We should support this.

const result = await db
  .selectFrom('person')
  .innerJoin('pet', 'pet.owner_id', 'person.id')
  .select((eb) =>
    eb.fn.jsonAgg('pet.name').as('person_pets')
  )
  .executeTakeFirstOrThrow()

The return type would be { person_pets: string[] }.

[koskimas]

Good idea! jsonAgg is also quite broken with left joins, and there's nothing we can do about it easily. Not related to this issue though.

We express left joined tables in the DB generic as tables where each column is nullable. While this works in most cases, in case of jsonAgg it produces objects with nullable properties when it should produce nullable objects with normal property types 😬

We have no way to tell if a column is nullable in DB because it's actually nullable or if it was put there using a left join.

We could express left joined tables in DB like this

{
  person: Person,
  leftJoined: Pet | null
}

But that's a big type overhaul and most likely a breaking change.

[SimonSimCity]

Another example of how this could be used is when using subqueries instead of joins. Here's something I found out about it:

This query (though correct in TypeScript) fails when executing:

const result = await db
  .selectFrom("person")
  .select((eb) => [
    eb.fn.jsonAgg(eb
      .selectFrom("pet")
      .whereRef("pet.owner_id", "=", "person.id")
      .select("pet.name"))
      .as("person_pets"),
  ])
  .executeTakeFirstOrThrow()

The error message is:

more than one row returned by a subquery used as an expression

But if you have another select on the outer query, you'd even get an error for that you'd have to group on each of the selected columns, which is confusing 🙈

This query can run on postgres, but is invalid as of TypeScript - hence this ticket:

const result = await db
  .selectFrom("person")
  .select((eb) => [
    eb
      .selectFrom("pet")
      .whereRef("pet.owner_id", "=", "person.id")
      .select(eb => eb.fn.jsonAgg("pet.name"))
      .as("person_pets"),
  ])
  .executeTakeFirstOrThrow()

If there's anything I can help with to get this done, please write, and I'll be happy to pick it up and see whether I can get it to PR-state!

[igalklebanov]

Hey 👋

It's yours. Swing by Discord if you've got any questions. DMs (to me) also welcome.

[SimonSimCity]

If you need a work-around, you can cast the types as following (the type DB is your database):

import { RawBuilder, ExtractTypeFromStringReference } from 'kysely'

const result = await db
  .selectFrom("person")
  .select((eb) => [
    eb
      .selectFrom("pet")
      .whereRef("pet.owner_id", "=", "person.id")
      .select(eb => eb.fn.jsonAgg("pet.name" as unknown as "pet") as unknown as RawBuilder<ExtractTypeFromStringReference<DB,"pet","name">[] | null>)
      .as("person_pets"),
  ])
  .executeTakeFirstOrThrow()

import { RawBuilder, ExtractTypeFromStringReference } from 'kysely'

const result = await db
  .selectFrom('person')
  .innerJoin('pet', 'pet.owner_id', 'person.id')
  .select((eb) =>
    (eb.fn.jsonAgg("pet.name" as unknown as "pet") as unknown as RawBuilder<ExtractTypeFromStringReference<DB,"pet","name">[] | null>).as('person_pets')
  )
  .executeTakeFirstOrThrow()

In case you don't want the aggregated property to be a union with null, you cannot use subqueries (first code-sample above) but have to use join instead and wrap it in a coalesce function:

import { sql, RawBuilder, ExtractTypeFromStringReference } from 'kysely'

const result = await db
  .selectFrom('person')
  .innerJoin('pet', 'pet.owner_id', 'person.id')
  .select((eb) =>
    eb.fn.coalesce(
      (eb.fn.jsonAgg("pet.name" as unknown as "pet") as unknown as RawBuilder<ExtractTypeFromStringReference<Database,"pet","name">[] | null>),
      sql<never[]>`'[]'`,
    ).as('person_pets')
  )
  .groupBy("person.id")
  .executeTakeFirstOrThrow()

This is not because of some typings in Kysely, but coalesce doesn't work on subqueries on my postgres version - at least not on my installation (PostgreSQL 14.13)