主页 / dba / 问题 / 60253
Accepted
Travis
Asked: 2014-03-06 16:16:29 +0800 CST

如何在 PostgreSQL 中返回 JSON 结构作为 json_agg() 的转置？

  • 772

我正在尝试将任意子查询转换为 PostgreSQL 中的 JSON。

json_agg()似乎是这项工作的明显工具。例如：

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

但是，数据的结构为the_data[row_number]["column_name"]. 假设我希望数据结构为the_data["column_name"][row_number]. 也就是我想要json_agg()的转置结构。我怎么做？

我最大的努力是：

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;

但这有（至少）两个缺点：

  1. 它不适用于任意子查询。每次array_agg()使用时，必须对列名进行硬编码。
  2. 列名变为f1, f2, ...
postgresql json

1 个回答

  1. Best Answer

    这将解决 f1,f2,f3 名称问题，但不幸的是，想不出一个简单的方法来进行任意查询。

     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;

    这不是很短，但允许您在子查询中放置任意内容并输出列。（所以解决问题 1 和问题 2）。它搭载在 hstore 上，因此如果您没有安装 hstore，您需要执行以下操作： 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

相关问题