✏️ Explanatory Question

JSON aggregation — build nested, API-ready JSON directly from SQL

👁 6 Views
📘 Detailed Answer
🟢 Easy
← Previous JSON_TABLE — convert a JSON array into relational rows
💡

Answer with Explanation

123

JSON aggregation — build nested, API-ready JSON directly from SQL

Level: Hard — Lets you return ready-to-serve nested JSON from the database, avoiding the N+1 problem in APIs.

Scenario: Your API must return each department with a nested array of its employees as JSON. The naive approach fires one query for departments, then one per department for employees (the N+1 problem). Can you build the entire nested JSON structure in a single query so the API just serves the result?

The JSON-Building Functions

  • JSON_OBJECT(k, v, ...): Builds a JSON object from key/value pairs.
  • JSON_ARRAY(v, ...): Builds a JSON array from values.
  • JSON_ARRAYAGG(expr): Aggregates rows in a group into a JSON array.
  • JSON_OBJECTAGG(k, v): Aggregates rows into a JSON object (key-value map).

Sample Data

departments

dept_iddept_name
10Engineering
20Sales

employees

emp_idnamedept_id
1Rumman10
2Krushna10
3Manjula20

Building a Simple JSON Object per Row

-- Wrap each employee row into a JSON object
SELECT JSON_OBJECT('id', emp_id, 'name', name) AS emp_json
FROM employees;
-- {"id": 1, "name": "Rumman"}
-- {"id": 2, "name": "Krushna"} ...

The Goal — Departments with Nested Employee Arrays

SELECT
    d.dept_name,
    JSON_ARRAYAGG(
        JSON_OBJECT('id', e.emp_id, 'name', e.name)
    ) AS employees
FROM departments d
JOIN employees e ON d.dept_id = e.dept_id
GROUP BY d.dept_id, d.dept_name;
dept_nameemployees (JSON)
Engineering[{"id":1,"name":"Rumman"},{"id":2,"name":"Krushna"}]
Sales[{"id":3,"name":"Manjula"}]

One query returns the complete nested structure — no N+1, no app-side stitching.

Why this is powerful: JSON_ARRAYAGG(JSON_OBJECT(...)) collapses the child rows of each group into a single JSON array — exactly the shape a REST/GraphQL API needs. The database does the nesting in one pass instead of the app making N extra queries.

Fully Nested Document (whole result as one JSON)

-- Build a single JSON array of departments, each with its employees
SELECT JSON_ARRAYAGG(
    JSON_OBJECT(
        'department', d.dept_name,
        'employees', (
            SELECT JSON_ARRAYAGG(JSON_OBJECT('id', e.emp_id, 'name', e.name))
            FROM employees e WHERE e.dept_id = d.dept_id
        )
    )
) AS result
FROM departments d;
-- [{"department":"Engineering","employees":[...]}, {"department":"Sales","employees":[...]}]

JSON_OBJECTAGG — Key/Value Map

-- Build a lookup object: { "Engineering": 2, "Sales": 1 }  (headcount map)
SELECT JSON_OBJECTAGG(dept_name, cnt) AS headcount_map
FROM (
    SELECT d.dept_name, COUNT(e.emp_id) AS cnt
    FROM departments d LEFT JOIN employees e ON d.dept_id = e.dept_id
    GROUP BY d.dept_id, d.dept_name
) t;

Caveats: JSON_ARRAYAGG does not guarantee element order unless you control it (wrap the source in an ordered subquery). Also watch the group_concat_max_len / max_allowed_packet limits — very large aggregated JSON can be truncated. And duplicate keys in JSON_OBJECTAGG cause an error.

Interviewer follow-up: "Should you build API JSON in the database or the application layer?" → For read-heavy endpoints where it eliminates N+1 round-trips, doing it in SQL is a genuine win. But it couples your DB to your API shape and can strain the DB on huge payloads. A balanced approach: use it for well-bounded nested reads, keep transformation logic in the app for complex/large responses.

← Previous JSON_TABLE — convert a JSON array into relational rows