Postgres: Update mutation
Auto-generated update mutation schema
For example, the auto-generated schema for the update mutation field
for a table article looks like the following:
update_article (
_inc: article_inc_input
_set: article_set_input
where: article_bool_exp!
): article_mutation_response
# response of any mutation on the table "article"
type article_mutation_response {
# number of affected rows by the mutation
affected_rows: Int!
# data of the affected rows by the mutation
returning: [article!]!
}
# single object update (supported from v1.2.0)
update_article_by_pk (
_inc: article_inc_input
_set: article_set_input
# primary key columns arg
pk_columns: article_pk_columns_input!
): article
As you can see from the schema:
- The
whereargument is compulsory to filter rows to be updated. See Filter queries for filtering options. Objects can be updated based on filters on their own fields or those in their nested objects. The{}expression can be used to update all rows. - You can return the number of affected rows and the affected objects (with nested objects) in the response.
See the update mutation API reference for the full specifications.
Note
- At least any one of
_set,_incoperators or the jsonb operators_append,_prepend,_delete_key,_delete_elem,_delete_at_pathis required. - If a table is not in the
publicPostgres schema, the update mutation field will be of the formatupdate_<schema_name>_<table_name>.
Update an object by its primary key
You can update a single object in a table using the primary key. The
output type is the nullable table object. The mutation returns the
updated row object or null if the row does not exist.
Example: Update an article where id is 1:
Example: Update a non-existent article:
Note
update_<table>_by_pk will only be available if you have select
permissions on the table, as it returns the updated row.
Supported from
The update_<table>_by_pk mutation is supported in versions v1.2.0
and above.
Update objects based on their fields
Example: Update the rating and is_published of articles with a
low rating:
Using variables:
OR
Update objects based on nested objects' fields
Example: Reset the rating of all articles authored by "Sidney":
Update all objects
You can update all objects in a table using the {} expression as the
where argument. {} basically evaluates to true for all objects.
Example: Reset rating of all articles:
Increment/Decrement int columns
You can increment/decrement an int column with a given value using the
_inc operator.
Example: Increment the likes of an article by 2:
Example: Decrement the likes of an article by 2:
Update jsonb columns
The currently available jsonb operators are:
| Operator | Postgres equivalent | Function |
|---|---|---|
_append | || | append json value to a jsonb column |
_prepend | || | prepend json value to a jsonb column |
_delete_key | - | delete top-level key from jsonb column |
_delete_elem | - | delete array element from jsonb column |
_delete_at_path | #- | delete element at a path from jsonb column |
Note
You can learn more about Postgres jsonb operators here.
Examples
Append a json to a jsonb column
You can append any jsonb column with another json value by using the
_append operator.
Since the input is a json value, it should be provided through a variable.
Example: Append the json {"key1": "value1"} to the jsonb column
extra_info of the article table:
Prepend a json to a jsonb column
You can prepend any jsonb column with another json value by using the
_prepend operator.
Since the input is a json value, it should be provided through a variable.
Example: Prepend the json {"key0": "value0"} to the jsonb column
extra_info of the article table:
Delete a top-level key from a jsonb column
You can delete a top-level key of a jsonb column by using the
_delete_key operator.
The input value should be a String.
Example: Delete the key key in the jsonb column extra_info of
the article table:
Delete an element from a jsonb column storing a json array
If a jsonb column is storing a json array, you can delete an element
from the array using the _delete_elem operator.
The input value should be an Int.
Example: Delete the element at position 2 in the array value of the
jsonb column extra_info of the article table:
Delete an element at a specific path in a jsonb column
You can delete a field or element of a jsonb column at a specified
path by using the _delete_at_path operator.
The input value should be a String Array.
Example: Delete element at json path name.last in the jsonb
column extra_info of the author table:
Replace all nested array objects of an object
In order to replace all existing nested array objects of an object, currently it's required to use two mutations: one to delete all the existing objects and one to add a list of new nested objects.
Example: Replace all articles of an author with a new list:
