© 2026 Hedgehog Software, LLC

TwitterGitHubDiscord
More
CommunitiesDocsAboutTermsPrivacy
Search
Star
Setup for Free
Drizzle TeamDT
Drizzle Team•15mo ago
gip2000

Custom postgres JSON_AGG function help

I want to create a function simmilar to
db.select
db.select
that for JsonAgg.

I also want to be able to do things like
jsonAgg({...}).orderBy(...).filter(...).as(..)
jsonAgg({...}).orderBy(...).filter(...).as(..)
In my sql queries to make it more like the rest of the ORM. (I need these orderBy and filters optionally, and I know I can just pass them in as arguments but i'm trying to keep the api similar to how the rest of drizzle would implement a feature like this.

I tried extending the SQL class function like this
class JsonAgg<
  T extends SelectedFields,
  AllowNull extends boolean = false,
> extends SQL<never> {
  private filterProp: SQL | null = null;
  private orderByProp: SQL | null = null;
  private allowNull: AllowNull;

  constructor(select: T, allowNull = false as AllowNull) {
    const builtJson = jsonBuild(select);
    super([sql`COALESCE(json_agg(${builtJson}`]);
    this.allowNull = allowNull;
  }

  orderBy(orderBy: SQL | undefined) {
    this.orderByProp = orderBy ?? null;
    return this;
  }

  filter(filterProp: SQL | undefined) {
    this.filterProp = filterProp ?? null;
    return this;
  }

  //@ts-expect-error this function is hard to override
  override as(
    alias: string,
  ): SQL.Aliased<
    AllowNull extends true
      ? SelectResultFields<T>[] | null
      : SelectResultFields<T>[]
  > {
    if (this.orderByProp) {
      this.append(sql`ORDER BY ${this.orderByProp}`);
    }
    this.append(sql`)`); 
    if (this.filterProp) {
      this.append(sql`FILTER (WHERE ${this.filterProp})`);
    }
    this.append(sql`,${this.allowNull ? sql.raw("null") : sql.raw("'[]'")})`);
    if (!alias) throw new Error("deprecated");
    return super.as(alias);
  }
}
class JsonAgg<
  T extends SelectedFields,
  AllowNull extends boolean = false,
> extends SQL<never> {
  private filterProp: SQL | null = null;
  private orderByProp: SQL | null = null;
  private allowNull: AllowNull;

  constructor(select: T, allowNull = false as AllowNull) {
    const builtJson = jsonBuild(select);
    super([sql`COALESCE(json_agg(${builtJson}`]);
    this.allowNull = allowNull;
  }

  orderBy(orderBy: SQL | undefined) {
    this.orderByProp = orderBy ?? null;
    return this;
  }

  filter(filterProp: SQL | undefined) {
    this.filterProp = filterProp ?? null;
    return this;
  }

  //@ts-expect-error this function is hard to override
  override as(
    alias: string,
  ): SQL.Aliased<
    AllowNull extends true
      ? SelectResultFields<T>[] | null
      : SelectResultFields<T>[]
  > {
    if (this.orderByProp) {
      this.append(sql`ORDER BY ${this.orderByProp}`);
    }
    this.append(sql`)`); 
    if (this.filterProp) {
      this.append(sql`FILTER (WHERE ${this.filterProp})`);
    }
    this.append(sql`,${this.allowNull ? sql.raw("null") : sql.raw("'[]'")})`);
    if (!alias) throw new Error("deprecated");
    return super.as(alias);
  }
}

The issue is I can't find a function I can actually override that will let me finish the builder pattern.
I know at this point I could just not override
as
as
and not extend
SQL
SQL
and add a
build
build
function. I was just wondering if there was a way to do this without having to do that?
Drizzle TeamJoin
The official Discord for all Drizzle related projects, such as Drizzle ORM, Drizzle Kit, Drizzle Studio and more!
11,879Members
Resources
Was this page helpful?

Similar Threads

Recent Announcements

Similar Threads

Postgres json_agg
Drizzle TeamDTDrizzle Team / help
3y ago
Custom SQL function (json_agg & json_build_object)
Drizzle TeamDTDrizzle Team / help
3y ago
limiting json_agg
Drizzle TeamDTDrizzle Team / help
2y ago
Using json_agg does not format correctly
Drizzle TeamDTDrizzle Team / help
3y ago