I’m creating a lot of JSON APIs using Grape or Ruby on Rails lately. From time to time I’m facing an issue when I need to return a large amount of data in a single response.
The usual way would be to execute query via ActiveRecord and use some library like JBuilder or ActiveModelSerializers to generate JSON response. It’s nice and easy but sometimes too slow :(
In situation like this I’m relying on PostgreSQL to help me out with it’s row_to_json function.
Let’s convert the chart query from my previous post so it can return json that is ready to send to the chart library right away:
with intervals as (
select start, start + interval '5min' as end
from generate_series('2015-05-29 9:30', '2015-05-29 16:00', interval '5min') as start
)
select json_agg(row_to_json(ohlc)) from
(
select distinct
intervals.start as date,
min(low) over w as low,
max(high) over w as high,
first_value(open) over w as open,
last_value(close) over w as close,
sum(volume) over w as volume
from
intervals
join ticker_minute_bars tmb on
tmb.key = 'AAPL.BATS' and
tmb.date >= intervals.start and
tmb.date < intervals.end
window w as (partition by intervals.start)
order by intervals.start
) as ohlc
Time: 76.968 msThe best part is that JSON serialization overhead comparing to the original query is almost invisible!
Thanks!