Split a Field into Multiple Fields

Split one combined location field into structured address fields while preserving the compatibility API.

Decomposing one business field into independently meaningful attributes.

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 evolved API should treat stand location as two independently meaningful attributes:

stand_pin
stand_address

Older clients still expect one location field.

Best Practice

Split a field only when its components have become independent business concepts.

If applications need to search, validate, index, aggregate, or update the components separately, make those components part of the canonical storage model. Treat the original combined value as a compatibility representation.

Evolution Goal

This scenario demonstrates structural decomposition: one combined field becomes multiple stored attributes.

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 overall business meaning is still stand location, but the canonical structure is now more precise.

This is not merely a display change. The business now needs to manage, validate, and query the components independently.

Design Decision

This evolution changes the canonical storage model for stand location.

Originally, the database treated location as one stored attribute. After the evolution, stand_pin and stand_address become independent attributes with distinct validation, indexing, and query requirements.

The compatibility API continues to expose one combined location field. The evolved API works directly with the structured attributes. Compatibility is preserved by deriving and decomposing the old shape rather than retaining it as the authoritative storage model.

Preprocessing

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

The goal of preprocessing is not merely to parse text. It is to migrate the canonical storage model from one combined value to two independently managed attributes.

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 required by sales_v2. The stand_pin is extracted from the beginning of location, and the stand_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 part of the canonical stored representation. It is generated from pin and address. This lets sales_v1 preserve the old single-field shape while sales_v2 works with the structured attributes directly.

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 key architectural decision is that the structured representation becomes the canonical storage model. sales_v1 preserves the original single-field contract, while sales_v2 exposes the evolved business structure directly.

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.
`stands.pin`	Real stored pin value used by `sales_v2`.
`stands.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 a stronger transformation than representation evolution. The old field is not merely reformatted; it is decomposed into multiple stored attributes.

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.

Recommendation

This pattern is appropriate only when the split reflects a genuine business-model evolution rather than a presentation preference.

Best when

The individual components have independent business meaning.
Applications query or validate the components separately.
The structured representation is expected to become the long-term storage model.

Avoid when

The split is arbitrary.
The components are never used independently.
The combined value remains the real business concept.

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 canonical storage model now consists of pin and address. Any side-band DML should write those split columns directly. If it accepts a combined location value, it must apply the same parsing rule before updating storage.

Design Trade-offs

Benefit	Cost
Components can be searched and indexed independently.	Existing data must be parsed successfully.
Validation becomes more precise.	Compatibility logic is required for older APIs.
Canonical storage reflects the business model.	Side-band DML must understand the new storage model.

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:

sales_v1 continues to expose one combined lowercase location field.
sales_v2 exposes stand_pin and stand_address separately.
Existing location data is split into pin and address.
The old combined location value is still available through a virtual column.
Older writes are decomposed into the new stored columns.
Newer writes can use the split fields directly.

Topic Takeaway

This scenario demonstrates structural decomposition: one stored business attribute becomes multiple independently meaningful attributes.

The canonical storage model evolves from a single location value to separate pin and address attributes. The original combined value survives only as a compatibility representation for existing clients.

The important design principle is to decompose data only when the components have independent business meaning. Once they do, store them separately and derive the older combined shape only for compatibility.

The cost is a parsing rule that outlives the migration. The combined value is now derived, so every write path that still accepts one, inside these views or outside them, must apply the same decomposition rule to reach the stored components.