Crafting JSON Arrays from PostgreSQL: A Step-by-Step Guide
When working with PostgreSQL and APIs or data exchange formats, you might often need to convert your query results into a JSON array format. This is because JSON is a widely used, lightweight, and human-readable data format that's perfect for transmitting data between systems or applications.
The Approach:
PostgreSQL provides two key functions to achieve this:
Steps to Convert Result Set to JSON Array:
Construct Your Query:
Convert Each Row to JSON Object:
- Within the SELECT clause, use
row_to_json()
on each row. You can specify which columns to include and how to format them (e.g., column aliases).
SELECT row_to_json(t) AS json_data FROM your_table t;
This will return a single row as a JSON object for each row in your table.
- Within the SELECT clause, use
Aggregate JSON Objects into Array:
- To get all rows as a JSON array, use
array_agg()
to aggregate the JSON objects created byrow_to_json()
.
SELECT array_agg(row_to_json(t)) AS json_array FROM your_table t;
This will return a single JSON array containing all rows from your table as JSON objects.
- To get all rows as a JSON array, use
Example:
Assuming you have a table products
with columns id
, name
, and price
:
SELECT array_agg(row_to_json(p)) AS json_array
FROM products p;
This will output a JSON array like:
[
{"id": 1, "name": "Product A", "price": 10.99},
{"id": 2, "name": "Product B", "price": 15.50},
... (other products)
]
Additional Considerations:
- You can customize the JSON output using options within
row_to_json()
. Refer to the PostgreSQL documentation for details. - For complex data structures or nested objects, you might need to explore more advanced techniques like custom JSON functions or PL/pgSQL procedures.
-- Sample table (products)
CREATE TABLE products (
id SERIAL PRIMARY KEY,
name VARCHAR(50) NOT NULL,
price DECIMAL(5,2) NOT NULL
);
-- Insert some data
INSERT INTO products (name, price) VALUES ('Product A', 10.99), ('Product B', 15.50), ('Product C', 22.75);
-- Convert all rows to JSON array
SELECT array_agg(row_to_json(p)) AS json_array
FROM products p;
This code will output a JSON array containing all products from the products
table:
[
{"id": 1, "name": "Product A", "price": 10.99},
{"id": 2, "name": "Product B", "price": 15.50},
{"id": 3, "name": "Product C", "price": 22.75}
]
Example 2: Selecting Specific Columns and Formatting
SELECT array_agg(
row_to_json(
-- Include only 'id' and 'name' with aliases
-- Cast 'price' to string for consistency
-- Add line breaks between objects
TO_CHAR(p.price, 'FM99G999G999O') AS formatted_price
) FROM (
SELECT id, name, price
FROM products
) AS p
) AS json_array_formatted
FROM products;
This code modifies the JSON output by:
- Selecting only
id
andname
columns. - Using aliases (
id
toproduct_id
,name
remains the same). - Casting
price
to a string usingTO_CHAR
for consistency. - Adding line breaks (
\n
) between objects with the||
operator.
The resulting JSON array will look like:
[
{"product_id": 1, "name": "Product A", "formatted_price": "10.99"},
{"product_id": 2, "name": "Product B", "formatted_price": "15.50"},
{"product_id": 3, "name": "Product C", "formatted_price": "22.75"}
]
Example 3: Conditional Formatting (PostgreSQL 9.4 or later)
-- Assuming a 'category' column exists in the 'products' table
SELECT array_agg(
row_to_json(
CASE WHEN p.category = 'electronics' THEN
-- Include all columns for electronics
p
ELSE
-- Include only 'id' and 'name' for other categories
(p.id, p.name)
END
)
) AS json_array_conditional
FROM products p;
This code showcases conditional formatting based on the category
column (available in PostgreSQL 9.4 or later). It creates a JSON array where:
- Products in the
electronics
category will have all columns included. - Products in other categories will only have
id
andname
.
The resulting JSON array will vary depending on your data, but it will demonstrate how to format objects differently based on conditions.
This function allows you to construct a JSON array element by element. It provides more granular control over the structure of the resulting array.
SELECT json_build_array(
json_build_object(
'id', p.id,
'name', p.name,
'price', TO_CHAR(p.price, 'FM99G999G999O')
)
FOR p IN (
SELECT id, name, price
FROM products
)
) AS json_array_custom
FROM products;
This code:
- Uses
json_build_object
to create JSON objects for each row. - Specifies key-value pairs for each column.
- Uses a subquery to retrieve the desired data.
- Finally,
json_build_array
aggregates these objects into a JSON array.
Using PL/pgSQL Procedures (for complex scenarios):
For highly customized JSON output or complex data manipulation, you can create PL/pgSQL procedures. These procedures allow you to write procedural code within PostgreSQL to handle the conversion logic. Here's a basic example structure:
CREATE OR REPLACE FUNCTION products_to_json_array(OUT result json) AS $$
BEGIN
-- Logic to iterate through rows and construct JSON objects
-- Use FOR loop or cursors to process each row
-- Build JSON objects and add them to an array variable
RETURN array_to_json(json_array_variable);
END;
$$ LANGUAGE plpgsql;
SELECT products_to_json_array(result);
This is a simplified example, and you'll need to write the specific logic within the procedure to handle your data and desired JSON structure.
Choosing the Right Method:
- For simple conversions where you want to include all columns as-is,
array_agg
withrow_to_json
is a straightforward approach. - If you need more control over the JSON structure or want to conditionally format the output, using
json_build_array
offers more flexibility. - When dealing with very complex data transformations or require custom logic, PL/pgSQL procedures provide the most customization but also require more development effort.
json postgresql