/30-seconds-of-sql

[WIP] Inspired by - 30-seconds/30-seconds-of-python - Short SQL snippets for common SQL problems

30-seconds-of-sql

Inspired by the repo - 30-seconds/30-seconds-of-python Short SQL snippets for common SQL problems, primary DB - PostgreSQL

Problems

Extracting hierarchical objects

http://sqlfiddle.com/#!17/46596/1

Table name - Hierarchy

id (pk) parent_id (self FK) name (text) data (jsonb)
1 NULL first '{"a":1}'
2 1 second '{"b":2}'
3 2 third '{"c":3}'
4 3 fourth '{"d":4}'
WITH RECURSIVE H(id, array_tree_id, level, merged_data, parent)
    AS
    (
      SELECT id, ARRAY[id], 0, data, parent_id
      FROM Hierarchy
      WHERE parent_id is null


      UNION ALL

      SELECT C.id, ARRAY_APPEND(D.array_tree_id, C.id), D.level + 1, D.merged_data || C.data, C.parent_id
      FROM Hierarchy AS C
      INNER JOIN H AS D ON (D.id = C.parent_id)

    )
SELECT id AS id, array_tree_id as Tree, level as Level, merged_data AS Data, parent from H