What's the recommended way to handle joins that aggregate results?

I'm currently converting a codebase that uses raw sql postgres queries over to drizzle, and have this query:

SELECT
  games.name,
  games.image_url,
  JSON_AGG(
    JSON_BUILD_OBJECT(
      'name', game_features.name,
      'icon_url', game_features.icon_url
    )
  ) AS features
FROM
  games
  LEFT JOIN game_features ON games.id = game_features.game_id
GROUP BY
  games.name,
  games.image_url,
  games.sort_order
ORDER BY
  games.sort_order;

SELECT
  games.name,
  games.image_url,
  JSON_AGG(
    JSON_BUILD_OBJECT(
      'name', game_features.name,
      'icon_url', game_features.icon_url
    )
  ) AS features
FROM
  games
  LEFT JOIN game_features ON games.id = game_features.game_id
GROUP BY
  games.name,
  games.image_url,
  games.sort_order
ORDER BY
  games.sort_order;

I was wondering what the recommended way of queries that aggregate joins using functions such as JSON_AGG or ARRAY_AGG?

4 Replies

lewisedcOP•2w ago

One solution I came up with is this:

db
    .select({
      name: gamesTable.name,
      imageUrl: gamesTable.imageUrl,
      features: sql`JSON_AGG(JSON_BUILD_OBJECT('name', ${gameFeaturesTable.name}, 'icon_url', ${gameFeaturesTable.iconUrl}))`,
    })
    .from(gamesTable)
    .leftJoin(gameFeaturesTable, eq(gameFeaturesTable.id, gamesToGameFeaturesTable.gameFeatureId))
    .groupBy(gamesTable.name, gamesTable.imageUrl, gamesTable.sortOrder)
    .orderBy(gamesTable.sortOrder);

db
    .select({
      name: gamesTable.name,
      imageUrl: gamesTable.imageUrl,
      features: sql`JSON_AGG(JSON_BUILD_OBJECT('name', ${gameFeaturesTable.name}, 'icon_url', ${gameFeaturesTable.iconUrl}))`,
    })
    .from(gamesTable)
    .leftJoin(gameFeaturesTable, eq(gameFeaturesTable.id, gamesToGameFeaturesTable.gameFeatureId))
    .groupBy(gamesTable.name, gamesTable.imageUrl, gamesTable.sortOrder)
    .orderBy(gamesTable.sortOrder);

However this has the downside of not being strongly typed as the features column of the result is now type unknown. Another way I can think of is just doing the joins normally and then using javascript to actual format it how I want. Would appriciate peoples take on this! bump

Mario564•2w ago

The approach you're using is currently the best. Drizzle doesn't have JSON helpers at the moment so you have to create the types and map it in JS/TS

lewisedcOP•2w ago

Thanks for letting me know! I had an obsession in the past with trying to write sql queries that got all the data and formatted it exactly how I needed it so I'm going to be writing a lot of data formatters, but still loving Drizzle haha 😄

Mario564•2w ago

Sounds good. JSON util functions are definitely on the roadmap, although no ETA for them as of yet

Gaming

Programming

What's the recommended way to handle joins that aggregate results?