json - 使用Postgres将列表邻接表转换为JSON图

标签表具有以下架构:

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

最佳答案

这是对方案使用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。希望能有所帮助。

我已针对here撰写了有关此解决方案工作方式的详细文章/分割。