There are various ways to generate JSON in Rails. There is to_json method built in Rails. We can also use jbuilder gem or active model serializer gem which can help us achieve the same.
As the number of records in the database grow, Rails can take a very long time to generate a response. The bottleneck can generally be traced back to JSON generation.
Recently, in one of our applications, we faced this issue where a page was taking too long to load. The load time was critical for us as this was the most visited page on the site. The page was loading a race, its racers and their lap details.
The page was loading fine for short races, with 10-15 racers and each racer having 30-50 laps. But for endurance races, with around 50-80 racers and each racer having around 700-800 laps, we were hitting the bottleneck with load times.
After benchmarking, JSON generation at the backend was found to be the culprit.
Looking out for solutions to fix the problem, we came across PostgreSQL JSON functions.
PostgreSQL 9.2 and above have built in support for generating JSON using functions row_to_json and array_to_json. Let's look into both of them in detail.
row_to_json
row_to_json returns each of the rows as JSON object.
1 2select row_to_json(laps) from laps; 3 4{"id":1, 5 "number":1, 6 "position":4, 7 "time":"628.744", 8 "flag_type":"Green" 9} 10. 11. 12.
We could use a subquery to only fetch the attributes/columns which we require.
1select row_to_json(lap) 2from ( 3 select id, number, position, time, flag_type from laps 4) lap; 5 6{"id":1,"number":1,"position":4,"time":"628.744","flag_type":"Green"} 7{"id":2,"number":2,"position":4,"time":"614.424","flag_type":"Green"} 8. 9. 10.
array_to_json
To understand array_to_json function, we must first look into array_agg. array_agg is an aggregate function. Aggregate functions compute a single result from a set of input values. sum, min, max are some other examples of aggregate functions. array_agg concatenates all the input values into a PostgreSQL array.
1select array_agg(lap) 2from ( 3 select id, number, position, time, flag_type from laps 4) lap; 5 6{"(1,1,4,\"628.744\",\"Green\")","(2,2,4,\"614.424\",\"Green\")", ... }
To convert this PostgreSQL array into JSON, we can use the array_to_json function.
1select array_to_json(array_agg(lap)) 2from ( 3 select id, number, position, time, flag_type from laps 4) lap; 5 6[{"id":1, 7 "number":1, 8 "position":4, 9 "time":"628.744", 10 "flag_type":"Green"}, 11 ...]
A more complex example
We can use the above two functions together to generate custom JSON response.
1select row_to_json(u) 2from ( 3 select first_name, last_name, 4 ( 5 select array_to_json(array_agg(b)) 6 from ( 7 select number, position, time, flag_type 8 from laps 9 inner join racer_laps 10 on laps.id = racer_laps.lap_id 11 where racer_laps.racer_id = racers.id 12 ) b 13 ) as laps 14 from racers 15 where first_name = 'Jack' 16) u; 17 18{ 19 "first_name": "Jack", 20 "last_name": "Altenwerth", 21 "laps": [ 22 { 23 "number": 1, 24 "position": 4, 25 "time": "628.744", 26 "flag_type": "Green" 27 }, 28 { 29 "number": 2, 30 "position": 4, 31 "time": "614.424", 32 "flag_type": "Green" 33 }, 34 ... 35 ] 36}
Using the functions in Rails
We can use the above mentioned functions in Rails as shown here.
1query = <<~EOQ 2select row_to_json(u) 3from ( 4 select first_name, last_name, 5 ( 6 select array_to_json(array_agg(b)) 7 from ( 8 select number, position, time, flag_type 9 from laps 10 inner join racer_laps 11 on laps.id = racer_laps.lap_id 12 where racer_laps.racer_id = racers.id 13 ) b 14 ) as laps 15 from racers 16 where first_name = 'Jack' 17) u; 18EOQ 19 20generated_json = ActiveRecord::Base.connection.execute(query).values; 21puts generated_json 22 23{ 24 "first_name": "Jack", 25 "last_name": "Altenwerth", 26 "laps": [ 27 { 28 "number": 1, 29 "position": 4, 30 "time": "628.744", 31 "flag_type": "Green" 32 }, 33 { 34 "number": 2, 35 "position": 4, 36 "time": "614.424", 37 "flag_type": "Green" 38 }, 39 ... 40 ] 41}
Although the code to generate the JSON using the above way is more verbose and less readable compared to other ways to generate JSON in Rails, it is more performant.
Observations
On the racer's page, generating the JSON using the PostgreSQL functions, gave us the following improvements.
For short races (10-15 racers and each racer having 30-50 laps), the average API response time decreased from 40ms to 15ms.
For endurance races (50-80 racers and each racer having around 700-800 laps), the average API response time decreased from 1200ms to 20ms.
Conclusion
Use Rails way of generating JSON as long as you can. If performance starts to be an issue then don't be afraid of using the features made available by the database. In this case we would be trading performance for complexity in code. However sometimes this trade is worth it.