Teradata how to use recursion to build JSON hierarchy

Question

What is the equivalent Teradata syntax to answer the same question asked about reverse aggregation inside of common table expression found at Reverse aggregation inside of common table expression?

I am trying to hack a Teradata syntax version to iterate over a parent child relationship table and build JSON which places parent of child who is parent to child who is parent to child etc in the one JSON field.

This is the answer given in the question from the hyperlink listed above which I think is written for PostgreSQL. I would really appreciate assistance in translating this to TD as I think this answer should allow me to accomplish my intended task. If not please set me straight.

I am not sure what row_to_json(c) is calling should this be JSON_AGG(c.children)? and I think that the double colon (NULL::JSON) is casting a null to a JSON data type? In any case I have tried a few variations to no avail. Please help.

Here is the PostgreSQL syntax answer given:

WITH RECURSIVE cte AS (
   SELECT id, parent_id, name, NULL::JSON AS children
   FROM   people p
   WHERE  NOT EXISTS (  -- only leaf nodes; see link below
      SELECT 1 FROM people
      WHERE  parent_id = p.id
      )
   UNION ALL
   SELECT p.id, p.parent_id, p.name, row_to_json(c) AS children
   FROM   cte c
   JOIN   people p ON p.id = c.parent_id
   )
SELECT id, name, json_agg(children) AS children
FROM   cte
GROUP  BY 1, 2;

`row_to_json(c)` is similar to `JSON_COMPOSE(c.id,c.parent_id,c.name, c.children)` in Teradata, i.e. you must list every column. — dnoeth, Feb 24 '16 at 08:39
Thanks @dnoeth I think where I am coming unstuck is when there are multiple children of a parent it doesn't know which row/child to place in the JSON_COMPOSE (row_to_json), where as I need it to place all the children inside and then continue on with the recursion so that parent then becomes a child and places all of its JSON_COMPOSE results inside the parents and so on. Perhaps I need to repeat the recursion for n levels of the depth. — A.Jehu, Feb 25 '16 at 00:52

score 1 · Accepted Answer · answered Feb 25 '16 at 16:21

When translating the PostgreSQL to Teradata I encountered a restriction, JSON columns are not supported by set operations like UNION.

Casting JSON/VarChar back and forth is a workaround:

CREATE VOLATILE TABLE people (id INT, name VARCHAR(20), parent_id INT) ON COMMIT PRESERVE ROWS;

INSERT INTO people VALUES(1, 'Adam', NULL);
INSERT INTO people VALUES(2, 'Abel',  1);
INSERT INTO people VALUES(3, 'Cain',  1);
INSERT INTO people VALUES(4, 'Enoch', 3);

WITH RECURSIVE cte AS (
   SELECT id, parent_id, name,
      CAST(NULL AS VARCHAR(2000)) AS children
   FROM   people p
   WHERE  NOT EXISTS (
      SELECT * FROM people
      WHERE  parent_id = p.id
      )
   UNION ALL
   SELECT p.id, p.parent_id, p.name,
      -- VarChar -> JSON -> VarChar
      CAST(JSON_COMPOSE(c.id, 
                        c.name,
                        NEW JSON(c.children) AS children) AS VARCHAR(10000)) AS children
   FROM   cte c                                       
   JOIN   people p ON p.id = c.parent_id
   )
SELECT id, name,
   JSON_AGG(NEW JSON(children) AS children) AS children
FROM cte
GROUP  BY 1, 2;

The result is similar, but not exactly the same, Teradata adds "children":, e.g:

{"children":{"id":4,"name":"Enoch","children":null}} -- Teradata
           [{"id":4,"name":"Enoch","children":null}] -- PostgreSQL

Finally adding JSONExtract to get the array only:

SELECT id, name,
     JSON_AGG(NEW JSON(children) AS X).JSONExtract('$..X') AS children
FROM cte
GROUP  BY 1, 2;

            [{"id":4,"name":"Enoch","children":null}]

This answers exactly. I will work on size limits that I am facing next and will post solution if I can get it to work with the size of my population which is very large (20k ids). — A.Jehu, Feb 26 '16 at 03:56

Teradata how to use recursion to build JSON hierarchy

1 Answers1