Lots of ways to do that, one way would be using a CTE like this one:
https://lite.datasette.io/?install=datasette-pretty-json&sql... with comment_vote_counts as (
select
comment_id,
count(*) as vote_count
from
votes
group by
comment_id
),
comments_with_vote_counts as (
select
id,
post_id,
content,
coalesce(vote_count, 0) as votes
from
comments
left join comment_vote_counts on comments.id = comment_vote_counts.comment_id
)
select
posts.id,
posts.title,
posts.content,
json_group_array(
json_object(
'id',
comments_with_vote_counts.id,
'content',
comments_with_vote_counts.content,
'votes',
comments_with_vote_counts.votes
)
) as comments
from
posts
join comments_with_vote_counts on comments_with_vote_counts.post_id = posts.id
group by posts.id
