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:

1. Construct Your Query:

2. 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.

3. Aggregate JSON Objects into Array:

   • To get all rows as a JSON array, use array_agg() to aggregate the JSON objects created by row_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.

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 and name columns.
• Using aliases (id to product_id, name remains the same).
• Casting price to a string using TO_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 and name.

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 with row_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.