Schema Evolution with JSON-Relational Duality Views
Use duality views to evolve relational schemas while keeping older and newer JSON document APIs compatible.
Schema evolution is the process of changing a data model while keeping existing applications working.
In real systems, data APIs do not stay fixed. Fields are added. Fields are removed. Values are reformatted. Embedded data becomes referenced data. Scalar fields become arrays. Flat structures become nested structures. A simple document can gradually become a richer business object with more relationships, more structure, and more rules.
The hard part is not only changing the schema. The hard part is keeping old and new APIs usable at the same time.
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.
This is the compatibility problem this chapter explores.
JSON-relational duality views provide a useful boundary between the application-facing JSON API and the relational storage model. The application can continue to work with JSON documents, while the database maps those documents to one or more relational tables.
That separation matters because the logical document shape and the physical schema can evolve independently.
| Layer | Meaning |
|---|---|
| Application-facing API | The JSON shape that a client reads and writes. |
| Physical schema | The relational tables, columns, keys, and relationships that store the data. |
| Duality view | The mapping layer that connects the JSON API to the relational schema. |
| Compatibility API | An older JSON shape that must continue working after the schema evolves. |
| Evolved API | A newer JSON shape introduced by the current evolution. |
A successful evolution needs both read 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 newer storage model.
That is why the examples in this chapter focus on both directions:
- How older clients continue to read the fields and structures they expect.
- How older clients can still write valid documents after the relational schema changes.
- How newer clients get access to the cleaner or richer evolved API.
- Where a transformation is safe, reversible, generated, defaulted, or limited.
The topics in this chapter build a small business from a lemonade kiosk toward a fast-food chain model. The story is simple, but the evolution patterns are general.
| Evolution pattern | Example in this chapter |
|---|---|
| Additive change | Add product_type and tags. |
| Subtractive change | Remove price_per_cup from the newer API. |
| Representational change | Store location in one format while exposing another format. |
| Field split | Split location into stand_pin and stand_address. |
| Normalization | Move customer details into a customers table. |
| Scalar-to-object-array change | Move product fields into lineitems. |
| Additional nesting | Add item_categories above lineitems. |
| Scalar-to-scalar-array change | Move tags into lineitem_tags and expose them as a scalar array. |
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 that the old and new document APIs need to handle.
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, and scalar arrays.
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.