Schema Evolution with JSON-Relational Duality Views
Use JSON-relational duality views to evolve relational schemas while keeping older and newer JSON document APIs compatible.
Businesses evolve. Their APIs evolve. Their storage evolves. Those changes rarely happen at the same speed.
Schema evolution is the discipline of allowing the business model, the JSON API, and the canonical storage model to evolve in a controlled way while preserving compatibility for existing applications.
An older client may still send the document shape it already understands. A newer client may expect a richer document shape that reflects the evolved business model. Both clients may need to read and write against the same logical data set.
JSON-relational duality views help preserve API compatibility as relational schemas evolve. They separate the application-facing JSON document from the canonical storage model, allowing applications to continue using familiar document shapes while the database evolves its relational representation.
| Layer | Meaning |
|---|---|
| Compatibility API | The older JSON shape that must continue working after the schema evolves. |
| Evolved API | The newer JSON shape introduced by the current evolution. |
| Duality view | The mapping layer that connects the JSON API to the canonical storage model. |
| Canonical storage model | The relational tables, columns, keys, and relationships that store the authoritative business data. |
A successful evolution needs both read compatibility and write compatibility.
On read, the database may need to present newer stored data in an older shape. On write, the database may need to accept an older document and translate it into the canonical storage model.
The examples in this chapter are organized into four families of schema evolution:
| Evolution family | What changes |
|---|---|
| Field evolution | A field is added, removed, reformatted, or split. |
| Relationship evolution | Embedded data becomes shared data. |
| Cardinality evolution | One value becomes many values, or one object becomes many objects. |
| Hierarchy evolution | A new parent level is added above an existing collection. |
The chapter ends with a different kind of choice: using a JSON flex field backed by a JSON column when the schema is still intentionally fluid. This is not one of the four evolution families. It is a way to defer structure until the business shape becomes stable enough to model directly. Treat it as a deliberate staging area rather than a permanent destination. Attributes that stabilize should graduate into the canonical storage model, where they gain constraints, indexes, and a defined contract.
The goal is not to hide every schema change. The goal is to make each change explicit, controlled, and compatible.
Some transformations are simple. A missing field can be supplied by a default. Some transformations are stronger. A field may need to be generated, hidden, split, normalized, or routed through an intermediate structure. Some transformations have limits. If an old API does not carry enough identity or context, only a narrow compatibility path may be safe.
This chapter treats those limits as part of the design. Compatibility is not only about proving that the new view works. It is also about knowing exactly what the old API can still promise.
Example Scenario and View Names
This chapter walks through schema evolution with Oracle JSON-relational duality views. It shows how a relational schema can change over time while different JSON document APIs continue to work over the same underlying data.
The examples follow Dave, a business owner who starts with a small lemonade kiosk and gradually grows toward a fast-food chain model. Each step in that growth introduces a schema change, a compatibility strategy, and a clearer canonical storage model.
The documentation uses simplified view names to separate the concept from the concrete script implementation.
| Documentation view | Meaning |
|---|---|
sales_view |
Introductory duality view used only in Create the Initial Sales Duality View to explain the basic JSON-to-relational shape. |
sales_v1 |
Older compatibility API. This is the API that existing systems depend on. |
sales_v2 |
Newer evolved API introduced by the current scenario. |
Each topic introduces one evolution pattern, explains how the JSON document shape changes, and calls out the mechanism that makes the change possible. These mechanisms include default values, generated fields, hidden helper fields, virtual columns, unnesting, limit-based read compatibility, nested arrays, scalar arrays, and JSON flex fields.
The following index maps each topic to the pattern it demonstrates, the mechanism that implements it, and the cost that mechanism carries.
| Topic | Pattern | Mechanism | What it costs |
|---|---|---|---|
| Add product fields | Additive evolution | Column defaults | New fields without default values cannot be made mandatory while older clients continue to be writable. |
| Remove stand pricing | Staged field removal | @generated compatibility field |
After the column is dropped, the old field can only be constant, derived, or ignored. |
| Represent location data | Representation change | Transformation, dual columns, or virtual column | Duplicated storage carries a permanent consistency obligation, if chosen. |
| Split location into parts | Structural decomposition | Virtual column over split attributes | A parsing rule outlives the migration and binds every write path. |
| Move customer data | Entity extraction | Shared referenced table | Edits through the old shape reach every sale sharing that customer. |
| Move product fields to line items | Cardinality evolution | is_primary compatibility row |
The old API addresses one designated row and cannot see the rest. |
| Add categories above line items | Hierarchy evolution | Hidden parent level | A flat document cannot address a grouped structure. |
| Convert tag to scalar array | Scalar cardinality evolution | Designated compatibility tag | Writing the scalar field leaves the other tags untouched. |
| Use a JSON type column | Deferred structure | JSON flex field | Flexible attributes gain no constraints, or relationships until modeled. |
This chapter is divided into topics, and some topics can have cases, which solve the same problem using a different strategy. Each case should be read as starting from the state produced by the previous topic, not from the previous case.