sql - 使用 row_to_json 的 Postgres 递归查询

Question

我在 postgres 9.3.5 中有一个表，看起来像这样:

CREATE TABLE customer_area_node
(
  id bigserial NOT NULL,
  customer_id integer NOT NULL,
  parent_id bigint,
  name text,
  description text,

  CONSTRAINT customer_area_node_pkey PRIMARY KEY (id)
)

我查询:

WITH RECURSIVE c AS (
       SELECT *, 0 as level, name as path FROM customer_area_node WHERE customer_id = 2 and parent_id is null
       UNION ALL
       SELECT customer_area_node.*, 
       c.level + 1 as level, 
       c.path || '/' || customer_area_node.name as path
  FROM customer_area_node 
  join c ON customer_area_node.parent_id = c.id
)
SELECT * FROM c ORDER BY path;

这似乎适用于构建像 building1/floor1/room1、building1/floor1/room2 等路径。

我想做的是轻松地将其转换为表示树结构的 json，有人告诉我可以使用 row_to_json。

作为一个合理的替代方案，我可以使用任何其他方式将数据格式化为更有效的机制，这样我实际上可以轻松地将其转换为实际的树结构，而无需在/上使用大量的 string.splits。

是否有一种相当简单的方法可以使用 row_to_json 来做到这一点？

Answer 1

很抱歉回答得太晚了，但我想我找到了一个优雅的解决方案，可以成为这个问题的公认答案。

基于@pozs 发现的很棒的“小黑客”，我想出了一个解决方案:

• 用很少的代码解决了“流氓叶子”的情况(利用 NOT EXISTS 谓词)
• 避免整个关卡计算/条件的东西

WITH RECURSIVE customer_area_tree("id", "customer_id", "parent_id", "name", "description", "children") AS (
  -- tree leaves (no matching children)
  SELECT c.*, json '[]'
  FROM customer_area_node c
  WHERE NOT EXISTS(SELECT * FROM customer_area_node AS hypothetic_child WHERE hypothetic_child.parent_id = c.id)

  UNION ALL

  -- pozs's awesome "little hack"
  SELECT (parent).*, json_agg(child) AS "children"
  FROM (
    SELECT parent, child
    FROM customer_area_tree AS child
    JOIN customer_area_node parent ON parent.id = child.parent_id
  ) branch
  GROUP BY branch.parent
)
SELECT json_agg(t)
FROM customer_area_tree t
LEFT JOIN customer_area_node AS hypothetic_parent ON(hypothetic_parent.id = t.parent_id)
WHERE hypothetic_parent.id IS NULL

更新:

Tested with very simple data ，它确实有效，但正如 posz 在评论中指出的那样，with his sample data ，一些流氓叶节点被遗忘了。但是，我发现 with even more complex data ，之前的答案也不起作用，因为只有具有“最大级别”叶节点的共同祖先的流氓叶节点被捕获(当“1.2.5.8”不存在时，“1.2.4”和“1.2.5”是不存在是因为它们没有任何“最大级别”叶节点的共同祖先)。

所以这是一个新的提议，通过提取 NOT EXISTS 子请求并使其成为内部 UNION，利用 UNION，将 posz 的工作与我的工作混合在一起> 重复数据删除能力(利用 jsonb 比较能力):

<!-- language: sql -->
WITH RECURSIVE
c_with_level AS (

    SELECT *, 0 as lvl
    FROM   customer_area_node
    WHERE  parent_id IS NULL

    UNION ALL

    SELECT child.*, parent.lvl + 1
    FROM   customer_area_node child
    JOIN   c_with_level parent ON parent.id = child.parent_id
),
maxlvl AS (
  SELECT max(lvl) maxlvl FROM c_with_level
),
c_tree AS (
    SELECT c_with_level.*, jsonb '[]' children
    FROM   c_with_level, maxlvl
    WHERE  lvl = maxlvl

    UNION 
    (
        SELECT (branch_parent).*, jsonb_agg(branch_child)
        FROM (
            SELECT branch_parent, branch_child
            FROM c_with_level branch_parent
            JOIN c_tree branch_child ON branch_child.parent_id = branch_parent.id
        ) branch
        GROUP BY branch.branch_parent

        UNION

        SELECT c.*, jsonb '[]' children
        FROM   c_with_level c
        WHERE  NOT EXISTS (SELECT 1 FROM c_with_level hypothetical_child WHERE hypothetical_child.parent_id = c.id)
    )
)
SELECT jsonb_pretty(row_to_json(c_tree)::jsonb)
FROM c_tree
WHERE lvl = 0;

测试于 http://rextester.com/SMM38494 ;)