Apprendre Advanced JSON Manipulation

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.


              1234567
            
UPDATE user_profiles
SET profile_data = jsonb_set(profile_data::jsonb, '{age}', '26', false)
WHERE user_id = 1;

SELECT user_id, profile_data
FROM user_profiles
WHERE user_id = 1;

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.


              1234567891011
            
UPDATE user_profiles
SET profile_data = jsonb_set(
    profile_data::jsonb,
    '{interests}',
    (profile_data->'interests')::jsonb || '"photography"'
)
WHERE user_id = 2;

SELECT user_id, profile_data
FROM user_profiles
WHERE user_id = 2;

Tout était clair ?

Merci pour vos commentaires !

Section 6. Chapitre 4

Demandez à l'IA

Posez n'importe quelle question ou essayez l'une des questions suggérées pour commencer notre discussion

Glissez pour afficher le menu


              1234567
            
UPDATE user_profiles
SET profile_data = jsonb_set(profile_data::jsonb, '{age}', '26', false)
WHERE user_id = 1;

SELECT user_id, profile_data
FROM user_profiles
WHERE user_id = 1;

Constructing New JSON Objects and Arrays in PostgreSQL

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

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.


              1234567891011
            
UPDATE user_profiles
SET profile_data = jsonb_set(
    profile_data::jsonb,
    '{interests}',
    (profile_data->'interests')::jsonb || '"photography"'
)
WHERE user_id = 2;

SELECT user_id, profile_data
FROM user_profiles
WHERE user_id = 2;

Tout était clair ?

Merci pour vos commentaires !

Section 6. Chapitre 4

Advanced JSON Manipulation

Constructing New JSON Objects and Arrays in PostgreSQL

jsonb_build_object

jsonb_build_array

Practical Uses

1. Which PostgreSQL function is used to update or add a key-value pair in a JSONB column?

2. What operator in PostgreSQL is used to remove a key from a JSONB object?

3. Which function would you use to construct a JSON object from key-value pairs in PostgreSQL?

Advanced JSON Manipulation

Constructing New JSON Objects and Arrays in PostgreSQL

jsonb_build_object

jsonb_build_array

Practical Uses

1. Which PostgreSQL function is used to update or add a key-value pair in a JSONB column?

2. What operator in PostgreSQL is used to remove a key from a JSONB object?

3. Which function would you use to construct a JSON object from key-value pairs in PostgreSQL?