Eu tenho o seguinte esquema para a tabela de tags:

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

Preciso criar uma consulta para retornar a seguinte estrutura (aqui representada como yaml para facilitar a leitura):

- 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: []

Após algumas tentativas e erros e procurando por perguntas semelhantes no SO, criei esta consulta:

    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

Mas ele retorna os seguintes resultados quando convertido em 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: []

O nó raiz está duplicado. Eu poderia mesclar os resultados com o resultado esperado no código do meu aplicativo, mas sinto que estou perto e isso pode ser feito com o PG.

Aqui está um exemplo com o SQL Fiddle:http://sqlfiddle.com/#!15/1846e/1/0

Saída esperada:https://gist.github.com/maca/e7002eb10f36fcdbc51b

Saída real:https://gist.github.com/maca/78e84fb7c05ff23f07f4