# Faster JSON generation using PostgreSQL

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

![hash](https://i.imgur.com/yqMt63o.png)

---

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

For example:
![hash](https://i.imgur.com/22OSnRP.png)

---

Second function us __to_jsonb__:
to_jsonb converts the whole row to JSON
![hash](https://i.imgur.com/e7SeKkD.png)

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
![hash](https://i.imgur.com/Tra7ZqB.png)

---

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.

![hash](https://i.imgur.com/lZ75tV6.png)

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.