Modify JSON Fields in PostgreSQL 9.3

To modify fields within a JSON object stored in a PostgreSQL 9.3 database, you can use the following methods:

Using the ->> operator:

The ->> operator extracts the value of a specific field within a JSON object. You can use this operator in conjunction with the UPDATE statement to modify the value of a field.

For example, to update the "name" field of a JSON object stored in the "data" column of the "mytable" table, you would use the following query:

UPDATE mytable
SET data = jsonb_set(data, '{name}', 'New Name', false)
WHERE id = 1;

This query will update the "name" field of the JSON object stored in the row with an ID of 1 to "New Name".

Using the jsonb_set function:

The jsonb_set function allows you to modify a specific field within a JSON object by providing the path to the field and the new value.

UPDATE mytable
SET data = jsonb_set(data, '{name}', 'New Name')
WHERE id = 1;

This query is equivalent to the query using the ->> operator.

The jsonb_insert function allows you to insert a new field into a JSON object or update an existing field.

UPDATE mytable
SET data = jsonb_insert(data, '{age}', 30)
WHERE id = 1;

If the "age" field already exists, this query will update its value to 30.

The jsonb_delete function allows you to delete a specific field from a JSON object.

UPDATE mytable
SET data = jsonb_delete(data, '{name}')
WHERE id = 1;

UPDATE mytable
SET data = jsonb_set(data, '{name}', 'New Name', false)
WHERE id = 1;

This query updates the "name" field of a JSON object stored in the "data" column of the "mytable" table to "New Name".

UPDATE mytable
SET data = jsonb_set(data, '{name}', 'New Name')
WHERE id = 1;

UPDATE mytable
SET data = jsonb_insert(data, '{age}', 30)
WHERE id = 1;

UPDATE mytable
SET data = jsonb_delete(data, '{name}')
WHERE id = 1;

Here are some additional examples:

• Updating a nested field:

UPDATE mytable
SET data = jsonb_set(data, '{address,street}', 'New Street')
WHERE id = 1;

• Adding a new element to an array:

UPDATE mytable
SET data = jsonb_insert(data, '{colors,-1}', 'blue')
WHERE id = 1;

This query adds the element "blue" to the end of the "colors" array within a JSON object stored in the "data" column of the "mytable" table.

• Removing an element from an array:

UPDATE mytable
SET data = jsonb_delete(data, '{colors,1}')
WHERE id = 1;

Instead of using a string path, you can use a path array to specify the path to the field you want to modify. This can be more readable for complex paths.

UPDATE mytable
SET data = jsonb_set(data, '{address,street}', 'New Street')
WHERE id = 1;

The fourth argument to the jsonb_set function is an optional boolean flag. If this flag is set to true, the function will create the path if it does not exist.

UPDATE mytable
SET data = jsonb_set(data, '{age}', 30, true)
WHERE id = 1;

The jsonb_update function is similar to the jsonb_set function, but it allows you to update multiple fields within a JSON object in a single operation.

UPDATE mytable
SET data = jsonb_update(data, '{name,age}', '{"New Name",30}')
WHERE id = 1;

The jsonb_populate_record function allows you to populate a PostgreSQL record with the values from a JSON object. This can be useful for converting JSON data into a relational format.

For example, to populate a record of the "mytable" table with the values from a JSON object stored in the "data" column, you would use the following query:

UPDATE mytable
SET data = jsonb_populate_record(mytable, data)
WHERE id = 1;