PostgreSQL: Generate JSON from a table

Introduction

Generating JSON from database tables is a practical way to seamlessly integrate databases with modern applications. In this tutorial, we will explore how PostgreSQL, with its robust JSON support, can be utilized to convert table data into JSON format.

Basic JSON Generation

To start, we’ll look at a very basic example of exporting a whole table as a JSON array. Assuming we have a table named users containing user data, we would write the following SQL:

SELECT json_agg(t) 
FROM (
  SELECT * FROM users
) t;

This query will output every row in the users table as a JSON array of objects, where each object represents a single row.

Column Selection and Aliasing

To only include specific columns and possibly rename them in the JSON output, modify the subquery within json_agg function:

SELECT json_agg(t) 
FROM (
  SELECT id, username AS user
  FROM users
) t;

The output JSON will now contain only id and user fields, the latter being an alias for the actual username column.

Filtering Data

Including filtering conditions inside the subquery is similar to a standard SQL query:

SELECT json_agg(t)
FROM (
  SELECT id, username
  FROM users
  WHERE active = true
) t;

Only active users will be included in the generated JSON array.

Complex Data Structures

A more complex JSON structure might involve nested objects. Consider a table orders that relates to users table:

SELECT json_agg(t) 
FROM (
  SELECT
    u.username,
    (
      SELECT json_agg(o) FROM (
        SELECT product_id, quantity FROM orders WHERE user_id = u.id
      ) o
    ) AS orders
  FROM
    users u
) t;

Each user object in the array will have an orders field, which itself is an array of order objects.

JSON Object Aggregation

Instead of an array, maybe a single JSON object mapping user IDs to user data is needed:

SELECT json_object_agg(u.id, u_info) 
FROM (
    SELECT id, json_build_object('username', username, 'email', email) AS u_info
    FROM users
) u;

This outputs a JSON object with user IDs as keys and other user details as nested objects.

Advanced Aggregations

PostgreSQL allows for even more complex queries combining multiple aggregations. For example:

SELECT
    json_build_object(
        'total_users', (SELECT count(*) FROM users),
        'users', json_agg(t)
    ) 
FROM (
    SELECT
        id,
        username,
        (SELECT json_agg(o) FROM orders o WHERE o.user_id = users.id) AS orders
    FROM
        users
) t;

Here we aggregate user data along with a total user count into a single JSON object.

Performance Considerations

Large datasets can present performance issues. Use indexing and LIMIT clauses to manage the size of the data being processed into JSON when necessary.

Conclusion

PostgreSQL’s JSON capabilities offer powerful and flexible solutions for generating JSON data from table contents, enabling seamless integration with modern web services and applications.