Início / dba / Perguntas / 60253
Accepted
Travis
Asked: 2014-03-06 16:16:29 +0800 CST

Como retorno o JSON estruturado como a transposição de json_agg () no PostgreSQL?

  • 772

Estou tentando converter uma subconsulta arbitrária em JSON no PostgreSQL.

json_agg()parece ser a ferramenta óbvia para o trabalho. Por exemplo:

select json_agg(a) from (
    select count(lead_source), lead_source from leads_table group by lead_source order by count desc
) as a;

No entanto, os dados são estruturados como arquivos the_data[row_number]["column_name"]. Suponha que eu queira dados estruturados como the_data["column_name"][row_number]. Ou seja, quero a estrutura de transposição de json_agg(). Como faço isso?

Meu melhor esforço é:

select row_to_json(row(array_to_json(array_agg(count)), array_to_json(array_agg(lead_source)))) from (
    select count(lead_source), lead_source from leads_table group by lead_source order by count desc
) as a;

Mas isso tem (pelo menos) duas desvantagens:

  1. Não funciona para uma subconsulta arbitrária. Os nomes das colunas devem ser codificados sempre que array_agg()forem usados.
  2. Os nomes das colunas se tornam f1, f2, ...
postgresql json

1 respostas

  1. Best Answer

    Isso resolverá o problema do nome f1,f2,f3, mas infelizmente não consigo pensar em uma maneira fácil de fazer uma consulta arbitrária.

     SELECT row_to_json(r) from 
      (SELECT array_to_json(array_agg(count)) As count, 
              array_to_json(array_agg(lead_source)) As lead_source
      FROM (
        SELECT count(lead_source), lead_source from leads_table group by lead_source    
          ORDER BY count desc
    ) as a) AS r;

    Este não é tão curto, mas permitirá que você coloque um arbitrário em sua subconsulta e gere as colunas. (resolvendo o problema 1 e o problema 2). Ele pega carona no hstore, portanto, se você não tiver o hstore instalado, precisará fazer um: CREATE EXTENSION hstore;

    SELECT  ('{' || string_agg(sv, ',') || '}')::json
 FROM (SELECT ('"' || (kv).key || '"' || ':' 
   || array_to_json(array_agg((kv).value) ) )   As sv
FROM (SELECT each(hstore(a)) As kv from 
  (
   SELECT count(lead_source), lead_source from leads_table group by lead_source    
      ORDER BY count desc
   ) as a) As r
 GROUP By (kv).key) As r2;
    • 1

relate perguntas