create_product_reporting_dim_table.sql

CREATE OR REPLACE TABLE product_reporting_dim
AS
WITH RECURSIVE parent_nodes (
    node_id
  , node_natural_key
  , node_name
  , level_name
  , parent_node_id
  , is_root
  , is_leaf
  , level_number
  , node_json
  , node_json_path
    )
AS (
    -- Anchor Clause
    SELECT
        node_id
      , node_natural_key
      , node_name
      , level_name
      , parent_node_id
      , is_root
      , is_leaf
      , 1 AS level_number
      , {node_id: node_id,
         node_natural_key: node_natural_key,
         node_name: node_name,
         level_name: level_name,
         parent_node_id: parent_node_id,
         is_root: is_root,
         is_leaf: is_leaf,
         level_number: 1
        } AS node_json
      , [{node_id: node_id,
         node_natural_key: node_natural_key,
         node_name: node_name,
         level_name: level_name,
         parent_node_id: parent_node_id,
         is_root: is_root,
         is_leaf: is_leaf,
         level_number: 1
        }] AS node_json_path
       FROM product_nodes_temp
      WHERE parent_node_id IS NULL
    UNION ALL
    -- Recursive Clause
    SELECT
        nodes.node_id
      , nodes.node_natural_key
      , nodes.node_name
      , nodes.level_name
      , nodes.parent_node_id
      , nodes.is_root
      , nodes.is_leaf
      , parent_nodes.level_number + 1 AS level_number
      , {node_id: nodes.node_id,
         node_natural_key: nodes.node_natural_key,
         node_name: nodes.node_name,
         level_name: nodes.level_name,
         parent_node_id: nodes.parent_node_id,
         is_root: nodes.is_root,
         is_leaf: nodes.is_leaf,
         level_number: parent_nodes.level_number + 1
        } AS node_json
       , array_append (parent_nodes.node_json_path
                     , {node_id: nodes.node_id,
                        node_natural_key: nodes.node_natural_key,
                        node_name: nodes.node_name,
                        level_name: nodes.level_name,
                        parent_node_id: nodes.parent_node_id,
                        is_root: nodes.is_root,
                        is_leaf: nodes.is_leaf,
                        level_number: parent_nodes.level_number + 1
                       }
        ) AS node_json_path
       FROM product_nodes_temp AS nodes
          JOIN
            parent_nodes
          ON nodes.parent_node_id = parent_nodes.node_id
)
SELECT node_id
     , node_natural_key
     , node_name
     , level_name
     , parent_node_id
     , is_root
     , is_leaf
     , level_number
     -- Construct a new node_json struct value to include the sort order...
     , {node_id: node_id,
        node_natural_key: node_natural_key,
        node_name: node_name,
        level_name: level_name,
        parent_node_id: parent_node_id,
        is_root: is_root,
        is_leaf: is_leaf,
        level_number: level_number,
        node_sort_order: ROW_NUMBER () OVER (ORDER BY REPLACE (node_json_path::VARCHAR, ']', '') ASC NULLS LAST)} AS node_json
     , node_json_path
     , ROW_NUMBER () OVER (ORDER BY REPLACE (node_json_path::VARCHAR, ']', '') ASC NULLS LAST) AS node_sort_order
     -- Level 1 columns
     , node_json_path[1].node_id            AS level_1_node_id
     , node_json_path[1].node_natural_key   AS level_1_node_natural_key
     , node_json_path[1].node_name          AS level_1_node_name
     , node_json_path[1].level_name         AS level_1_level_name
     -- Level 2 columns
     , node_json_path[2].node_id            AS level_2_node_id
     , node_json_path[2].node_natural_key   AS level_2_node_natural_key
     , node_json_path[2].node_name          AS level_2_node_name
     , node_json_path[2].level_name         AS level_2_level_name
     -- Level 3 columns
     , node_json_path[3].node_id            AS level_3_node_id
     , node_json_path[3].node_natural_key   AS level_3_node_natural_key
     , node_json_path[3].node_name          AS level_3_node_name
     , node_json_path[3].level_name         AS level_3_level_name
     -- If you have more than 3 levels, copy a level section and paste here - using: node_json_path[n].x (where n is the level)
  FROM parent_nodes
ORDER BY node_sort_order ASC;