What?

There are a lot of ways to generate JSON in rails, from the inbuilt to_json to gems like jbuilder gem or active model serializer. But as responses and data grow in size JSON generation can become a bottleneck for many backend services.

In this post i want to show you how your PostgreSQL DB can do this for you.

Why?

You can find multiple articles where they have tested this and found it to be 10X faster than plain ActiveModel::Serializers for small data sets, and 160X faster for larger data sets.

By doing all this in PostgreSQL, we avoid using CPU cycles and memory on our web server.

Is the complexity worth it? Well, in my opinion you can use your framework to do all the heavy lifting untill performance becomes a problem and then the tradeoff of complexity vs performance might make sense.

How?

I will just go over the basics of few common json functions available in PostgreSQL.

Let's create 2 tables and some sample data.

---

First function is jsonb_agg: jsonb_agg all the input values, including nulls, into a JSON array.

For example:

---

Second function us to_jsonb: to_jsonb converts the whole row to JSON

If you want the whole row except some keys, starting with Postgres 9.6 you can simply use - to remove a key from a JSONB:

Here I removed id key

---

Third function is json_build_object: json_build_object() function creates and returns a JSON object from a variadic parameter list consisting of alternating keys and values.

Here col_id is my key and collections.id is my value. You can use this if you want your keys to be different from the row name or use other functions (for eg: round()) on your keys.

Although the code to generate the JSON using the above way is more verbose and less readable compared to other ways to generate JSON, it is more performant.