Rails无法正确呈现Postgres JSON函数的嵌套JSON结果

Question

出于各种原因，我正在创建一个应用程序，它将SQL查询字符串作为URL参数，并将其传递给Postgres(类似于CFPB的CartDB SQL API和CFPB的Qu)。然后，Rails呈现来自Postgres的结果的JSON响应。

来自我的控制器的代码片段：

@table = ActiveRecord::Base.connection.execute(@query)
render json: @table

这可以很好地工作。但是当我使用Postgres JSON函数(row_to_json、json_agg)时，它将嵌套的JSON属性呈现为一个字符串。例如，以下查询：

query?q=SELECT max(municipal) AS series, json_agg(row_to_json((SELECT r FROM (SELECT sch_yr,grade_1 AS value ) r WHERE grade_1 IS NOT NULL))ORDER BY sch_yr ASC) AS values FROM ed_enroll WHERE grade_1 IS NOT NULL GROUP BY municipal

返回：

{
series: "Abington",
values: "[{"sch_yr":"2005-06","value":180}, {"sch_yr":"2005-06","value":180}, {"sch_yr":"2006-07","value":198}, {"sch_yr":"2006-07","value":198}, {"sch_yr":"2007-08","value":158}, {"sch_yr":"2007-08","value":158}, {"sch_yr":"2008-09","value":167}, {"sch_yr":"2008-09","value":167}, {"sch_yr":"2009-10","value":170}, {"sch_yr":"2009-10","value":170}, {"sch_yr":"2010-11","value":153}, {"sch_yr":"2010-11","value":153}, {"sch_yr":"2011-12","value":167}, {"sch_yr":"2011-12","value":167}]"
},
{
series: "Acton",
values: "[{"sch_yr":"2005-06","value":353}, {"sch_yr":"2005-06","value":353}, {"sch_yr":"2006-07","value":316}, {"sch_yr":"2006-07","value":316}, {"sch_yr":"2007-08","value":323}, {"sch_yr":"2007-08","value":323}, {"sch_yr":"2008-09","value":327}, {"sch_yr":"2008-09","value":327}, {"sch_yr":"2009-10","value":336}, {"sch_yr":"2009-10","value":336}, {"sch_yr":"2010-11","value":351}, {"sch_yr":"2010-11","value":351}, {"sch_yr":"2011-12","value":341}, {"sch_yr":"2011-12","value":341}]"
}

因此，它只部分呈现JSON，当我在查询中使用Postgres函数创建嵌套JSON数组时会遇到问题。

我不知道从哪里开始解决这个问题。有什么想法吗？我确信这是Rails的一个问题。

Answer 1

PG::Result类(@table的类型)利用TypeMaps将结果值类型转换为ruby对象。对于您的示例，您可以使用PG::TypeMapByColumn，如下所示：

@table = ActiveRecord::Base.connection.execute(@query)
@table.type_map = PG::TypeMapByColumn.new [nil, PG::TextDecoder::JSON.new]
render json: @table

更通用的方法是使用PG::TypeMapByOid TypeMap类。这要求您为每个PG属性类型提供OID。可以在pg_type.dat中找到这些内容的列表。

tm = PG::TypeMapByOid.new
tm.add_coder PG::TextDecoder::Integer.new oid: 23
tm.add_coder PG::TextDecoder::Boolean.new oid: 16
tm.add_coder PG::TextDecoder::JSON.new oid: 114
@table.type_map = tm

Answer 2

ActiveRecord::Base.connection.execute不知道如何将数据库类型解压成Ruby型，所以所有东西--数字、布尔值、JSON、所有东西--都将是一个字符串。如果你想让你的控制器输出合理的JSON，你必须手动将@table中的数据转换成Ruby类型，然后以通常的方式将Ruby化的数据转换成JSON。

您的@table实际上是一个PG::Result实例，这些实例具有ftype (获取列类型)和fmod (获取列的类型修饰符)等方法，这些方法可以帮助您确定PG::Result中的每一列中是哪种类型的数据。您可能会向PG::Result请求每一列的类型和修饰符，然后将它们传递给format_type PostgreSQL函数以获得一些易于理解的类型字符串；然后将这些类型字符串映射到转换方法，并使用该映射来解压返回的字符串。如果你深入研究一下ActiveRecord源代码，你会发现AR做了类似的事情。虽然AR源代码不适合胆小鬼，但很抱歉，当您走出AR事物应该如何与数据库交互的狭窄限制时，这是理所当然的。

您可能需要重新考虑您的“抛出SQL块”的方法。如果您能找到一种自己构建SQL的方法，那么您可能会更轻松(并且能够在查询时将其列入白名单)。