Introduction to generating JSON using PostgreSQL

Published On - November 24, 2016

coderhs

Introduction One of the major requirements for any online business is to have a backend that either provides or can be extended to provide an API response. Building  websites with static HTML and simple jquery ajax is coming to an end. In this era, Javascript frameworks rules the market. Hence, it is a good decision for the database to support JSON, as JSON is becoming the glue that connects the frontend and backend. Rails have an inbuilt support for generating JSON, as it’s our swiss army knife of web development, and encourages the REST URL structure . And its a good choice for building API. It is good enough to a particular point of growth. Very soon you will reach bottlenecks, where you have more requests than you can handle and you have to either spawn up more servers or use some concurrent languages like elixir, go, etc. Before we go to that scale and burn down the existing codebase, we can use database to generate JSON responses for us, which is 10 times faster in generating JSON than Rails (though more verbose). Since PostgreSQL 9.2, the database has taken a major leap in supporting JSON. The support that PostgreSQL provides can be divided into two

  • Storing data in JSON and JSONB format
  • Generating JSON results from the query itself
In this article, we will talk about generating JSON(an introduction) from the query itself.

Getting Started

One of the advantages of using a database to generate JSON is that I have found it fast while generating smaller JSON but much more faster in generating complex JSON. (Note: The speed is in comparison with rails not with respect to the database itself)

How to generate JSON

Simplest way to do that is row_to_json() For example: Query to return user with id 1 as JSON
select row_to_json(users) from users where id = 1;
Result: 
{"id":1,"email":"[email protected]","encrypted_password":"iwillbecrazytodisplaythat",
"reset_password_token":null,"reset_password_sent_at":null,
"remember_created_at":"2016-11-06T08:39:47.983222",
"sign_in_count":11,"current_sign_in_at":"2016-11-18T11:47:01.946542",
"last_sign_in_at":"2016-11-16T20:46:31.110257",
"current_sign_in_ip":"::1","last_sign_in_ip":"::1",
"created_at":"2016-11-06T08:38:46.193417",
"updated_at":"2016-11-18T11:47:01.956152",
"first_name":"Super","last_name":"Admin","role":3}
if you want to send only some specific fields 
select row_to_json(results)
from (
  select id, email from users
) as results
Result 
{"id":1,"email":"[email protected]"}
Now let’s see how to generate more complex JSON with sub JSON, and arrays. 
select row_to_json(result)
from (
  select id, email,
    (
      select array_to_json(array_agg(row_to_json(user_projects)))
      from (
        select id, name
        from projects
        where user_id=users.id
        order by created_at asc
      ) user_projects
    ) as projects
  from users
  where id = 1
) result
This would return the JSON response 
{"id":1,"email":"[email protected]", "project":["id": 3, "name": "CSnipp"]}
The issue with the above code is that it is more verbose (has more text)  when compared to a ruby code. We need to make sure that while we do a bit of sacrifice there, is worthwhile. So while working with API’s  use it only where you see a delay in JSON generation. Similarly ,to the ‘array_agg’ method that we used above to aggregate values to an array then to JSON, we aggregate them as JSON using json_agg. 
array_to_json(array_agg(row_to_json(user_projects)))
can be shortened to 
json_agg(user_projects)
  Since the above method of array generation can be tedious, in PostgreSQL 9.4, they have introduced a new method called json_build_object. Simple usage of the function can be as below 
json_build_object('foo',1,'bar',2)
which will output 
{"foo": 1, "bar": 2}
Also, we can use it to build complex JSON tree by creating functions within the PostgreSQL database. Of course, as we do that, we are moving more and more logic of the code into the DB and we would need to run migrations every time when we want to update a function. So as I said before, we are sacrificing our convenience here .So we should only use this, as the complexity of our JSON generation increases. I will be covering how to write PostgreSQL functions to help generate more complex JSON structure easier in the second part of this particle.

References

https://www.postgresql.org/docs/current/static/functions-json.html http://bytefish.de/blog/postgresql_json/]]>