© 2026 Hedgehog Software, LLC

TwitterGitHubDiscord
More
CommunitiesDocsAboutTermsPrivacy
Search
Star
Setup for Free
Drizzle TeamDT
Drizzle Team•3y ago•
7 replies
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]);
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
integrations
and
websites
websites
, as queried with
json_agg
json_agg
returns me objects with
snake_case
snake_case
and Dates that are formatted as
string
string
instead of
Date
Date
.

It looks like Drizzle do this transformation by default on the
usersTable
usersTable
by default (ie. when the table is called directly) but not on my
json_agg
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
json_agg
but that lets Drizzle format correctly the output? Thank you very much !
Drizzle TeamJoin
The official Discord for all Drizzle related projects, such as Drizzle ORM, Drizzle Kit, Drizzle Studio and more!
11,879Members
Resources

Similar Threads

Was this page helpful?
Recent Announcements

Similar Threads

limiting json_agg
Drizzle TeamDTDrizzle Team / help
2y ago
Postgres json_agg
Drizzle TeamDTDrizzle Team / help
3y ago
Custom SQL function (json_agg & json_build_object)
Drizzle TeamDTDrizzle Team / help
3y ago
Custom postgres JSON_AGG function help
Drizzle TeamDTDrizzle Team / help
15mo ago