Incorrect type when using jsonb_agg

[timvandam]

Describe the bug A clear and concise description of what the bug is.

When using jsonb_agg safeql is suggesting an incorrect TS type.

To Reproduce Steps to reproduce the behavior:

DB setup:

await sql`
    CREATE TABLE users (
        id SERIAL PRIMARY KEY,
        public_id VARCHAR(255) UNIQUE NOT NULL,
        username VARCHAR(255) UNIQUE NOT NULL,
        created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
        updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
    );`;

await sql`
    CREATE TABLE user_authentication_methods (
        id SERIAL PRIMARY KEY,
        user_id INTEGER NOT NULL REFERENCES users (id),
        name VARCHAR(255) NOT NULL,
        -- Data depends on the authentication method
        data JSONB NOT NULL,
        created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
        updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
    );`;

await sql`
    CREATE UNIQUE INDEX user_authentication_methods_user_id_name_index
    ON user_authentication_methods (user_id, name);`;

await sql`
    CREATE FUNCTION updated_at() RETURNS TRIGGER AS $$
    BEGIN
        NEW.updated_at = NOW();
        RETURN NEW;
    END;
    $$ LANGUAGE plpgsql;`;

await sql`
    CREATE TRIGGER users_updated_at_trigger
    BEFORE UPDATE ON users
    FOR EACH ROW
    EXECUTE PROCEDURE updated_at();`;

await sql`
    CREATE TRIGGER user_authentication_methods_updated_at_trigger
    BEFORE UPDATE ON user_authentication_methods
    FOR EACH ROW
    EXECUTE PROCEDURE updated_at();`;

Example of wrong type:

await sql<{
    id: number;
    public_id: string;
    username: string;
    created_at: Date;
    updated_at: Date;
    jsonb_agg:
        | {
              id: number | null;
              user_id: number | null;
              name: string | null;
              data: unknown | null;
              created_at: Date | null;
              updated_at: Date | null;
          }[]
        | null;
}>`
    SELECT users.*, jsonb_agg(user_authentication_methods)
    FROM users
    LEFT JOIN user_authentication_methods ON users.id = user_authentication_methods.user_id
    GROUP BY users.id
`;

// Running this with some test data gives:
// 1,test,test,2024-06-10 12:31:20.240877 +00:00,2024-06-10 12:31:20.240877 +00:00,"[{""id"": 1, ""data"": {""some_data"": 123}, ""name"": ""test1"", ""user_id"": 1, ""created_at"": ""2024-06-10T12:31:38.655757+00:00"", ""updated_at"": ""2024-06-10T12:31:38.655757+00:00""}, {""id"": 2, ""data"": {""some_other_data"": 321}, ""name"": ""test2"", ""user_id"": 1, ""created_at"": ""2024-06-10T12:31:47.674517+00:00"", ""updated_at"": ""2024-06-10T12:31:51.324642+00:00""}]"

// However, when the LEFT JOIN yields no user_authentication_methods rows we get a return value that does not match the TS type for json_agg:
await sql`
    SELECT users.*, jsonb_agg(user_authentication_methods)
    FROM users
    LEFT JOIN user_authentication_methods ON FALSE
    GROUP BY users.id;
`;

// Results in:
// 1,test,test,2024-06-10 12:31:20.240877 +00:00,2024-06-10 12:31:20.240877 +00:00,[null]

// Clearly [null] does not match json_agg's type

Expected behavior A clear and concise description of what you expected to happen.

I would expect json_agg to have the type

({
    id: number;
    user_id: number;
    name: string;
    data: unknown; // usually any, but I override it in safeql.config.ts
    created_at: Date;
    updated_at: Date;
} | null)[]

Screenshots If applicable, add screenshots to help explain your problem.

Desktop (please complete the following information):

• OS: [e.g. iOS] macOS
• PostgreSQL version [e.g. 13, 14] 15.6
• Version [e.g. 22] @ts-safeql/[email protected] @ts-safeql/[email protected]

Additional context Add any other context about the problem here.

Another bonus issue I found while playing around:

await sql<{
    id: number;
    public_id: string;
    username: string;
    created_at: Date;
    updated_at: Date;
    jsonb_agg: { method: number | null }[] | null;
}>`
    SELECT users.*, jsonb_agg(json_build_object('method', user_authentication_methods))
    FROM users
    LEFT JOIN user_authentication_methods ON users.id = user_authentication_methods.user_id
    GROUP BY users.id;
`;

method is definitely not a number