Transforming Postgres rows into JSON
“ TLDR” — Use
json_build_objectthat has been available since postgres 9.4 to return your postgres query results as json objects.
Why would we want this?
Have you ever needed to create a product listing containing a list of products with specific information? If so, then you definitely had to do some fancy queries over multiple tables and join the data you wanted in a smart way.
But after all that work, you receive the query response in a format you can’t directly throw back at your user. But instead you have to transform all these data points into an array of JSON objects and then finally give it back to your user.
Then json_build_objectmight be the solution for you! With json_build_object you can create your own JSON object based on your queried data all from inside the query creation context.
json_build_object is a function that has been introduced in postgres 9.4 which allows the creation of custom JSON results based on retrieved row data.
How can you use it?
Using thejson_build_object function is quite simple and only requires a select statement, and to write down how you want the JSON to look.
json_build_object takes in variadic arguments, which means it takes multiple arguments. These arguments have to be added in pairs and comma separated(key, value). For example, to make a JSON object for your user information with a username, email selected from the users table. You could write the following query
select json_build_object('username',username,'email',email) from users;
this will generate the following JSON per row:
{
"username": "ERBO",
"email": "consultancy@erbo.io"
}Now, this is only a short example and using just one function that allows cool JSON usability inside your Postgres queries. Let’s showcase 2 more functions row_to_json and json_agg to help with creating JSON results.
Row to JSON
If you would like to just turn a whole row into JSON, you could use therow_to_json function, with as input your row.
select row_to_json(users) from users;
Which will return your entire row data in a JSON format.
Aggregating
If you want to do some aggregations on your data and inject them into your JSON object, you can use the aggregate functions in Postgres.
We plan to dive a bit deeper into the aggregation functions in a future blog post, but we still want to give you a short example of what you can do with such an aggregation function.
So, for example, imagine that you have a users table and a posts table. The user can create posts and the created post will be linked back to the user via a author_id.
Now the data we want to retrieve is a list of 5 users and a list of all their created post titles. We first have to select 5 users and retrieve their created posts. To do this without aggregation will result in a lot of rows and matching post data with user data and creating a new object per row.
But since we can use json_agg to aggregate for example the post titles from a joined post table on our user query. We can retrieve the exact data format we want directly from within a Postgres query.
select
json_build_object(
'username', u.username,
'post_title',json_agg(p.title)
)
from users u
join posts p on p.author_id = u.user_id
group by u.user_id
limit 5;This query will result in the following data
{
"username": "Theo",
"post_titles": [
"Cool post 1",
"Cool post 2"
]
}Treat for our readers 🎐
Today we are giving a shoutout to a neat tool that was used to create this post.
For all the testing of the Postgres queries and easily viewing the results in JSON and rows.
This tool is named dbeaver and works for windows, mac and Linux and is super easy to set up and start working with it. We use it to test out queries and determine the performance of the queries we write.
We used to do this with pgadmin4, but having a dedicated application you can use feels a lot nicer and quicker.
Sources
Follow us for more articles in the near future. 🚀
We plan to publish an article at least once a month containing all kinds of interesting tech topics. Ranging from “Improving your development environment” to “Introduction into embedded Linux” and many more.
