[pg] How do I make subquery in select()?

I wanna write a query like this in drizzle, but it doesn't seem like working

SELECT
    books.*,
    (
        SELECT jsonb_build_object('genres', json_agg(jsonb_build_object(
            'id', genres.id,
            'name', genres.name,
            -- Add other columns as needed
        )))
        FROM genres
        JOIN toons_genres ON genres.id = toons_genres.genre_id
        WHERE toons_genres.toon_id = toons.id
    ) AS matched_genres
Was this page helpful?