邻接表转换为带Postgres的JSON图

Question

邻接表转换为带Postgres的JSON图

11

我有一个标签表的以下模式：

CREATE TABLE tags (
    id integer NOT NULL,
    name character varying(255) NOT NULL,
    parent_id integer
);

我需要构建一个查询，以返回以下结构（这里使用YAML表示以提高可读性）：

- name: Ciencia
  parent_id: 
  id: 7
  children:
  - name: Química
    parent_id: 7
    id: 9
    children: []
  - name: Biología
    parent_id: 7
    id: 8
    children:
    - name: Botánica
      parent_id: 8
      id: 19
      children: []
    - name: Etología
      parent_id: 8
      id: 18
      children: []

经过一些试错和在 SO 上寻找类似的问题，我得出了这个查询：

    WITH RECURSIVE tagtree AS (
      SELECT tags.name, tags.parent_id, tags.id, json '[]' children
      FROM tags
      WHERE NOT EXISTS (SELECT 1 FROM tags tt WHERE tt.parent_id = tags.id)

      UNION ALL

      SELECT (tags).name, (tags).parent_id, (tags).id, array_to_json(array_agg(tagtree)) children FROM (
        SELECT tags, tagtree
        FROM tagtree
        JOIN tags ON tagtree.parent_id = tags.id
      ) v
      GROUP BY v.tags
    )

    SELECT array_to_json(array_agg(tagtree)) json
    FROM tagtree
    WHERE parent_id IS NULL

但是将其转换为yaml格式后，会得到以下结果：

- name: Ciencia
  parent_id: 
  id: 7
  children:
  - name: Química
    parent_id: 7
    id: 9
    children: []
- name: Ciencia
  parent_id: 
  id: 7
  children:
  - name: Biología
    parent_id: 7
    id: 8
    children:
    - name: Botánica
      parent_id: 8
      id: 19
      children: []
    - name: Etología
      parent_id: 8
      id: 18
      children: []

根节点被复制了。我可以在我的应用程序代码中合并结果，但我感觉我很接近成功，而且可以完全从PG中完成。

这里是一个带有SQL Fiddle示例的链接： http://sqlfiddle.com/#!15/1846e/1/0

期望输出为： https://gist.github.com/maca/e7002eb10f36fcdbc51b

实际输出为： https://gist.github.com/maca/78e84fb7c05ff23f07f4

- Macario

您能够发布一些样本记录吗？ - mlinth

我发布了一个带有SQL Fiddle示例和期望输出以及实际输出的gist。 - Macario

1

感谢提供这样优秀结构化的问题。我尝试了一下，但是无法解决 :-( 不过，这里有一个链接或许可以帮助你更进一步... http://bender.io/2013/09/22/Returning-Hierarchical-Data-in-a-Single-SQL-Query/ - mlinth

谢谢分享链接！我会去看一下。看起来使用Postgres生成复杂图表的JSON不是一个好方法。在应用程序端更容易维护。但我仍然有兴趣探究如何解决这个问题。 - Macario

3个回答

2

尝试使用PL/Python和networkx。

不可否认，以下内容并不完全符合所请求的JSON格式，但是信息似乎都在那里，如果可以接受PL/Python，这可能会成为一个完整的答案。

CREATE OR REPLACE FUNCTION get_adjacency_data(
    names text[],
    ids integer[],
    parent_ids integer[])
  RETURNS jsonb AS
$BODY$

    pairs = zip(ids, parent_ids)

    import networkx as nx
    import json
    from networkx.readwrite import json_graph

    name_dict = dict(zip(ids, names))

    G=nx.DiGraph()
    G.add_nodes_from(ids)
    nx.set_node_attributes(G, 'name', name_dict)
    G.add_edges_from(pairs)
    return json.dumps(json_graph.adjacency_data(G))

$BODY$ LANGUAGE plpythonu;

WITH raw_data AS (
    SELECT array_agg(name) AS names,
        array_agg(parent_id) AS parent_ids,
        array_agg(id) AS ids
    FROM tags
    WHERE parent_id IS NOT NULL)
SELECT get_adjacency_data(names, parent_ids, ids)
FROM raw_data;

- Ian Gow

2

我曾经也在寻找同样的解决方案，也许这个例子对任何人都有用。

在具有相同结构的表格上测试过Postgres 10。

表格包含id、name和pid作为parent_id的列。

最初的回答：


create or replace function get_c_tree(p_parent int8) returns setof jsonb as $$

  select
    case 
      when count(x) > 0 then jsonb_build_object('id', c.id, 'name', c.name,  'children', jsonb_agg(f.x))
      else jsonb_build_object('id', c.id, 'name', c.name, 'children', null)
    end
  from company c left join get_c_tree(c.id) as f(x) on true
  where c.pid = p_parent or (p_parent is null and c.pid is null)
  group by c.id, c.name;

$$ language sql;


select jsonb_agg(get_c_tree) from get_c_tree(null::int8);

- Pavel Tsvetkov

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Badri · Accepted Answer

这是一个使用PLV8为您的模式提供解决方案。

首先，使用PLSQL函数和递归CTE构建一个物化路径。

CREATE OR REPLACE FUNCTION get_children(tag_id integer)
RETURNS json AS $$
DECLARE
result json;
BEGIN
SELECT array_to_json(array_agg(row_to_json(t))) INTO result
    FROM (
WITH RECURSIVE tree AS (
  SELECT id, name, ARRAY[]::INTEGER[] AS ancestors
  FROM tags WHERE parent_id IS NULL
 
  UNION ALL
 
  SELECT tags.id, tags.name, tree.ancestors || tags.parent_id
  FROM tags, tree
  WHERE tags.parent_id = tree.id
) SELECT id, name, ARRAY[]::INTEGER[] AS children FROM tree WHERE $1 = tree.ancestors[array_upper(tree.ancestors,1)]
) t;
RETURN result;
END;
$$ LANGUAGE plpgsql;

然后，根据上述函数的输出构建树。

CREATE OR REPLACE FUNCTION get_tree(data json) RETURNS json AS $$

var root = [];

for(var i in data) {
  build_tree(data[i]['id'], data[i]['name'], data[i]['children']);
}

function build_tree(id, name, children) {
  var exists = getObject(root, id);
  if(exists) {
       exists['children'] = children;
  }
  else {
    root.push({'id': id, 'name': name, 'children': children});
  }
}


function getObject(theObject, id) {
    var result = null;
    if(theObject instanceof Array) {
        for(var i = 0; i < theObject.length; i++) {
            result = getObject(theObject[i], id);
            if (result) {
                break;
            }   
        }
    }
    else
    {
        for(var prop in theObject) {
            if(prop == 'id') {
                if(theObject[prop] === id) {
                    return theObject;
                }
            }
            if(theObject[prop] instanceof Object || theObject[prop] instanceof Array) {
                result = getObject(theObject[prop], id);
                if (result) {
                    break;
                }
            } 
        }
    }
    return result;
}

    return JSON.stringify(root);
$$ LANGUAGE plv8 IMMUTABLE STRICT;

这将产生你问题中提到的所需的JSON数据。希望能帮到你。

我在这里写了一篇详细的帖子/解析，解释了这个解决方案的工作原理。