O resultado do retorno do PostgreSQL é definido como matriz JSON?
Eu gostaria que o PostgreSQL retornasse o resultado de uma consulta como uma matriz JSON. Dado
Eu gostaria de algo parecido com
(na verdade, seria mais útil conhecer os dois). Eu tentei algumas coisas como
E sinto que estou perto, mas na verdade não existe. Devo procurar outra documentação, exceto a 9.15. Funções e operadores JSON ?
A propósito, não tenho certeza da minha ideia. Essa é uma decisão usual de design? Meu pensamento é que eu poderia, é claro, pegar o resultado (por exemplo) da primeira das 3 consultas acima e manipulá-lo levemente no aplicativo antes de servi-lo ao cliente, mas se o PostgreSQL puder criar diretamente o objeto JSON final diretamente, seria mais simples, porque ainda não incluí nenhuma dependência em nenhuma biblioteca JSON no meu aplicativo.
— engineerX
O PG 9.4, agora disponível na versão beta 1, aprimorou o suporte ao JSON, incluindo E / S binária. Se você estiver em uma máquina de desenvolvimento, poderá dar uma olhada.
@ Patrick: obrigado, parece que json_object () é uma nova função na 9.4 e eu tentaria algo como SELECT json_object (array_agg (ta), array_agg (tb)) FROM t, se eu tivesse
para uma matriz de objetos JSON e
para um objeto JSON de matrizes.
Esta seção descreve como gerar uma matriz de objetos JSON, com cada linha sendo convertida em um único objeto. O resultado fica assim:
9.3 e superior
A json_aggfunção produz esse resultado imediatamente. Ele automaticamente descobre como converter sua entrada em JSON e a agrega em uma matriz.
Não existe jsonb(introduzida na 9.4) versão do json_agg. Você pode agregar as linhas em uma matriz e depois convertê-las:
ou combine json_aggcom um elenco:
Meus testes sugerem que agregá-los em uma matriz primeiro é um pouco mais rápido. Eu suspeito que isso ocorre porque o elenco precisa analisar o resultado JSON inteiro.
9.2 não possui as funções json_aggou to_json, portanto, você precisa usar as mais antigas array_to_json:
Opcionalmente, você pode incluir uma row_to_jsonchamada na consulta:
Isso converte cada linha em um objeto JSON, agrega os objetos JSON como uma matriz e, em seguida, converte a matriz em uma matriz JSON.
Não consegui discernir nenhuma diferença significativa de desempenho entre os dois.
Objeto de listas
Esta seção descreve como gerar um objeto JSON, com cada chave sendo uma coluna na tabela e cada valor sendo uma matriz dos valores da coluna. É o resultado que se parece com isso:
9.5 e superior
Podemos aproveitar a json_build_objectfunção:
Você também pode agregar as colunas, criando uma única linha e convertê-las em um objeto:
Observe que o alias das matrizes é absolutamente necessário para garantir que o objeto tenha os nomes desejados.
Qual é o mais claro é uma questão de opinião. Se estiver usando a json_build_objectfunção, recomendo colocar um par de chave / valor em uma linha para melhorar a legibilidade.
Você também pode usar array_aggno lugar de json_agg, mas meus testes indicam que json_aggé um pouco mais rápido.
Não há jsonbversão da json_build_objectfunção. Você pode agregar em uma única linha e converter:
Ao contrário das outras consultas para esse tipo de resultado, array_aggparece ser um pouco mais rápido ao usar to_jsonb. Eu suspeito que isso se deva à sobrecarga de análise e validação do resultado JSON de json_agg.
Ou você pode usar um elenco explícito:
A to_jsonbversão permite evitar o elenco e é mais rápida, de acordo com meus testes; novamente, suspeito que isso se deva à sobrecarga de analisar e validar o resultado.
9.4 e 9.3
A json_build_objectfunção era nova na 9.5, portanto, você precisa agregar e converter em um objeto nas versões anteriores:
dependendo se você deseja jsonou jsonb.
(9.3 não possui jsonb.)
No 9.2, nem to_jsonexiste. Você deve usar row_to_json:
Encontre a documentação para as funções JSON nas funções JSON .
json_aggestá na página de funções agregadas .
Se o desempenho for importante, verifique se suas consultas são comparadas com seu próprio esquema e dados, em vez de confiar nos meus testes.
Se é um bom design ou não, realmente depende da sua aplicação específica. Em termos de manutenção, não vejo nenhum problema específico. Isso simplifica o código do seu aplicativo e significa que há menos para manter nessa parte do aplicativo. Se o PG pode fornecer exatamente o resultado que você precisa imediatamente, a única razão pela qual posso pensar em não usá-lo seria considerações de desempenho. Não reinvente a roda e tudo.
As funções agregadas geralmente retornam NULLquando operam sobre zero linhas. Se isso for uma possibilidade, convém usá-lo COALESCEpara evitá-los. Alguns exemplos:
Agradecemos a Hannes Landeholm por apontar isso
Obrigado pela sua resposta. Você me inspirou a encontrar a resposta para minha segunda pergunta, SELECT row_to_json (row (array_agg (ta), array_agg (tb))) FROM t, embora o resultado tenha "f1" e "f2" como rótulos, em vez de a e b.
@engineerX Expandi minha resposta.
Em alguns casos, pode ser indesejável recuperar NULL de volta em vez de uma matriz JSON vazia quando a seleção interna (de t) retorna zero linhas. Isso é causado por funções agregadas sempre retornando NULL quando a seleção sobre nenhuma linha e solucionável por coalescência: array_to_json (coalescência (array_agg (t), array [] :: record [])).
— Hannes Landeholm 03/03
você pode usar em to_jsonvez de row_to_jsonearray_to_json
Para selecionar (várias) colunas específicas, você deve passá-las como um único argumento - como uma lista de colchetes SELECT json_agg((column1, column2, ...)) FROM t - observe os colchetes extras. Isso pode não ser óbvio "pronto para uso".