Se você já se deparou alguma vez com a necessidade de desnormalizar um relacionamento entre duas ou mais tabelas para criar uma lista de strings, ou criar um mapeamento para popular uma estrutura de grafo ou documento JSON, ou ainda converter do Relacional para JSON, você precisa conhecer as funções LISTAGG, JSON_OBJECT e JSON_ARRAYAGG.

Como exemplo considere as seguintes tabelas:

ALUNO (id, nome)
DISCIPLINA (id, nome)
ALUNO_DISCIPLINA (id_aluno, id_disciplina)

A tabela ALUNO_DISCIPLINA é basicamente a tabela de ligação do muitos-para-muitos entre ALUNO e DISCIPLINA, já que um aluno pode cursar várias disciplinas, e cada disciplina pode ter muitos alunos.

Então a seguinte consulta retorna:

SELECT a.nome AS nome_aluno,
       b.nome AS nome_disciplinas
FROM aluno A,
     disciplina B,
     aluno_disciplina C
WHERE a.id = c.id_aluno
  AND b.id = c.id_disciplina
ORDER BY a.nome, b.nome

Transforma os Valores de Linhas Diferentes em uma String separada por Algum Delimitador

Para desnormalizar, basta agrupar pelas colunas que devemos manter como identificador [no meu exemplo nome do aluno] e depois na função LISTAGG coloque a coluna que queremos desnormalizar e o delimitador [neste caso, vírgula]. WITHIN GROUP permite ordenar o resultado:

SELECT a.nome AS nome_aluno,
       LISTAGG(b.nome, ',') WITHIN GROUP (ORDER BY b.nome) AS lista_disciplinas
FROM aluno A,
     disciplina B,
     aluno_disciplina C
WHERE a.id = c.id_aluno
  AND b.id = c.id_disciplina
GROUP BY a.nome ORDER BY a.nome

Mais simples do que parece, certo?! Se você quiser transformar dados no modelo Relacional para um modelo de Grafos, pode usar LISTAGG para ligar as arestas aos nós com apenas uma query. Há outras variantes do LISTAGG na documentação, neste link. No Oracle ela está disponível a partir da versão 11g, e há similares em outras tecnologias.

Na versão 19c foi incluída a opção DISTINCT no LISTAGG, para o caso de você querer eliminar os valores duplicados que são listados com LISTAGG. Veja este exemplo onde eu projeto o nome do departamento e listo ao lado, separado por vírgula, todos os cargos a partir da tabela de funcionários. Como vários funcionários podem ter o mesmo cargo, ele poderá se repetir na lista. Então com distinct nós eliminamos os valores duplicados:

Transforma Relacional em JSON, usando Atributos Simples ou com Arrays

Voltando ao exemplo dos estudantes, ao invés de apenas desnormalizar, vamos também converter para JSON, e aninhar as disciplinas, pois quero montar um Documento com os alunos e seus respectivos cursos:

SELECT JSON_OBJECT('id'          VALUE a.id,
                   'nome'        VALUE a.nome,
                   'disciplinas' VALUE JSON_ARRAYAGG(b.nome order by b.nome)) JSON_DOCUMENT
FROM aluno A,
     disciplina B,
     aluno_disciplina C
WHERE a.id = c.id_aluno
  AND b.id = c.id_disciplina
GROUP BY a.id, a.nome ORDER BY a.nome

JSON_OBJECT transforma uma coluna em um atributo JSON em um documento, e JSON_ARRAYAGG cria um array para um atributo. Temos como resultado um documento JSON com os códigos dos alunos, nomes e um array com as disciplinas que cada um cursa. Há uma variedade de funções de conversão para JSON no Oracle. Especificamente a JSON_ARRAYAGG você pode verificar a referência neste link. JSON_ARRAYAGG está disponível a partir da versão 12c.

A partir da versão 19c do a função JSON_OBJECT foi simplificada. Basta listar as colunas separadas por vírgula, ou então colocar asterisco para retornar todas as colunas. Veja:

Para criar um array com JSON_ARRAYAGG utilizando a nova forma do JSON_OBJECT podemos fazer como no exemplo abaixo. Eu retorno o departamento com o nome de todos os funcionários:

Agora Faz o Contrário: Transforma do JSON para o Relacional

E para converter de JSON para Relacional, utilize NESTED sobre a coluna que representa o documento JSON e as colunas que você quer projetar [também somente na versão 19c]. Eu fiz uma sub-query utilizando o exemplo acima [transformando de Relacional para JSON] e a query superior faz o inverso, transformando o JSON no Relacional:

Utilizei o Oracle XE 18c para fazer os testes, e o SQL Developer como front-end. Todos são gratuitos e estão disponíveis para download. Para os comandos que funcionam no 19c, utilizei Oracle SQL Live direto na internet [no momento que estou escrevendo este post, a versão 19c está disponível somente no SQL Live]. Update: você pode criar uma conta no free tier da Oracle Cloud (OCI) e utilizar gratuitamente o Oracle Autonomous Database.