json agg for drizzle

json-agg.ts

export function jsonAgg<T extends Record<string, AnyColumn>>(select: T) {
  const chunks: SQL[] = [];

  Object.entries(select).forEach(([key, column], index) => {
    if (index > 0) chunks.push(sql`,`);
    chunks.push(sql.raw(`'${key}',`), sql`${column}`);
  });

  return sql<InferColumnsDataTypes<T>[]>`
    coalesce(
      json_agg(json_build_object(${sql.fromList(chunks)})),
      '[]'
    )
  `;
}

Thanks! I found this helpful!

Here's similar (but for sqlite) that infers with a Record<string, AnyColumn> or a Table
https://gist.github.com/Gardego5/b5e1bccdba5217f8507283b567b5605a

Hi, I noticed that sql.fromList is deprecated in newer drizzle versions, so we need to replace it with sql.join instead, as described by the docs:

-      json_agg(json_build_object(${sql.fromList(chunks)})),
+      json_agg(json_build_object(${sql.join(chunks)})),

Thank you for making this!