Best Practices for generating nested JSON responses with SQL and a data API

Hey all! I wanted to kick off a bit of a discussion on creating nested JSON responses for an API.

I'd like to produce a nested response to an API request. Let's imagine something like this from `/api/books/:id`

{
"id": 1,
"title": "How to make a pie",
"author": {
"id": 2,
"name": "Jim Jimson"
}
}

My main question is, what's the best way to produce this data from our relational database?

Option 1 - Query Level

We could build a single SQL query that makes this, and the API end point could just pass along the response.

SELECT
  books.id,
  books.title,
  (SELECT row_to_json(a) FROM ( 
    SELECT 
        authors.id,
        authors.name
      FROM authors
      WHERE authors.id = books.id
  ) a ) as author
  FROM books
  WHERE books.id = $1

(aside - is this the best way to nest through SQL?)

const book = await client.query(QUERY, [ id ] )
res.json(book)

Pros:

• Let's the database handle the data and does it in a single query
• Endpoint performance is fast

Cons:

• The shape of a response is kinda sorta business logic so maybe it doesn't belong at the database level?
• Typing is thrown out the window using the JSON function
• Depending on the nesting complexity, performance of query could suffer and hog a connection

Option 2 - API Level

We could keep the queries separate and simple, and assemble them in the API

SELECT 
  id,
  title,
  author_id
FROM books WHERE id = $1

SELECT
  id,
  name
FROM authors WHERE id = $1

const book = await client.query(BOOKQUERY, [ id ] )
const author = await client.query(AUTHORQUERY, [ book.author_id ])
delete book.author_id
res.json({...book, author })

Pros:

• Easy queries to maintain
• Individual queries are fast
• Better type safety

Cons:

• Endpoint performance and maintainability suffer by some amount
• May require more management of DB connections/clients, using transactions or otherwise, and async management (Promise.all, etc)

Option 3: Something else?

Any better ideas?