Structuring Nested JSON Arrays from Joined Tables in SQLite

Understanding Column-Tile Data Aggregation in JSON Output

The core challenge involves transforming relational data from separate column and tile tables into a nested JSON structure where each column object contains an array of its associated tiles. The initial approach retrieves columns and tiles as separate arrays and attempts to merge them procedurally, resulting in a flat structure. The desired output requires hierarchical nesting: tiles must be grouped under their respective columns. This necessitates SQL-driven aggregation to avoid post-query data manipulation.

Relational databases like SQLite store data in normalized tables, which inherently lack nested structures. The mismatch between flat table rows and hierarchical JSON output creates friction. Without explicit aggregation directives, query results remain disjointed. The problem is exacerbated when columns lack tiles, requiring empty arrays instead of omitting the key. Solutions must address schema alignment, JSON construction, and handling absent relationships.

Key components of this issue include:

• Data Normalization: Columns and tiles exist as separate entities linked by idcolumn.
• JSON Nesting Requirements: Tiles must be nested within columns using SQL-generated JSON arrays.
• Empty Array Handling: Columns without tiles must explicitly include "tile": [].

Insufficient Use of JSON Aggregation Functions and Joins

The failure to achieve the desired JSON structure stems from three primary causes:

1. Disjoint Queries Without Joins
   Fetching columns and tiles in separate queries creates disconnected datasets. Without a join condition, there’s no mechanism to associate tiles with their parent columns. This forces manual merging in application code, which cannot efficiently nest tiles within columns. The absence of a shared context between the two result sets makes aggregation impossible at the query level.

2. Leveraging JSON Functions Incorrectly or Not at All
   SQLite’s JSON1 extension provides functions like json_group_array() and json_object(), which construct nested JSON structures directly in queries. Failing to use these functions results in flat arrays that require post-processing. For example, json_group_array() can aggregate tiles into a JSON array per column, while json_object() builds column objects with embedded tile arrays.

3. Improper Handling of LEFT JOINs and GROUP BY Logic
   A LEFT JOIN between column and tile ensures all columns appear in the result, even those without tiles. Without grouping rows by column.id, the query produces duplicate column entries for each associated tile. Combining LEFT JOIN with GROUP BY consolidates rows into one per column, enabling aggregation of tiles into a single JSON array.

Building Hierarchical JSON with SQLite’s JSON1 Extension and Joins

Step 1: Schema Preparation
Ensure tables are structured to support joins:

CREATE TABLE column (
  id INTEGER PRIMARY KEY,
  columntitle TEXT,
  state TEXT
);
CREATE TABLE tile (
  id INTEGER PRIMARY KEY,
  tiletitle TEXT,
  autore TEXT,
  idcolumn INTEGER REFERENCES column(id),
  message TEXT
);

Step 2: Constructing the Nested JSON Query
Use LEFT JOIN, GROUP BY, and JSON functions to aggregate tiles:

SELECT
  json_group_array(
    json_object(
      'id', c.id,
      'columntitle', c.columntitle,
      'state', c.state,
      'tile', coalesce(tile_array, json_array())
    )
  ) AS columns
FROM column c
LEFT JOIN (
  SELECT
    idcolumn,
    json_group_array(
      json_object(
        'tiletitle', t.tiletitle,
        'autore', t.autore,
        'idcolumn', t.idcolumn,
        'message', t.message
      )
    ) AS tile_array
  FROM tile t
  GROUP BY t.idcolumn
) AS t_sub ON c.id = t_sub.idcolumn;

Explanation:

• Subquery for Tile Aggregation: The subquery t_sub groups tiles by idcolumn and converts them into a JSON array using json_group_array().
• Coalescing Empty Arrays: coalesce(tile_array, json_array()) ensures columns without tiles default to an empty JSON array.
• JSON Object Construction: json_object() builds column objects with nested tile arrays.

Step 3: Integrating with Knex.js
Translate the SQL into Knex’s query builder:

exports.columnandtile = async (req, res) => {
  const nestedColumns = await knex
    .select(
      knex.raw(`
        json_group_array(
          json_object(
            'id', c.id,
            'columntitle', c.columntitle,
            'state', c.state,
            'tile', coalesce(t_sub.tile_array, json_array())
          )
        ) AS columns
      `)
    )
    .from('column as c')
    .leftJoin(
      knex
        .select(
          'idcolumn',
          knex.raw(`
            json_group_array(
              json_object(
                'tiletitle', t.tiletitle,
                'autore', t.autore,
                'idcolumn', t.idcolumn,
                'message', t.message
              )
            ) as tile_array
          `)
        )
        .from('tile as t')
        .groupBy('idcolumn')
        .as('t_sub'),
      'c.id',
      't_sub.idcolumn'
    );
  res.json({ columns: JSON.parse(nestedColumns[0].columns) });
};

Key Adjustments:

• Raw SQL Segments: Knex’s raw() method embeds JSON functions unsupported by its fluent API.
• Subquery Handling: The tile aggregation subquery is built separately and joined to column.
• JSON Parsing: json_group_array() returns a JSON string; parse it to ensure the response is a valid JavaScript object.

Step 4: Validating Edge Cases

• Empty Tile Arrays: Verify that columns without tiles include "tile": [].
• Large Datasets: Test with hundreds of columns/tiles to ensure performance remains acceptable. Indexes on tile.idcolumn improve subquery efficiency.
• Data Consistency: Ensure foreign key constraints (tile.idcolumn references column.id) are enforced to prevent orphaned tiles.

Step 5: Alternative Approaches with Views
For frequent use, define a VIEW to encapsulate the complexity:

CREATE VIEW column_with_tiles AS
SELECT
  json_object(
    'id', c.id,
    'columntitle', c.columntitle,
    'state', c.state,
    'tile', coalesce(t_sub.tile_array, json_array())
  ) AS column_json
FROM column c
LEFT JOIN (
  SELECT
    idcolumn,
    json_group_array(
      json_object(
        'tiletitle', t.tiletitle,
        'autore', t.autore,
        'idcolumn', t.idcolumn,
        'message', t.message
      )
    ) AS tile_array
  FROM tile t
  GROUP BY t.idcolumn
) AS t_sub ON c.id = t_sub.idcolumn;

Query the view and aggregate results:

SELECT json_group_array(column_json) AS columns FROM column_with_tiles;

This guide systematically addresses the root causes of flat JSON output by leveraging SQLite’s JSON functions and join strategies. By shifting data aggregation from application code to the database layer, the solution achieves efficient, maintainable, and scalable nested JSON generation.