Turning SQL Query Results into JSON REST API Responses

Every REST API that uses a relational database is doing the same thing at its core: running SQL queries and turning the results into JSON. How well you do this determines your API's performance, code clarity, and maintainability.

The naive approach works at small scale and collapses under real load. Here's the full picture.

The Basic Pattern

A SQL query returns rows and columns — a tabular structure. A REST API endpoint returns JSON — typically a nested object or array. The translation happens in application code:

SELECT id, name, email, role FROM users WHERE active = true;

Returns:

id | name | email | role

----+-------+--------------------+-------

1 | Alice | [email protected] | admin

2 | Bob | [email protected] | editor

Application code maps each row to a JSON object:

[

{"id": 1, "name": "Alice", "email": "[email protected]", "role": "admin"},

{"id": 2, "name": "Bob", "email": "[email protected]", "role": "editor"}

]

This is the core loop: fetch → map → serialise. Every ORM, query builder, and raw database library does some version of this.

The N+1 Problem

The most common performance failure in SQL-to-JSON APIs. You fetch a list of posts, then for each post fetch the author separately:

SELECT * FROM posts; -- 1 query, returns 100 posts

SELECT * FROM users WHERE id = ? -- called 100 times (once per post)

That's 101 queries instead of 1. At small scale it's invisible. At 1000 requests/second it's catastrophic.

The fix: join or eager load.

SELECT posts.*, users.name AS author_name, users.email AS author_email

FROM posts

JOIN users ON posts.user_id = users.id;

One query, flat result, then reshape in application code.

PostgreSQL's Built-In JSON Functions

PostgreSQL has native JSON aggregation — you can build JSON directly in SQL:

SELECT

users.id,

users.name,

json_agg(

json_build_object(

'id', posts.id,

'title', posts.title,

'published_at', posts.published_at

)

ORDER BY posts.published_at DESC

) AS posts

FROM users

JOIN posts ON posts.user_id = users.id

GROUP BY users.id, users.name;

This returns JSON-shaped data directly from the database:

{"id": 1, "name": "Alice", "posts": [{"id": 42, "title": "Hello", "published_at": "2024-01-15"}]}

Key PostgreSQL JSON functions:

- row_to_json() — convert a single row to a JSON object

- json_agg() — aggregate rows into a JSON array

- json_build_object() — build a JSON object from key-value pairs

- jsonb_build_array() — build a JSON array

These push the serialisation work into the database, which often outperforms application-side mapping for complex aggregations.

MySQL's JSON Aggregation

MySQL 5.7+ has JSON_OBJECT() and JSON_ARRAYAGG():

SELECT

users.id,

users.name,

JSON_ARRAYAGG(

JSON_OBJECT('id', posts.id, 'title', posts.title)

) AS posts

FROM users

JOIN posts ON posts.user_id = users.id

GROUP BY users.id, users.name;

Less powerful than PostgreSQL but covers the core cases.

When to Convert Tabular Results vs Use the Database

Use database-side JSON when:

- The output shape is complex and nested

- You're aggregating across many rows with GROUP BY

- The query runs frequently and database-side performance matters

- You want to reduce the amount of data transferred to the application

Use application-side conversion when:

- The output shape is simple and flat

- You need business logic that SQL can't express (permissions filtering, computed fields)

- You're using an ORM that handles mapping for you

- The query runs infrequently or the result set is small

The Copy-Paste Debug Case

The other context where SQL-to-JSON conversion matters is debugging. You run a query in psql, pgAdmin, or DBeaver, get a tabular result, and want to share it as JSON — in a Slack message, a bug report, a Postman example, or an API documentation snippet.

psql's tabular output is not JSON:

id | name | email

----+-------+-------------------

1 | Alice | [email protected]

PostgreSQL does have pset format json in psql (since PostgreSQL 12) which outputs JSON directly. But many developers aren't aware of this, or are using tools that don't support it.

Converting the tabular output to JSON for documentation and communication purposes is a common workflow that DevConvert's SQL Results converter handles directly.

Serialisation Performance

For high-throughput APIs, JSON serialisation time is measurable. Standard approaches (Ruby's as_json, Python's json.dumps, JavaScript's JSON.stringify) handle typical objects in microseconds — fast enough for most APIs.

Where it matters:

- Large arrays (10,000+ items per response) — consider pagination

- Deeply nested objects — each nesting level adds serialisation time

- Streaming responses — for large payloads, stream the JSON rather than building it in memory first

Rails, Django, FastAPI, and Express all have serialiser abstractions (Active Model Serializers, DRF Serializers, Pydantic, etc.) that handle this at scale. The important thing is that serialisation is not an afterthought — the shape of your JSON response affects your query design, your memory usage, and your response time.