Generate nested JSON from hierarchical SQL data using recursive common table expressions

Data and Databases
1

I’m working with a database table that has a hierarchical structure where records reference their parent records. My goal is to create a single nested JSON output using MariaDB 11.4. I’m not experienced with recursive CTEs and need help building a query that can transform my flat table data into a properly nested JSON structure.

Here’s an example of what I want to achieve:

{
  "org_id": 1,
  "department_name": "headquarters",
  "has_subdepts": 1,
  "subdepartments": [
    {
      "org_id": 2,
      "department_name": "operations",
      "has_subdepts": 1,
      "subdepartments": [
        {
          "org_id": 3,
          "department_name": "production",
          "has_subdepts": 0,
          "subdepartments": []
        },
        {
          "org_id": 4,
          "department_name": "quality_control",
          "has_subdepts": 0,
          "subdepartments": []
        }
      ]
    },
    {
      "org_id": 5,
      "department_name": "administration",
      "has_subdepts": 1,
      "subdepartments": [
        {
          "org_id": 6,
          "department_name": "human_resources",
          "has_subdepts": 0,
          "subdepartments": []
        }
      ]
    }
  ]
}

I need a recursive CTE approach that can handle multiple levels of nesting and output everything as one JSON object.

2

I’ve done similar hierarchical JSON stuff in MariaDB. Two-step approach works best: build a recursive CTE to traverse the hierarchy and calculate depths, then use JSON_OBJECT with conditional JSON_ARRAYAGG for nesting. The trick with subdepartments arrays is using CASE statements so empty ones show as instead of null. Throw in a level counter in your CTE - helps with debugging and can boost performance. Watch out for ordering within each hierarchy level. I got burned on this - make sure you’ve got ORDER BY clauses in your aggregation functions or your output structure gets inconsistent.

3

mariadb’s json functions are perfect for this! use with recursive for the cte, then wrap everything in json_object() and json_arrayagg(). the tricky bit is building the hierarchy right - start from root nodes where parent_id is null, then recursively join on parent relationships. make sure u handle the base case and recursive case separately in your cte.

4

that’s interesting! have you tried json_objectagg with your recursive cte? what’s your table schema like - do you have a parent_id column? also, how deep does your hierarchy usually go? performance might take a hit with really nested structures.