PostgreSQL provides powerful tools for modifying JSON data directly within your tables, allowing you to keep your information up to date without replacing entire JSON documents. When you need to add or update a key-value pair in a JSON column, use the jsonb_set function. This function lets you specify the exact location in the JSON structure to insert or update a value, making targeted changes simple and efficient.

If you need to remove a key from your JSON data, use the - operator. This operator deletes a specified key from a JSON object, helping you remove outdated or unnecessary information while leaving the rest of the document untouched.

By using jsonb_set and the - operator, you can efficiently manage and update your JSON data, ensuring it always reflects the most current information without the need to rewrite the entire JSON content.

Constructing New JSON Objects and Arrays in PostgreSQL

PostgreSQL provides powerful functions for creating structured JSON data directly from your tables. The two most useful functions for this purpose are jsonb_build_object and jsonb_build_array.

jsonb_build_object

Use jsonb_build_object to create a new JSON object by specifying key-value pairs. This function is ideal when you want to generate a JSON object from columns or expressions in your query.

Key points:

• Accepts an even number of arguments: alternating between keys and values;
• Keys must be text strings; values can be columns, literals, or expressions;
• Returns a single JSON object.

Example:

Create a JSON object for each user, containing their ID and city:

SELECT jsonb_build_object(
    'user_id', user_id,
    'city', profile_data->'address'->>'city'
) AS user_summary
FROM user_profiles;

jsonb_build_array

Use jsonb_build_array to create a new JSON array from a list of values. This is useful for assembling multiple items into a single JSON array, either for output or for updating/inserting JSON fields.

Key points:

• Accepts any number of arguments;
• Arguments can be columns, literals, or expressions;
• Returns a single JSON array.

Example:

Combine a user's age and interests into a JSON array:

SELECT jsonb_build_array(
    profile_data->>'age',
    profile_data->'interests'
) AS age_and_interests
FROM user_profiles;

Practical Uses

• Generate structured JSON output for APIs or reporting directly from relational data;
• Assemble new JSON objects or arrays for updating or inserting into JSON columns;
• Simplify the process of transforming relational rows into nested JSON structures.

By using jsonb_build_object and jsonb_build_array, you can flexibly construct custom JSON documents from your SQL queries, making it easy to bridge relational and document-based data workflows.