Split a Field into Multiple Fields

Split one location field into structured address fields while keeping the older API compatible.

Splitting one field into many.

Business Step

The standardized location value is working, but it is still one combined field.

As Dave’s business moves closer to a fast-food chain model, location needs to become more useful for routing, reporting, and operations. A single string like 560001 market cart, north plaza is readable, but it is not structured enough.

The newer API should understand the stand location as two separate pieces:

Older clients still expect one location field.

Evolution Goal

This scenario splits one logical field into multiple fields.

sales_v1 remains the older compatibility API. It continues to expose location as one combined value, but that value now follows the standardized lowercase format adopted after the successful migration from the older uppercase format.

sales_v2 is the newer API. It exposes stand_pin and stand_address separately.

Documentation view Role
sales_v1 Older compatibility API. Existing clients continue to read and write one combined lowercase location field.
sales_v2 Newer API. Newer clients read and write stand_pin and stand_address separately.

Shape Change

At this point, the old shape means one combined field:

"stand_info": {
    "_id": 1,
    "location": "560001 market cart, north plaza"
}

The new location shape splits that value:

"stand_info": {
    "_id": 1,
    "stand_pin": "560001",
    "stand_address": "market cart, north plaza"
}

The business meaning is the same. The representation is now more structured.

Preprocessing

Before this split can happen, the old location value needs a predictable structure.

For this scenario, Dave has maintained stand locations in a format where the value starts with a six-character pin followed by a space, and the remaining text represents the address.

The old single-field format looks like this:

"location": "560001 market cart, north plaza"

The newer structured format separates that value into two fields:

"stand_pin": "560001",
"stand_address": "market cart, north plaza"

The preprocessing creates two real columns, pin and address, and backfills them from the existing location value using that fixed-position rule.

ALTER TABLE stands ADD (pin VARCHAR2(10));
ALTER TABLE stands ADD (address VARCHAR2(200));

UPDATE stands
SET pin = SUBSTR(location, 1, INSTR(location, ' ') - 1),
    address = TRIM(SUBSTR(location, INSTR(location, ' ') + 1));

After this step, the database has the structured values that sales_v2 needs. The pin is extracted from the beginning of location, and the address is extracted from the remaining text.

Once pin and address become the stored representation, the original location column is replaced with a virtual compatibility field.

DROP INDEX idx_old_location;
ALTER TABLE stands DROP COLUMN oldlocationformat;

ALTER TABLE stands DROP COLUMN location;
ALTER TABLE stands
  ADD (location VARCHAR2(300) GENERATED ALWAYS AS
       (pin || ' ' || address) VIRTUAL);

ALTER TABLE stands MODIFY (pin VARCHAR2(10) NOT NULL);
ALTER TABLE stands MODIFY (address VARCHAR2(200) NOT NULL);

After this step, location is no longer stored as a real column. It is generated from pin and address. This lets sales_v1 continue exposing the old single-field shape, while sales_v2 works with the real structured fields.

sales_v1 Compatibility View

sales_v1 keeps the older location API alive.

Older clients still see one location field. By this point, all systems have migrated to the standardized lowercase location format introduced earlier. On read, the view returns the combined location value from pin and address. On write, hidden helper fields split the incoming lowercase location string into pin and address.

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,
    stand_pin: pin @hidden @generated(path: "$.stand_info.location.substr(0, 6)", on: "write"),
    stand_address: address @hidden @generated(path: "$.stand_info.location.substr(7)", on: "write"),
    price_per_cup @generated(sql: "2.75")
  }
};

sales_v2 Evolved View

sales_v2 exposes the split location fields directly.

Newer clients no longer need to parse a combined location string. They can work with stand_pin and stand_address as separate fields.

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,
    stand_pin: pin,
    stand_address: address
  }
};

The newer document shape looks like this:

{
    "_id": 16,
    "date": "2026-01-26",
    "quantity": 9,
    "amount": 33.75,
    "product_type": "Yuzu Lemonade",
    "tags": "seasonal",
    "customer_name": "lena",
    "email_address": "lena@example.com",
    "stand_info": {
        "_id": 8,
        "stand_pin": "560008",
        "stand_address": "river cart, east bank"
    }
}

Evolution Thought Process

The important idea in this scenario is that sales_v1 still owns one visible location field, while sales_v2 owns the split fields. The compatibility being preserved is the single-field shape.

sales_v1 exposes location because older clients understand location as a single value. The hidden fields stand_pin and stand_address are not part of the JSON response, but they participate during writes. They extract pieces from the incoming location string and store them in the real pin and address columns.

sales_v2 exposes the real split columns directly. Newer clients send stand_pin and stand_address separately, so no extraction is needed.

Field or column Purpose
location Virtual compatibility field that combines pin and address.
pin Real stored pin value used by sales_v2.
address Real stored address value used by sales_v2.
stand_pin Hidden helper in sales_v1 that extracts the pin from the old location string.
stand_address Hidden helper in sales_v1 that extracts the address from the old location string.

This is the same compatibility idea as earlier topics, but the transformation is stronger. The old field is not only reformatted. It is decomposed into multiple stored fields.

Behavior

API or schema object Behavior
sales_v1.location Reads the combined location value from pin and address.
sales_v2.stand_pin Reads and writes the pin column directly.
sales_v2.stand_address Reads and writes the address column directly.
stands.location Virtual field generated from pin and address.
stands.pin Required stored column after the split.
stands.address Required stored column after the split.

When to use this strategy

Use this strategy when one field has become too dense and newer systems need its parts separately. It works best when the old field can be split reliably, such as a fixed pin followed by address text.

Limitation

This approach depends on a reliable split rule. In this scenario, the value starts with a six-character pin followed by a space, and the remaining text represents the address. If the old location values are inconsistent, the split can produce bad data.

The old location field becomes a compatibility shape. The real stored model is now pin and address. Any side-band DML should write the split columns directly. If it writes a combined location value, it must apply the same fixed pin-plus-address parsing rule before updating storage.

New Behavior Used

This scenario introduces decomposition of one JSON field into multiple stored fields.

Feature Why it matters
Virtual column Recreates the old combined location field from pin and address.
@hidden Keeps split helper fields out of the sales_v1 JSON API.
@generated with on: “write” Extracts pin and address from the old location string during sales_v1 DML.

Evolution Check

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

Topic Takeaway

This scenario turns one field into multiple fields without breaking the old document API.

Older clients still see one lowercase location field. Newer clients get stand_pin and stand_address. The database stores the structured version and derives the old combined value when needed.

This is a useful pattern when the business outgrows a compact text field and needs the database to understand the pieces inside it.