---
title: "Faster JSON generation using PostgreSQL JSON function"
description:
  "PostgreSQL 9.2 and above has inbuilt support for JSON generation which can be
  used for faster JSON generation"
canonical_url: "https://www.bigbinary.com/blog/generating-json-using-postgresql-json-function"
markdown_url: "https://www.bigbinary.com/blog/generating-json-using-postgresql-json-function.md"
---

# Faster JSON generation using PostgreSQL JSON function

PostgreSQL 9.2 and above has inbuilt support for JSON generation which can be
used for faster JSON generation

- Author: Chirag Shah
- Published: May 29, 2018
- Categories: Rails

There are various ways to generate JSON in Rails. There is
[to_json](http://api.rubyonrails.org/classes/ActiveStorage/Filename.html#method-i-to_json)
method built in Rails. We can also use
[jbuilder gem](https://github.com/rails/jbuilder) or
[active model serializer gem](https://github.com/rails-api/active_model_serializers)
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](https://www.postgresql.org/docs/9.2/static/functions-json.html).

**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.

```pgsql

select row_to_json(laps) from laps;

{"id":1,
 "number":1,
 "position":4,
 "time":"628.744",
 "flag_type":"Green"
}
.
.
.
```

We could use a subquery to only fetch the attributes/columns which we require.

```pgsql
select row_to_json(lap)
from (
  select id, number, position, time, flag_type from laps
) lap;

{"id":1,"number":1,"position":4,"time":"628.744","flag_type":"Green"}
{"id":2,"number":2,"position":4,"time":"614.424","flag_type":"Green"}
.
.
.
```

## 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.

```pgsql
select array_agg(lap)
from (
  select id, number, position, time, flag_type from laps
) lap;

{"(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.

```pgsql
select array_to_json(array_agg(lap))
from (
  select id, number, position, time, flag_type from laps
) lap;

[{"id":1,
  "number":1,
  "position":4,
  "time":"628.744",
  "flag_type":"Green"},
  ...]
```

## A more complex example

We can use the above two functions together to generate custom JSON response.

```pgsql
select row_to_json(u)
from (
  select first_name, last_name,
    (
      select array_to_json(array_agg(b))
      from (
        select number, position, time, flag_type
        from laps
        inner join racer_laps
        on laps.id = racer_laps.lap_id
        where racer_laps.racer_id = racers.id
      ) b
    ) as laps
  from racers
  where first_name = 'Jack'
) u;

{
  "first_name": "Jack",
  "last_name": "Altenwerth",
  "laps": [
    {
      "number": 1,
      "position": 4,
      "time": "628.744",
      "flag_type": "Green"
    },
    {
      "number": 2,
      "position": 4,
      "time": "614.424",
      "flag_type": "Green"
    },
    ...
  ]
}
```

## Using the functions in Rails

We can use the above mentioned functions in Rails as shown here.

```pgsql
query = <<~EOQ
select row_to_json(u)
from (
  select first_name, last_name,
    (
      select array_to_json(array_agg(b))
      from (
        select number, position, time, flag_type
        from laps
        inner join racer_laps
        on laps.id = racer_laps.lap_id
        where racer_laps.racer_id = racers.id
      ) b
    ) as laps
  from racers
  where first_name = 'Jack'
) u;
EOQ

generated_json = ActiveRecord::Base.connection.execute(query).values;
puts generated_json

{
  "first_name": "Jack",
  "last_name": "Altenwerth",
  "laps": [
    {
      "number": 1,
      "position": 4,
      "time": "628.744",
      "flag_type": "Green"
    },
    {
      "number": 2,
      "position": 4,
      "time": "614.424",
      "flag_type": "Green"
    },
    ...
  ]
}
```

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.

## Links

- [Human page](https://www.bigbinary.com/blog/generating-json-using-postgresql-json-function)