How to Use json_build_object in PostgreSQL to Aggregate Data

PostgreSQL’s json_build_object is your go-to when you want to return structured JSON data from SQL queries, especially for APIs or for logging data. Think of it as PostgreSQL's native way of building a JSON object from key-value pairs directly from the query.

The basic syntax looks something like the following:

json_build_object(key1, value1, key2, value2, ...)

Keys must be text (or cast to a text). Values can be any SQL expression.

Example: User JSON Response

Let’s say you have a users table:

SELECT 
  json_build_object(
    'id', id,
    'name', full_name,
    'email', email,
    'created', created_at
  ) AS user_json
FROM users
WHERE id = 1;

This returns the following JSON object:

{
  "id": 1,
  "name": "Walt",
  "email": "[email protected]",
  "created": "2025-06-01T10:00:00"
}

Nested Objects

Want nested data? Just use another json_build_object inside:

SELECT json_build_object(
  'user', json_build_object(
    'id', id,
    'name', full_name
  ),
  'meta', json_build_object(
    'fetched_at', NOW()
  )
) AS result
FROM users
WHERE id = 1;

Which would return the following output:

{
  "result": {
    "user": {
      "id": 1,
      "name": "Jane Doe"
    },
    "meta": {
      "fetched_at": "2025-06-05T08:45:30.123456Z"
    }
  }
}

Aggregate Data with json_agg

Let’s say you want each user with an array of their posts returned from a single query. You can combine the json_build_object with the json_agg function as such:

SELECT 
  u.id,
  u.full_name,
  json_agg(
    json_build_object(
      'id', p.id,
      'title', p.title,
      'created_at', p.created_at
    )
  ) AS posts
FROM users u
LEFT JOIN posts p ON p.user_id = u.id
GROUP BY u.id, u.full_name;

You’ll get something like:

{
  "id": 1,
  "full_name": "Walt G.",
  "posts": [
    { "id": 10, "title": "Hello World", "created_at": "2025-06-01" },
    { "id": 11, "title": "Another Post", "created_at": "2025-06-02" }
  ]
}

Wrap It All Up

You can even wrap the whole row in json_build_object too:

SELECT json_build_object(
  'id', u.id,
  'name', u.full_name,
  'posts', json_agg(
    json_build_object(
      'id', p.id,
      'title', p.title
    )
  )
) AS user_data
FROM users u
LEFT JOIN posts p ON p.user_id = u.id
GROUP BY u.id, u.full_name;

This gives you a clean, API-ready result per row.

Tips

• NULL values will still appear in the JSON unless you filter them out manually.
• If you're dealing with arrays, consider json_agg or jsonb_agg in combination.
• For performance: don’t over-nest if you don’t have to, JSON parsing on the client side can become expensive.

When To Use It

• API endpoints via PostgREST, Hasura, or custom Node/Express backends.
• Logging structured data into a column.
• Generating JSON responses from views or materialized views.