使用 row_to_json 进行 Postgres 递归查询 [英] Postgres recursive query with row_to_json

问题描述

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

I've got a table in postgres 9.3.5 that looks like this:

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 等路径.

this seems to work to build paths like building1/floor1/room1, building1/floor1/room2, etc.

我希望能够轻松地将其转换为表示树结构的 json，有人告诉我我可以使用 row_to_json 来实现.

What I'd like to be able to do is easily turn that into either json that represents the tree structure which I've been told I can do with row_to_json.

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

As a reasonable alternative, any other way I can format the data to a more efficient mechanism such that I can actually easily turn it into an actual tree structure without having a ton of string.splits on /.

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

Is there a reasonably easy way to do this with row_to_json?

推荐答案

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

Sorry for the very late answer but i think i found an elegant solution that could become an accepted answer for this question.

基于@pozs 发现的令人敬畏的小技巧"，我想出了一个解决方案:

Based on the awesome "little hack" found by @pozs, i came up with a solution that:

• 用很少的代码解决流氓叶子"的情况(利用 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

更新:

使用非常简单的数据进行测试，确实有效，但正如 posz 在评论中指出的那样，他的样本数据，一些流氓叶节点被遗忘了.但是，我发现使用更复杂的数据，之前的答案也不起作用，因为只有流氓叶节点捕获具有最大级别"叶节点的共同祖先(当1.2.5.8"不存在时，1.2.4"和1.2.5"不存在，因为它们没有具有任何最大级别"叶节点的共同祖先节点).

Tested with very simple data, it does work, but as posz pointed out in a comment, with his sample data, some rogue leaf nodes are forgotten. But, i found out that with even more complex data, the previous answer is not working either, because only rogue leaf nodes having a common ancestor with "max level" leaf nodes are caught (when "1.2.5.8" is not there, "1.2.4" and "1.2.5" are absent because they have no common ancestor with any "max level" leaf node).

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

So here is a new proposition, mixing posz's work with mine by extracting the NOT EXISTS subrequest and making it an internal UNION, leveraging UNION de-duplication abilities (leveraging jsonb comparison abilities):

<!-- 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 上测试；)

这篇关于使用 row_to_json 进行 Postgres 递归查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！