json_test.sql

-- Setup
create table json_test(id int,tree json);
insert into json_test values(16605,'{ "id": 0, "children": [{ "id": 965, "children": [{ "id": 967 }, { "id": 969 }, { "id": 971 }, { "id": 973 } ] }, { "id": 974, "children": [{ "id": 976 }, { "id": 978 } ] } ] }');

--1----------------------------------------------------------------------
WITH RECURSIVE rec (id,json_element) as
(
  SELECT tree -> 'id', tree -> 'children' from json_test where (tree -> 'id')::text::int = 0
  UNION all
  SELECT json_element -> 'id', json_element -> 'children' from rec) 
select * from rec;

--2----------------------------------------------------------------------
with recursive cte(id,json_element) as (
select tree->'id',tree->'children' from json_test
union
select json_element->'id',json_element->'children' from cte
) select * from cte;
--could not identify an equality operator for type json

--3----------------------------------------------------------------------
WITH RECURSIVE cte(id, children) AS (
  SELECT
    tree->'id' as id,
    tree->'children' as children
  FROM json_test
  UNION ALL
  SELECT
    children -> 'id',
    children -> 'children'
  FROM cte,
    --json_each(CASE WHEN json_typeof(cte.children) <> 'object' THEN '{}' :: JSON ELSE cte.children END) AS t
    json_each(CASE WHEN json_typeof(cte.children) = 'array' THEN json_array_elements(cte.children) ELSE cte.children END) AS t
)
SELECT * FROM cte WHERE json_typeof(cte.children) <> 'object';
 --ERROR: set-returning functions are not allowed in CASE
  --Hint: You might be able to move the set-returning function into a LATERAL FROM item.
  --Position: 393
    --json_each(CASE WHEN json_typeof(cte.children) = 'array' THEN json_array_elements(cte.children) ELSE --cte.children END) AS t