SELECT
  a.name,
  json_agg(json_build_object('id', p.id, 'title', p.title)
           ORDER BY p.published_at DESC) AS posts
FROM authors a
JOIN posts p ON p.author_id = a.id
GROUP BY a.id, a.name;