TIL you can rename PostgreSQL JSONB keys in a query

Oliver Peate posted on May 24th, 2019

Using the delete - and concatenate || operators - here’s an ActiveRecord example:

key_before = "old_name"
key_after = "new_name"

MyModel.where(["metadata ? :key_before", { key_before: key_before }])
       .update_all(
         [
           "metadata = metadata - :key_before || jsonb_build_object(:key_after, metadata->:key_before)", 
           { key_before: key_before, key_after: key_after }
         ]
       )

In this example metadata is the name of the JSONB column.

The WHERE condition limits the query to rows which have the old key set. The UPDATE condition deletes the old key from the object, then concatenates the result after deletion with another object which has the new key set with the same value.

Because PostgreSQL uses ? as the operator to check if a JSON column has a key (which is also the placeholder for SQL parameter interpolation) we have to use named-placedholders instead:

# ? Placeholder
MyModel.where("metadata ? ?", "foo")

# Named-placeholders
MyModel.where("metadata ? :param", param: "foo")

References:

What did you learn today?

Write a quick TIL post to reflect on what you've learned.

Write a post