We write about Ruby on Rails, React.js, React Native, remote work, open source, engineering and design.
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
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.
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 ...]
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}
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.
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
.
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.