Display an array in a readable/hierarchical format

sql

data-hierarchy

sql-functions

data-formatting

byNikita Barsukov·Nov 22, 2024

To adequately represent an array in an organized, hierarchical style, use a recursive common table expression (CTE) in SQL by continually self-joining the table. Child elements are indented with the REPEAT or SPACE function to indicate varying depth levels. Here, let's consider a category table with id, name, and parent_id fields:

WITH RECURSIVE category_tree AS (
  SELECT id, name, CAST('' AS VARCHAR(255)) AS indentation, parent_id
  FROM category WHERE parent_id IS NULL -- The roots have no parents 😢
  UNION ALL
  SELECT c.id, c.name, ct.indentation || '  ', c.parent_id
  FROM category c JOIN category_tree ct ON c.parent_id = ct.id -- The branches seeking their parents 🌿
)
SELECT indentation || name AS display_name FROM category_tree ORDER BY indentation;

Understand that the indentation accumulates pairs of spaces for each additional level of the tree, producing visually consistent formatting that marries hierarchy with aesthetics.

Get to grips with complex data hierarchies

Working with complex data structures, especially those in JSON or XML formats, often requires you to parse, decipher, and translate the data into a more user-friendly format. This process should maintain the original hierarchy to retain all essential relationships. SQL's XML and JSON functions come in handy when fetching, restructuring, and presenting the nested structures effectively.

Array formatting techniques in SQL

Giving JSON data a makeover

In the case of JSON arrays, Postgres' jsonb_pretty function can be your go-to toolbox for creating a well-structured, easy-to-read string representation of the JSON value:

SELECT jsonb_pretty(your_json_column) FROM your_table;

Molding XML data into trees

For XML data, the XMLTABLE function can morph the data into a tabular image, and you can then use XQuery to offer a user-friendly, navigable, hierarchical depiction:

SELECT *
FROM XMLTABLE('/root/branch' PASSING your_xml_column -- Passing the XML love letter 💌
  COLUMNS branch_name VARCHAR PATH './name');

By adapting these queries to your needs, you can generate output that makes nested arrays or complex data structures more approachable.

Advanced readability techniques in SQL

Spacing for grace

You can breathe life into your SQL output by using whitespace indentation. A seemingly humdrum result set can morph into a visually appealing, hierarchical presentation reminiscent of a tree:

SELECT REPEAT('  ', level) || name AS indented_name
FROM (SELECT name, (your_logic_to_determine_level) AS level FROM your_table) AS subquery;

Highlight key findings with conditionals

Conditional SQL constructs like CASE are fantastic for emphasizing specific data points, which helps you spot important needles in the haystack:

SELECT 
  CASE WHEN important_condition THEN '>>' || name ELSE name END AS highlighted_name
FROM your_table
ORDER BY level_of_importance;

Old dogs can learn new functions

Sometimes, SQL stops short of answering specific needs, forcing us to customize our own functions to format the output to our unique requests. Welcome to the world of elegant and understandable SQL presentations!

explain-codes / Sql / Display an array in a readable/hierarchical format

Linked

How can I import a JSON file into PostgreSQL?



Index for finding an element in a JSON array



How to query a json column for empty objects?



Best JSON-LD practices: using multiple <script> elements?



Why can't Python parse this JSON data?

