Reformat a Field in an API

Represent the same business field in different API formats while compatibility and evolved APIs share one canonical storage model.

Changing the representation of a business field without changing its meaning.

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 only a few carts. As the business moves closer to a fast-food chain model, the location value needs a more consistent internal representation.

The business concept is still location. What changes is only the format expected by newer systems.

Best Practice

When a business concept remains the same but its representation changes, keep the field name stable unless the meaning also changes.

If the old and new formats can be converted reliably, evolve the representation rather than inventing a second business concept. Choose a storage strategy that reflects how long the compatibility format must remain writable.

Evolution Goal

This scenario changes the representation of location without changing the business meaning of the field.

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 increasingly mature strategies for this representation change:

Translate one stored representation for older clients.
Temporarily store both representations during migration.
Standardize on one stored representation and derive the legacy representation only for compatibility.

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.

Design Decision

The destination is one canonical representation of location. The three cases differ only in how quickly the canonical storage model gets there, and each pays for that speed differently: transformation logic in the view, duplicated storage, or a derived compatibility value. The three are compared in Design Trade-offs at the end of this topic.

Standardization Used in This Scenario

For this scenario, the standardization is intentionally simple: the canonical location value moves 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 format changes, but compatibility is safe only when the required transformations are reliable and well understood.

Case 1: Change the existing column

In the first approach, the existing location column becomes the canonical storage location.

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 the canonical storage model contains only one location value. The trade-off is that the compatibility API depends on transformation logic in the view.

Case 1 Recommendation

Best when

The transformation is simple and reversible.
Storage can be migrated immediately.
The compatibility period is relatively short.

Avoid when

The old representation contains information that cannot be regenerated.
The transformation is lossy or expensive.

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 temporarily stores both the compatibility representation and the evolved representation.

In this case, stands.location continues to hold the old uppercase format used by sales_v1. A new column, stands.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 continues reading and writing the compatibility format, while sales_v2 reads and writes the evolved 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
`stands.location`	Keeps the old uppercase value for `sales_v1`.
`stands.formattedlocation`	Stores the new lowercase value for `sales_v2`.

sales_v1 Compatibility View

sales_v1 continues to expose the original stands.location column. It also maintains stands.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 stands.formattedlocation as the location field. It also keeps the original stands.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 representation it expects while the duality views keep the duplicated storage aligned during DML.

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

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

So the visible field stays clean in both APIs:

sales_v1 clients see and send location in the old uppercase format.
sales_v2 clients see and send location in the new lowercase format.

The generated helper 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 `stands.formattedlocation`.
`old_location`	`sales_v2`	Converts the newer lowercase location input into uppercase and stores it in `stands.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
`stands.location`	Old uppercase format used by `sales_v1`.
`stands.formattedlocation`	New lowercase format used by `sales_v2`.

Case 2 Recommendation

Best when

Both old and new APIs must remain fully writable.
Migration is gradual.
Rollback is still possible.

Avoid when

The duplicated columns are likely to become permanent.
Multiple write paths make synchronization difficult.

Case 2 Limitation

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

Use the separate-column approach as a migration technique, not as a long-term canonical storage model. If both columns remain permanently while always representing the same location, the duplication becomes technical debt: every future write path inherits the obligation to keep them aligned, and nothing in the schema enforces it.

Case 3: Store one value and derive the old format

The third approach stores only the standardized representation as canonical 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 evolved API a clean canonical storage model while preserving the compatibility API.

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 stands.oldlocationformat virtual column produces the uppercase value from stands.location whenever sales_v1 needs to show the older format.

The index on stands.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 stands.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 while keeping both APIs usable.

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 stands.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
`stands.location`	Real stored lowercase value used by `sales_v2`.
`stands.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 `stands.oldlocationformat`.
`sales_v2.location`	Reads the stored lowercase value from `stands.location`.
`stands.location`	Stores the real standardized lowercase value.
`stands.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 canonical location value and derives the compatibility representation only when needed.

Case 3 Recommendation

Best when

The standardized representation is the long-term storage model.
The legacy format can be derived reliably.
Compatibility is primarily for existing clients.

Avoid when

The legacy representation cannot be reconstructed exactly from the stored value.

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.

Design Trade-offs

Approach	Storage	Migration	Long-term recommendation
Case 1	One physical column	Simple	Good
Case 2	Two physical columns	Excellent	Transitional only
Case 3	One physical column + virtual compatibility column	Moderate	Preferred long-term

Case 1 is the simplest implementation. Case 2 is primarily a transition strategy. Case 3 is usually the preferred long-term architecture because it stores one canonical value and derives the compatibility representation only when required.

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:

sales_v1 continues to expose location in the older uppercase format.
sales_v2 exposes location in the standardized lowercase format.
The same business field can have different representations across APIs.
The transition can be handled by changing the existing column, storing both formats temporarily, or deriving the old format through a virtual column.
Newer systems can move to the standardized value while older systems continue using the format they already understand.

Topic Takeaway

This scenario demonstrates representation evolution: the business concept remains the same while compatibility and evolved APIs express it differently.

The canonical storage model should eventually contain one authoritative representation. Additional stored formats may be useful during migration, but they should not become permanent unless they represent distinct business meaning.

Among the three approaches presented here, the virtual-column approach in Case 3 is generally the preferred long-term design because it avoids duplicated storage while preserving compatibility for existing clients.

The important design principle is to separate business meaning from representation. Evolve the stored format toward one canonical value, and derive compatibility formats only when they are needed.