postgres jsonb返回node的任何后代

WITH RECURSIVE descendants AS (
  SELECT id, data
  FROM your_table
  WHERE id = 'root' -- 根节点的ID
  UNION ALL
  SELECT child.id, child.data
  FROM your_table child
  JOIN descendants parent ON child.data ->> 'parent_id' = parent.id -- parent_id是指向父节点的字段名
)
SELECT *
FROM descendants;

CREATE OR REPLACE FUNCTION get_descendants(node jsonb)
  RETURNS TABLE (id text, data jsonb) AS
$$
BEGIN
  RETURN QUERY
  SELECT id, data
  FROM your_table
  WHERE data ->> 'parent_id' = node->>'id'; -- parent_id是指向父节点的字段名
  FOR row IN
    SELECT id, data
    FROM your_table
    WHERE data ->> 'parent_id' = node->>'id' -- parent_id是指向父节点的字段名
  LOOP
    RETURN QUERY SELECT * FROM get_descendants(row.data);
  END LOOP;
END;
$$
LANGUAGE plpgsql;