Fetching JSON as a Column in PostgreSQL: A Simpler Way to Build API Responses

Problem Statement

Most API responses contain objects. In a typical software development workflow, we fetch data from a database using a SQL query and then convert the result object into an API response object. This process can be complex sometimes and require a lot of logical programming code, especially if the response contains an object or array of objects with complex logic.

However, we can use a different approach where we can fetch the objects as columns using the SQL query itself. This approach allows us to directly bind the SQL result into our object, which can simplify the code and make it easier to maintain. Libraries like sqlx support this approach. In this blog, we will see how to fetch JSON as a column in the SQL query itself.

Schema Setup

Customer Orders schema

I have created the order management schema, along with some sample data, which is available here: https://github.com/kesavanpalani/sql-scripts/blob/main/order-details

Order Details API Response

We want to find all order details, including order, customer, shipping address, and product information. Let’s see how we can get this data from a SQL query itself.

{
    order_id,
    ordered_at,
    customer_id,
    customer_name,
    email,
    phone_number,
    address : {address_id, line1, line2, state, city, pincode}
    products:[{product_id, name, description, quantity}]
}

Solution

The order details response above contains a JSON object for address and an array for products, with all other attributes being primitive types. How can we fetch this as a JSON object? The answer is to use the json_build_object() and json_agg() functions.

json_build_object() is used to create a key-value pair, and json_agg()can be used to group the result into an array of objects.

Here, we will use json_build_object() to populate the address details, and we will use json_agg() to get the list of products as a single column.

The query is,

select
 customer_order.order_id,
 customer_order.created_at as ordered_at,
 customer.customer_id,
 customer."name",
 customer.email,
 customer.phone_number,
 json_build_object(
  'address_id',
  address.address_id,
  'line1',
  address.line1,
  'line2',
  address.line2,
  'pincode',
  address.pincode,
  'city',
  address.city,
  'state',
  address.state
 ) as address,
 json_agg(
  json_build_object(
   'product_id',
   product.product_id,
   'name',
   product."name",
   'description',
   product.description,
   'quantity',
   order_item.quantity
  )
 ) as products
from
 customer_order
 inner join customer on customer_order.customer_id = customer.customer_id
 inner join address on customer_order.shipping_address_id = address.address_id
 inner join order_item on customer_order.order_id = order_item.order_id
 inner join product on order_item.product_id = product.product_id
group by
 customer_order.order_id,
 customer.customer_id,
 address.address_id;

This will provide you with all the data necessary to build the previously mentioned order details response. As far as coding is concerned, you will only need to write logic to convert the DB result into an API response object.

Note that we have used the GROUP BY clause, which is required for json_agg() to uniquely identify the grouped table columns.

Conclusion

That’s all! Fetching JSON as a column is a great way to reduce unnecessary programming code and simplify the process of building API response results from database results.

Thanks for reading!