import type { SQL, InferColumnsDataTypes } from "drizzle-orm";
import type { PgColumn } from "drizzle-orm/pg-core";
import { sql, DrizzleError } from "drizzle-orm";

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

    if (!entries.length) {
        throw new DrizzleError('Cannot aggregate an empty object');
    }

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

    const filterChunks = entries.filter(([_, col]) => !col.notNull).map(([_, col]) => col);
    const filterSql = filterChunks.length ? sql`FILTER (WHERE ${sql.join(filterChunks)} IS NOT NULL)` : "";

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

import type { SQL, InferColumnsDataTypes } from "drizzle-orm";
import type { PgColumn } from "drizzle-orm/pg-core";
import { sql, DrizzleError } from "drizzle-orm";

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

    if (!entries.length) {
        throw new DrizzleError('Cannot aggregate an empty object');
    }

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

    const filterChunks = entries.filter(([_, col]) => !col.notNull).map(([_, col]) => col);
    const filterSql = filterChunks.length ? sql`FILTER (WHERE ${sql.join(filterChunks)} IS NOT NULL)` : "";

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

      COALESCE(
        json_agg(json_build_object('id', g.id, 'name', g.name)),
        '[]'::json
      )

      COALESCE(
        json_agg(json_build_object('id', g.id, 'name', g.name)),
        '[]'::json
      )

const res: {
    id: number;
    name: string;
    verified: boolean;
    invitedBy: number | null;
    fullName: string;
    group: {
        id: number;
        name: string;
        description: string | null;
    }[];
}[];

const res: {
    id: number;
    name: string;
    verified: boolean;
    invitedBy: number | null;
    fullName: string;
    group: {
        id: number;
        name: string;
        description: string | null;
    }[];
}[];