Using json_agg does not format correctly

I've been querying my PSQL DB with the following query

const enrichedUser = await client
    .select({
      user: usersTable,
      integrations: sql<Integration[]>`json_agg(integrations.*)`,
      websites: sql<Website[]>`json_agg(websites.*)`,
    })
    .from(usersTable)
    .where(eq(usersTable.id, userId))
    .leftJoin(integrationsTable, eq(integrationsTable.userId, usersTable.id))
    .leftJoin(websitesTable, eq(websitesTable.userId, usersTable.id))
    .orderBy(desc(websitesTable.createdAt))
    .groupBy(usersTable.id, websitesTable.createdAt)
    .execute()
    .then((r) => r[0]);

const enrichedUser = await client
    .select({
      user: usersTable,
      integrations: sql<Integration[]>`json_agg(integrations.*)`,
      websites: sql<Website[]>`json_agg(websites.*)`,
    })
    .from(usersTable)
    .where(eq(usersTable.id, userId))
    .leftJoin(integrationsTable, eq(integrationsTable.userId, usersTable.id))
    .leftJoin(websitesTable, eq(websitesTable.userId, usersTable.id))
    .orderBy(desc(websitesTable.createdAt))
    .groupBy(usersTable.id, websitesTable.createdAt)
    .execute()
    .then((r) => r[0]);

However the array of integrations and websites, as queried with json_agg returns me objects with snake_case and Dates that are formatted as string instead of Date. It looks like Drizzle do this transformation by default on the usersTable by default (ie. when the table is called directly) but not on my json_agg tables which forces me to do this extra formatting step below

    formattedWebsites = formattedWebsites
    .map((w) => snakeCaseToCamelCase<Website>(w))
    .map((w) => convertDatesStringToDate<Website>(w));

  formattedIntegrations = formattedIntegrations
    .map((i) => snakeCaseToCamelCase<Integration>(i))
    .map((i) => convertDatesStringToDate<Integration>(i));

    formattedWebsites = formattedWebsites
    .map((w) => snakeCaseToCamelCase<Website>(w))
    .map((w) => convertDatesStringToDate<Website>(w));

  formattedIntegrations = formattedIntegrations
    .map((i) => snakeCaseToCamelCase<Integration>(i))
    .map((i) => convertDatesStringToDate<Integration>(i));

Is there another way of doing my json_agg but that lets Drizzle format correctly the output? Thank you very much !

4 Replies

Angelelz•6mo ago

When you use the sql operator you are bypassing any mapping that dirzzle does for you May I ask why you're not using the drizzle relational query builder? It does all of this for you

sevenwestonroads•5mo ago

I didn't know it existed @Angelelz - do you mind sharing an example? Thank you !

Angelelz•5mo ago

If you want to use the RQB, you'll need to define the relations. You can follow the docs depending on what type of relations you have

Angelelz•5mo ago

https://orm.drizzle.team/docs/rqb

Drizzle ORM - next gen TypeScript ORM

Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.

Gaming

Programming

Using json_agg does not format correctly