PostgreSQL: Build a JSON array

Introduction

PostgreSQL is a powerful, open-source object-relational database system that has strong support for handling JSON data types. This tutorial delves into the construction of JSON arrays within PostgreSQL, showcasing the flexibility and utility of JSON features in modern database systems.

Understanding JSON in PostgreSQL

Before diving into JSON arrays, it’s crucial to understand how PostgreSQL handles JSON data types. JSON (JavaScript Object Notation) is a lightweight data interchange format that is easy for humans to read and write, and easy for machines to parse and generate. PostgreSQL provides two JSON data types: json and jsonb. The type jsonb stores data in a decomposed binary format that is efficient to query, while json stores text data exactly as entered.

Creating a JSON array in PostgreSQL can be accomplished in various ways, ranging from aggregate functions to built-in functions and operators. Here, we will explore these methods with practical examples and explanations of their use cases.

Creating a Simple JSON Array

One of the basic methods to create a JSON array in PostgreSQL is by using the json_agg function. This function aggregates values, including records, as a JSON array.

SELECT json_agg(value) FROM (VALUES (1), (2), (3)) AS tbl(value);

This query will result in:

[
    1,
    2,
    3
]

Building a JSON Array from a Table

You can also build a JSON array directly from table data using the json_agg function.

SELECT json_agg(t) FROM (SELECT id, name FROM users) t;

This query aggregates each row of the users table into a JSON array where each element is a JSON object representing a row.

Complex JSON Array Construction

More advanced use cases maybe require nesting JSON arrays and objects. Using the json_build_array and json_build_object functions together, you can construct complex hierarchies.

SELECT
    json_build_object(
        'user', json_build_array(
            json_build_object('id', u.id, 'name', u.name),
            json_build_object('contacts', (
                SELECT json_agg(
                    json_build_object('type', c.type, 'value', c.value)
                )
                FROM contacts c
                WHERE c.user_id = u.id
            ))
        )
    )
FROM users u;

This query will create a JSON object for each user, including a nested array of contact objects.

Aggregating With Filtering

It’s also possible to aggregate a JSON array with conditionals for more selective array contents by using the FILTER clause with json_agg.

SELECT json_agg(t) FILTER (WHERE t.value > 1)
FROM (VALUES (1), (2), (3)) AS t(value);

The resulting JSON array will only include elements where the value is greater than 1.

Ordering and Limiting in JSON Arrays

To create an ordered JSON array or limit the elements that get included, the subquery used with json_agg can be sorted and limited accordingly before aggregation.

SELECT json_agg(t ORDER BY t.value DESC LIMIT 2)
FROM (VALUES (1), (2), (3)) AS t(value);

This example returns the top two values in descending order.

Transforming Relational Data

If you have relational data that needs to be transformed into a hierarchical JSON structure, a combination of functions, subqueries, and joins can be applied.

SELECT json_agg(
    json_build_object(
        'product', p.name,
        'price', p.price,
        'categories', (
            SELECT json_agg(c.name)
            FROM categories c
            JOIN product_categories pc ON c.id = pc.category_id
            WHERE pc.product_id = p.id
        )
    )
)
FROM products p;

The result is a JSON array of products, each with a nested array of their associated categories.

Using JSON Array Functions and Operators

PostgreSQL also provides functions and operators for working with JSON arrays, like appending elements using the || operator or extracting an element using the -> or ->> operators.

SELECT '[1,2]'::jsonb || '3'::jsonb;

This will output:

[1, 2, 3]

To extract the second element from a JSON array:

SELECT '[1, 2, 3]'::jsonb -> 1;

This query produces:

2

Conclusion

Throughout this tutorial, we’ve explored various techniques for building JSON arrays in PostgreSQL. From simple aggregations to complex nested structures, PostgreSQL provides robust and flexible options for working with JSON data. Leveraging these capabilities can significantly enhance the ways your applications interact with database layers, offering new possibilities for data representation and manipulation.