ruby-on-rails - Rails 无法正确呈现来自 Postgres JSON 函数的嵌套 JSON 结果

Question

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

来 self 的 Controller 的片段:

@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

ActiveRecord::Base.connection.execute 不知道如何将数据库类型解压为 Ruby 类型，所以一切——数字、 bool 值、JSON，一切——你从它返回的将是一个字符串.如果您希望 Controller 输出合理的 JSON，您必须手动将 @table 中的数据转换为 Ruby 类型，然后以通常的方式将 Ruby 化数据转换为 JSON。

你的 @table 实际上是一个 PG::Result实例和那些有方法，如 ftype (获取列类型)和 fmod (获取列的类型修饰符)可以帮助您确定 PG::Result 中每一列的数据类型。您可能会向 PG::Result 询问每一列的类型和修饰符，然后将它们交给 format_type。 PostgreSQL 函数获取一些可理解的类型字符串；然后您将这些类型字符串映射到转换方法，并使用该映射来解压缩您返回的字符串。如果深入了解 ActiveRecord 源代码，您会发现 AR 正在做类似的事情。 AR 源代码不适合胆小的人，抱歉，当您走出 AR 事物应如何与数据库交互的狭窄范围时，这对于类(class)来说是正常的。

您可能需要重新考虑您的“使用大量 SQL”方法。如果您能想出一种自己构建 SQL 的方法，您的事情可能会更轻松(并且能够在查询时列入白名单)。