Reformat a Field in an API

Represent location data in different formats while compatibility and evolved APIs share the same logical data.

Reformatting a field in the API.

Business Step

Dave now has more stands across more locations.

In the early system, stand locations were stored as display text. A value like MARKET CART, NORTH PLAZA was good enough when the business had a few carts. As the business moves closer to a fast-food chain model, the location value needs a more consistent internal representation.

The field is still location. The meaning is still “where the stand is.” What changes is the format expected by newer systems.

Evolution Goal

This scenario changes how location is represented.

sales_v1 remains the older compatibility API. It continues to expose the location value in the format older clients expect.

sales_v2 is the newer API. It exposes the same business field in the new standardized format.

Documentation view Role
sales_v1 Older compatibility API. Existing clients continue to see the old location format.
sales_v2 Newer API. Newer clients see the standardized location format.

There are three ways to solve this kind of field-format change:

Case Approach
Case 1 Change the existing column to the new format and make sales_v1 translate values for older clients.
Case 2 Add a second column for the new format and keep both formats stored during the transition.
Case 3 Store only the new format and use a virtual column to expose the old format for compatibility.

Standardization Used in This Scenario

For this scenario, the location standardization is simple: Dave wants the stored location value to move from uppercase display text to lowercase standardized text.

The old format looks like this:

"location": "MARKET CART, NORTH PLAZA"

The new format looks like this:

"location": "market cart, north plaza"

sales_v1 continues to return the uppercase value because older clients already depend on that format. sales_v2 returns the lowercase value because newer systems are expected to use the standardized representation.

The same pattern can apply to more complex field-format changes, but the safety of the approach depends on whether the old and new formats can be converted reliably.

Case 1: Change the existing column

In the first approach, the same location column is reused.

The stored value is converted to the new standard.

UPDATE stands SET location = LOWER(location);

After this change, the base table stores location in lowercase. The newer API can expose that value directly.

sales_v1 Compatibility View

Older clients still expect uppercase location values. The compatibility view reads the lowercase stored value and returns uppercase. It also uses a hidden write helper so older uppercase input is normalized into lowercase storage.

CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW sales_v1 AS
sales @insert @update @delete
{
  _id: sale_id,
  date: sale_date,
  quantity,
  amount,
  product_type,
  tags,
  customer_name,
  email_address,
  stand_info: stands @insert @update @nodelete
  {
    _id: stand_id,
    location @generated(sql: "UPPER(location)", on: "read"),
    loc_write: location @hidden @generated(path: "$.stand_info.location.lower()", on: "write"),
    price_per_cup @generated(sql: "2.75")
  }
};

sales_v2 Evolved View

The newer view exposes the lowercase value directly from the base table.

CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW sales_v2 AS
sales @insert @update @delete
{
  _id: sale_id,
  date: sale_date,
  quantity,
  amount,
  product_type,
  tags,
  customer_name,
  email_address,
  stand_info: stands @insert @update @nodelete
  {
    _id: stand_id,
    location
  }
};

The newer document shape looks like this:

{
    "_id": 10,
    "date": "2026-01-20",
    "quantity": 9,
    "amount": 36.00,
    "product_type": "Peach Lemonade",
    "tags": "seasonal",
    "customer_name": "kevin",
    "email_address": "kevin@example.com",
    "stand_info": {
        "_id": 5,
        "location": "garden cart, south lawn"
    }
}

Case 1 Behavior

API or schema object Behavior
sales_v1 Returns location in the older uppercase format.
sales_v2 Returns location in the new lowercase format.
stands.location Stores the lowercase value.
loc_write Hidden helper used to normalize older writes into lowercase storage.

This approach is simple because there is still only one stored location column. The cost is that the older API depends on transformation logic in the view.

When to use this strategy

Use this approach when the older format can be reliably produced from the newer stored value. Simple transformations, such as uppercase to lowercase, are good candidates. More complex transformations need extra care because the old format may not be safely reversible.

Case 1 Limitation

If older clients filter heavily on location, generated expressions may need additional indexing or query support to avoid slow lookups.

Case 2: Store both formats during the transition

The second approach keeps the old value and adds a new column for the standardized value.

In this case, stands.location continues to hold the old uppercase format used by sales_v1. A new column, formattedlocation, is added to hold the lowercase format used by sales_v2.

This gives each API its own physical column during the transition. sales_v1 can continue reading and writing the old format, while sales_v2 can read and write the new format.

Preprocessing note

Before this case starts, stands.location is still the old-format column. Its values are uppercase.

The preprocessing keeps the old location value and creates a new column for the standardized value.

ALTER TABLE stands ADD (formattedlocation VARCHAR2(100));

UPDATE stands SET formattedlocation = LOWER(location);

After this preprocessing, stands.location still supports sales_v1 with the uppercase format, while stands.formattedlocation is ready for sales_v2 with the lowercase format. Both APIs now have their own physical representation during the transition.

Column Purpose after preprocessing
location Keeps the old uppercase value for sales_v1.
formattedlocation Stores the new lowercase value for sales_v2.

sales_v1 Compatibility View

sales_v1 continues to expose the original location column. It also maintains formattedlocation through a hidden helper field when older clients write the old format.

CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW sales_v1 AS
sales @insert @update @delete
{
  _id: sale_id,
  date: sale_date,
  quantity,
  amount,
  product_type,
  tags,
  customer_name,
  email_address,
  stand_info: stands @insert @update @nodelete
  {
    _id: stand_id,
    location: location,
    new_location: formattedlocation @hidden @generated(path: "$.stand_info.location.lower()", on: "write"),
    price_per_cup @generated(sql: "2.75")
  }
};

sales_v2 Evolved View

sales_v2 exposes formattedlocation as the location field. It also keeps the original location column aligned through a hidden helper field.

CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW sales_v2 AS
sales @insert @update @delete
{
  _id: sale_id,
  date: sale_date,
  quantity,
  amount,
  product_type,
  tags,
  customer_name,
  email_address,
  stand_info: stands @insert @update @nodelete
  {
    _id: stand_id,
    location: formattedlocation,
    old_location: location @hidden @generated(path: "$.stand_info.location.upper()", on: "write")
  }
};

Evolution Thought Process

The important idea in this case is that each API gets the location format it expects, while the duality views keep the two physical columns aligned during DML.

sales_v1 exposes location from the original location column. That preserves the older uppercase API. The hidden field new_location writes into formattedlocation by taking the incoming sales_v1 location value and converting it to lowercase.

sales_v2 does the opposite. It exposes location from formattedlocation because the newer API expects lowercase. The hidden field old_location writes into the original location column by taking the incoming sales_v2 location value and converting it to uppercase.

So the visible field stays clean in both APIs:

The hidden generated fields handle the cross-format maintenance:

Hidden helper Used in Purpose
new_location sales_v1 Converts the older uppercase location input into lowercase and stores it in formattedlocation.
old_location sales_v2 Converts the newer lowercase location input into uppercase and stores it in location.

This is why @hidden and @generated are used together. The helper fields are not part of the JSON API, but they let the DML path maintain both storage columns from whichever API receives the write.

Case 2 Behavior

Column Meaning
location Old uppercase format used by sales_v1.
formattedlocation New lowercase format used by sales_v2.

This approach is easy to understand during migration. Each API has a column that matches the format it expects.

Case 2 Limitation

This approach duplicates the same business value in two columns. Any write path outside these duality views must also keep location and formattedlocation consistent.

The separate-column approach is useful during transition, but it can become technical debt if both columns remain permanently and always represent the same location.

Case 3: Store one value and derive the old format

The third approach keeps only the standardized value as stored data.

At this stage, Dave no longer wants two physical columns for the same stand location. The lowercase value becomes the real stored value, and the old uppercase value is derived only when older clients need it.

This gives the newer API a clean storage model while still keeping sales_v1 compatible.

UPDATE stands SET location = LOWER(location);

ALTER TABLE stands
  ADD (oldlocationformat VARCHAR2(100) GENERATED ALWAYS AS
       (UPPER(location)) VIRTUAL);

CREATE INDEX idx_old_location ON stands (oldlocationformat);

After this preprocessing, stands.location stores the lowercase standardized value. The oldlocationformat virtual column produces the uppercase value from location whenever sales_v1 needs to show the older format.

The index on oldlocationformat helps older clients if they search or filter by the old uppercase location format.

sales_v1 Compatibility View

sales_v1 maps location to the virtual uppercase column. This keeps older clients on the same location format they already understand.

At the same time, sales_v1 uses a hidden helper field to write incoming old-format values back into the real location column as lowercase.

CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW sales_v1 AS
sales @insert @update @delete
{
  _id: sale_id,
  date: sale_date,
  quantity,
  amount,
  product_type,
  tags,
  customer_name,
  email_address,
  stand_info: stands @insert @update @nodelete
  {
    _id: stand_id,
    location: oldlocationformat,
    new_location: location @hidden @generated(path: "$.stand_info.location.lower()", on: "write"),
    price_per_cup @generated(sql: "2.75")
  }
};

sales_v2 Evolved View

sales_v2 reads the stored lowercase location directly.

There is no compatibility transformation in the newer API because the base table already stores the value in the format sales_v2 expects.

CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW sales_v2 AS
sales @insert @update @delete
{
  _id: sale_id,
  date: sale_date,
  quantity,
  amount,
  product_type,
  tags,
  customer_name,
  email_address,
  stand_info: stands @insert @update @nodelete
  {
    _id: stand_id,
    location
  }
};

Evolution Thought Process

This case removes duplicated storage but keeps both APIs alive.

sales_v2 is straightforward. It exposes location directly from stands.location because that column now stores the lowercase standardized value.

sales_v1 needs more help. Older clients still expect location in uppercase, so sales_v1 reads from oldlocationformat. That value is not stored as a second real column. It is derived from stands.location through the virtual column expression.

For writes through sales_v1, the hidden field new_location keeps the real storage column aligned. Older clients send location in uppercase. The hidden generated field takes that incoming value, converts it to lowercase, and writes it into stands.location.

Field or column Purpose
location Real stored lowercase value used by sales_v2.
oldlocationformat Virtual uppercase value exposed through sales_v1.
new_location Hidden helper that normalizes sales_v1 writes into lowercase storage.

This is why the virtual column and hidden write helper work together. The virtual column protects old-format reads. The hidden helper protects new-format storage during old-API writes.

Case 3 Behavior

API or schema object Behavior
sales_v1.location Reads the derived uppercase value from oldlocationformat.
sales_v2.location Reads the stored lowercase value from location.
stands.location Stores the real standardized lowercase value.
oldlocationformat Derives the older uppercase format without storing a second copy.
new_location Hidden helper used to normalize older writes into lowercase storage.

The database stores one real location value and derives the older representation only when needed.

When to use this strategy

Use this approach when the newer format should become the long-term stored value and the older format can be reliably derived from it. This is a strong fit for reversible transformations.

Case 3 Limitation

The virtual-column approach works only when the older format can be reliably derived from the stored value. If the conversion loses information, the old API may not be accurate.

If older systems search or filter on the old format, indexing the virtual column can be important for performance.

New Behavior Used

This scenario introduces compatibility for a changed field representation.

Feature Why it matters
@generated with on: “read” Lets sales_v1 return the older format while storage uses the newer format.
@generated with on: “write” Lets writes through one API update the corresponding stored representation.
@hidden Allows helper fields to participate in the view without appearing in the JSON document.
Virtual column Provides an old-format compatibility value without storing a second real column.

Evolution Check

By the end of this scenario, the evolution proves the following:

Topic Takeaway

This scenario is not about adding or deleting a field. It is about changing the representation of a field that both APIs still need.

The JSON field is still location. sales_v1 and sales_v2 expose the same business concept, but not the same representation.

The cleanest long-term model is usually the one that stores the business value once and derives compatibility output when needed. In this scenario, that points toward the virtual-column approach when the transformation is reliable.

By the time the next scenario begins, the lowercase representation has become the shared location format. The development team was successfully able to migrate the uppercase data used by old API to lowercase format. Thus, the requirement for older uppercase representation is no longer carried forward as an active compatibility requirement for old APIs.