时间戳

首页 > 解决方案 > 如何在 Django 查询集中实现 json_object_agg()？

python - 如何在 Django 查询集中实现 json_object_agg()？

问题描述

如何在视图中的 Django 查询集中实现来自 PostgreSQL 的 json_object_agg() 函数？

例如，我想将类似于下面的查询转换为 Django 查询集（取自https://postgresql.verite.pro/blog/2018/06/19/crosstab-pivot.html）

SELECT city,
       json_object_agg(year,total ORDER BY year)
   FROM (
     SELECT city, year, SUM(raindays) AS total
        FROM rainfall
        GROUP BY city,year
   ) s
  GROUP BY city
  ORDER BY city;

具有预期的输出，例如

   city    |                                    json_object_agg                                     
-----------+----------------------------------------------------------------------------------------
 Ajaccio   | { "2012" : 69, "2013" : 91, "2014" : 78, "2015" : 48, "2016" : 81, "2017" : 51 }
 Bordeaux  | { "2012" : 116, "2013" : 138, "2014" : 137, "2015" : 101, "2016" : 117, "2017" : 110 }
 Brest     | { "2012" : 178, "2013" : 161, "2014" : 180, "2015" : 160, "2016" : 165, "2017" : 144 }
 Dijon     | { "2012" : 114, "2013" : 124, "2014" : 116, "2015" : 93, "2016" : 116, "2017" : 103 }
 Lille     | { "2012" : 153, "2013" : 120, "2014" : 136, "2015" : 128, "2016" : 138, "2017" : 113 }
 Lyon      | { "2012" : 112, "2013" : 116, "2014" : 111, "2015" : 80, "2016" : 110, "2017" : 102 }
 Marseille | { "2012" : 47, "2013" : 63, "2014" : 68, "2015" : 53, "2016" : 54, "2017" : 43 }
 Metz      | { "2012" : 98, "2013" : 120, "2014" : 110, "2015" : 93, "2016" : 122, "2017" : 115 }
 Nantes    | { "2012" : 124, "2013" : 132, "2014" : 142, "2015" : 111, "2016" : 106, "2017" : 110 }
 Nice      | { "2012" : 53, "2013" : 77, "2014" : 78, "2015" : 50, "2016" : 52, "2017" : 43 }
 Paris     | { "2012" : 114, "2013" : 111, "2014" : 113, "2015" : 85, "2016" : 120, "2017" : 110 }
 Perpignan | { "2012" : 48, "2013" : 56, "2014" : 54, "2015" : 48, "2016" : 69, "2017" : 48 }
 Toulouse  | { "2012" : 86, "2013" : 116, "2014" : 111, "2015" : 83, "2016" : 102, "2017" : 89 }
(13 rows)

标签： pythondjangodjango-views

解决方案

推荐阅读