Drizzle TeamDT
Drizzle Team•2y ago
sevenwestonroads

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]);


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));


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