Rails isn't correctly rendering nested JSON results from Postgres JSON functions

Question

For various reasons, I'm creating an app that takes a SQL query string as a URL parameter and passes it off to Postgres(similar to the CartDB SQL API, and CFPB's Qu). Rails then renders a JSON response of the results that come from Postgres.

Snippet from my controller:

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

This works fine. But when I use Postgres JSON functions (row_to_json, json_agg), it renders the nested JSON property as a string. For example, the following query:

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

returns:

{
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}]"
}

So, it only partially renders the JSON, running into problems when I have nested JSON arrays created with the Postgres functions in the query.

I'm not sure where to start with this problem. Any ideas? I am sure this is a problem with Rails.

Answer 1

ActiveRecord::Base.connection.execute doesn't know how to unpack database types into Ruby types so everything – numbers, booleans, JSON, everything – you get back from it will be a string. If you want sensible JSON to come out of your controller, you'll have to convert the data in @table to Ruby types by hand and then convert the Ruby-ified data to JSON in the usual fashion.

Your @table will actually be a PG::Result instance and those have methods such as ftype (get a column type) and fmod (get a type modifier for a column) that can help you figure out what sort of data is in each column in a PG::Result. You'd probably ask the PG::Result for the type and modifier for each column and then hand those to the format_type PostgreSQL function to get some intelligible type strings; then you'd map those type strings to conversion methods and use that mapping to unpack the strings you get back. If you dig around inside the ActiveRecord source, you'll see AR doing similar things. The AR source code is not for the faint hearted though, sorry but this is par for the course when you step outside the narrow confines of how AR things you should interact with databases.

You might want to rethink your "sling hunks of SQL around" approach. You'll probably have an easier time of things (and be able to whitelist when the queries do) if you can figure out a way to build the SQL yourself.

Answer 2

The PG::Result class (the type of @table), utilizes TypeMaps for type casts of result values to ruby objects. For your example, you could use PG::TypeMapByColumn as follows:

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

A more generic approach would be to use the PG::TypeMapByOid TypeMap class. This requires you to provide OIDs for each PG attribute type. A list of these can be found in 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