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 where argument 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.

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:

mutation update_an_article {
  update_article_by_pk (
    pk_columns: {id: 1}
    _set: { is_published: true }
  ) {
    id
    is_published
  }
}

Example: Update a non-existent article:

mutation update_an_article {
  update_article_by_pk (
    pk_columns: {id: 100}
    _set: { is_published: true }
  ) {
    id
    is_published
  }
}

Update objects based on their fields¶

Example: Update the rating and is_published of articles with a low rating:

mutation update_article {
  update_article(
    where: {rating: {_lte: 2}},
    _set: {
      rating: 1,
      is_published: false
    }
  ) {
    affected_rows
    returning {
      id
      title
      content
      rating
      is_published
    }
  }
}

Using variables:

mutation update_article($rating: Int, $changes: article_set_input) {
  update_article(
    where: {rating: {_lte: $rating}},
    _set: $changes
  ) {
    affected_rows
    returning {
      id
      title
      content
      rating
      is_published
    }
  }
}

{
  "rating": 2,
  "changes": {
    "rating": 1,
    "is_published": false,
  }
}

OR

mutation update_article($ratingLimit: Int, $rating: Int, $isPublished: Boolean) {
  update_article(
    where: {rating: {_lte: $ratingLimit}},
    _set: {
      rating: $rating,
      is_published: $isPublished
    }
  ) {
    affected_rows
    returning {
      id
      title
      content
      rating
      is_published
    }
  }
}

{
  "ratingLimit": 2,
  "rating": 1,
  "isPublished": false
}

Update objects based on nested objects’ fields¶

Example: Reset the rating of all articles authored by “Sidney”:

mutation update_ratings {
  update_article(
    where: {author: {name: {_eq: "Sidney"}}},
    _set: {rating: null}
  ) {
    affected_rows
  }
}

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:

mutation reset_rating {
  update_article (
    where: {}
    _set: { rating: null }
  ) {
    affected_rows
  }
}

Increment int columns¶

You can increment an int column with a given value using the _inc operator.

Example: Increment the likes of an article by 2:

mutation update_likes {
  update_article(
    where: {id: {_eq: 1}},
    _inc: {likes: 2}  # initial value: 1
  ) {
    affected_rows
    returning {
      id
      likes
    }
  }
}

Update jsonb columns¶

The currently available jsonb operators are:

mutation update_extra_info($value: jsonb) {
  update_article(
    where: {id: {_eq: 1}},
    _append: {extra_info: $value}  # initial value: {"key": "value"}
  ) {
    affected_rows
    returning {
      id
      extra_info
    }
  }
}

{
  "value": { "key1": "value1" }
}

mutation update_extra_info($value: jsonb) {
  update_article(
    where: {id: {_eq: 1}},
    _prepend: {extra_info: $value}  # initial value "{"key": "value", "key1": "value1"}"
  ) {
    affected_rows
    returning {
      id
      extra_info
    }
  }
}

{
  "value": { "key0": "value0" }
}

mutation update_extra_info {
  update_article(
    where: {id: {_eq: 1}},
    _delete_key: {extra_info: "key"}  # initial value "{"key0": "value0, "key": "value", "key1": "value1"}"
  ) {
    affected_rows
    returning {
      id
      extra_info
    }
  }
}

mutation update_extra_info {
  update_article(
    where: {id: {_eq: 1}},
    _delete_elem: {extra_info: 2}  # initial value "["a", "b", "c"]"
  ) {
    affected_rows
    returning {
      id
      extra_info
    }
  }
}

mutation update_extra_info {
  update_author(
    where: {id: {_eq: 1}},
    _delete_at_path: {extra_info: ["name", "first"]}  # initial value "{"name": {"first": "first_name", "last": "last_name"}}"
  ) {
    affected_rows
    returning {
      id
      extra_info
    }
  }
}

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:

mutation updateAuthorArticles($author_id: Int!) {
  delete_articles(
    where: {author_id: {_eq: $author_id}}
  ) {
    affected_rows
  }
  insert_articles(
    objects: [
      {
        author_id: $author_id,
        title: "title",
        content: "some content"
      },
      {
        author_id: $author_id,
        title: "another title",
        content: "some other content"
      }
    ]
  ) {
    affected_rows
  }
}

{
  "author_id": 21
}