Issue Overview: Struggling to Build Hierarchical JSON with Proper Nesting and Escaped Characters

The core challenge revolves around transforming tabular data into a nested JSON array structure using SQLite’s JSON functions. The input data consists of menu items grouped by menu_id, each containing multiple commands ordered by command_order. The desired JSON output requires two levels of nesting:

1. Top-Level Menu Objects: Each object key is menu_label, with its value being an array of command objects. These top-level objects must be ordered by menu_order.
2. Command Arrays: Within each menu object, commands must appear as an array of objects with label as the key and command as the value, ordered by command_order.

The initial attempt to construct this JSON resulted in improperly escaped strings (e.g., "[{\"New\":\"do_new\"}]") instead of valid nested JSON arrays. This occurs because intermediate JSON fragments lose their "JSON subtype" during aggregation, causing SQLite to treat them as plain text strings. The solution involves explicitly casting these fragments back to JSON using the json() function to preserve structure and prevent escaping.

Possible Causes: JSON Subtype Loss During Aggregation and Misordered Grouping

Three primary factors contribute to the issue of escaped JSON strings and incorrect nesting:

1. Intermediate JSON Subtype Loss:
   When using json_group_array() or json_object() in subqueries or CTEs, SQLite may fail to retain the JSON subtype of the generated fragments. This occurs because the JSON functions in SQLite dynamically allocate memory for JSON objects/arrays, and when these are stored in temporary tables (as in CTEs), they revert to their textual representation. Subsequent operations then treat them as strings, leading to escaped quotes.

2. Insufficient Nesting of JSON Functions:
   Failing to wrap aggregated JSON fragments with json() after grouping operations forces SQLite to interpret them as string literals. For example, json_group_array(json_object(label, command)) produces a valid JSON array, but when this array is inserted into another JSON object without json(), it becomes a string:

   json_object(menu_label, x) -- x is a JSON array stored as text
   

   This results in escaped characters.

3. Misordering During Grouping:
   Incorrect use of ORDER BY clauses within aggregation steps can disrupt the hierarchical ordering required for menus and commands. The menu_order and command_order columns dictate the sequence of top-level menus and their commands, respectively. If ordering is not enforced at both the command aggregation and menu aggregation stages, the final JSON will not reflect the intended structure.

Troubleshooting Steps, Solutions & Fixes: Preserving JSON Subtypes and Structuring Aggregations

Step 1: Validate Input Data Ordering

Before constructing JSON, ensure the raw data is ordered correctly. Use explicit ORDER BY clauses in CTEs to enforce the desired sequence for menus and commands:

WITH ordered_commands AS (
  SELECT 
    menu_id, 
    menu_order, 
    menu_label, 
    command_order, 
    label, 
    command 
  FROM data 
  ORDER BY menu_order, command_order
)

This guarantees that menus appear in menu_order sequence, and commands within each menu follow command_order.

Step 2: Aggregate Commands into JSON Arrays

Group commands by menu_id, menu_order, and menu_label, converting each command into a JSON object and aggregating them into an array:

, aggregated_commands AS (
  SELECT 
    menu_id, 
    menu_order, 
    menu_label, 
    json_group_array(
      json_object(label, command)
    ) AS commands_json 
  FROM ordered_commands 
  GROUP BY menu_id, menu_order, menu_label 
)

At this stage, commands_json contains valid JSON arrays like [{"New":"do_new"}, ...], but SQLite treats them as text.

Step 3: Recast JSON Strings to JSON Subtype

Apply json() to commands_json to restore the JSON subtype and prevent escaping when embedding it into outer JSON objects:

, recast_commands AS (
  SELECT 
    menu_order, 
    menu_label, 
    json(commands_json) AS commands_json 
  FROM aggregated_commands 
)

The json() function parses the textual JSON array and marks it as a JSON subtype, ensuring it is treated as structured data in subsequent operations.

Step 4: Assemble Top-Level Menu Objects

Construct the top-level menu objects by combining menu_label with the recast commands_json:

, menu_objects AS (
  SELECT 
    json_object(
      menu_label, 
      commands_json
    ) AS menu_json 
  FROM recast_commands 
  ORDER BY menu_order
)

Using json_object() here with the recast commands_json avoids escaping because both the key (menu_label) and value (commands_json) are valid JSON components.

Step 5: Aggregate Menu Objects into Final JSON Array

Use json_group_array() to combine all menu objects into a single JSON array, ensuring the final output maintains the correct order:

SELECT json_group_array(menu_json) AS final_json 
FROM menu_objects;

This produces the desired output without escaped characters:

[
  { "File": [ {"New": "do_new" }, {"Open":  "do_open" } ] }, 
  { "Edit": [ {"Copy": "do_copy"}, {"Paste": "do_paste" } ] }
]

Comprehensive Solution Query

Combining all steps into a single query:

WITH ordered_commands AS (
  SELECT * 
  FROM data 
  ORDER BY menu_order, command_order
),
aggregated_commands AS (
  SELECT 
    menu_id, 
    menu_order, 
    menu_label, 
    json_group_array(json_object(label, command)) AS commands_json 
  FROM ordered_commands 
  GROUP BY menu_id, menu_order, menu_label
),
recast_commands AS (
  SELECT 
    menu_order, 
    menu_label, 
    json(commands_json) AS commands_json 
  FROM aggregated_commands
),
menu_objects AS (
  SELECT 
    json_object(menu_label, commands_json) AS menu_json 
  FROM recast_commands 
  ORDER BY menu_order
)
SELECT json_group_array(menu_json) AS final_json 
FROM menu_objects;

Key Fixes and Rationale

1. Explicit Ordering:
   The ORDER BY clauses in ordered_commands and menu_objects ensure that both menus and commands appear in the correct sequence. Without this, the aggregation functions might not respect the intended order, as SQLite does not guarantee row order without explicit directives.

2. JSON Subtype Preservation:
   The json() function in recast_commands is critical. It converts the textual JSON array produced by json_group_array() into a recognized JSON subtype. This prevents SQLite from escaping the JSON when it is embedded into another JSON object.

3. Layered Aggregation:
   By breaking the problem into CTEs—ordering, command aggregation, JSON recasting, and menu object creation—the query maintains clarity and ensures each transformation step is validated before proceeding.

Debugging Escaped JSON Issues

If the final JSON still contains escaped strings, inspect intermediate results using SELECT statements in CTEs:

-- Check output of aggregated_commands
SELECT * FROM aggregated_commands;  
-- Verify commands_json is a valid JSON array string

-- Check output of recast_commands
SELECT typeof(commands_json) FROM recast_commands;  
-- Should return 'json' 

If typeof(commands_json) returns text, the json() function was not applied correctly.

Alternative Approach: Direct Nesting Without CTEs

For simpler cases, the query can be condensed by nesting functions directly, though this sacrifices readability:

SELECT json_group_array(
  json_object(
    menu_label, 
    json(
      (SELECT json_group_array(json_object(label, command)) 
       FROM data AS d2 
       WHERE d2.menu_id = d1.menu_id 
       ORDER BY command_order)
    )
  )
) AS final_json 
FROM (SELECT DISTINCT menu_id, menu_order, menu_label FROM data) AS d1 
ORDER BY menu_order;

This uses a correlated subquery to aggregate commands per menu, but it may incur performance overhead with large datasets.

Performance Considerations

• Indexing: Add indexes on menu_order, menu_id, and command_order to speed up ordering and grouping:

  CREATE INDEX idx_data_menu ON data(menu_order, menu_id);
  CREATE INDEX idx_data_command ON data(menu_id, command_order);
  

• Materialized CTEs: SQLite materializes CTEs by default, which can buffer intermediate results and improve performance.

Conclusion

The solution hinges on understanding how SQLite manages JSON subtypes during aggregation and the necessity of explicit recasting. By enforcing order at each stage and using json() to preserve JSON structure, developers can generate complex nested JSON outputs directly from SQL queries, avoiding escaped strings and ensuring valid syntax.