Move Fields to Nested Objects

Move product fields into line items so one sale can own a repeatable collection of products.

Changing one product field set into a repeatable collection of product objects.

Business Step

Dave’s sale document still treats product information as if every sale has only one product.

That worked when each sale meant one lemonade order. But the business is now closer to a fast-food chain model. One sale can contain multiple things: a drink, a cookie, a snack, or several product variants in the same order.

The old product fields are still useful:

product_type
tags

But they no longer belong directly to the sale as a whole. They belong to each item inside the sale.

This is a cardinality change, not a presentation change. Product data moves from one scalar field set on the sale to a repeatable collection of line-item objects.

Best Practice

Move fields into a child collection when the business concept becomes naturally repeatable.

Once individual items have their own attributes, identity, or lifecycle, the child collection should become the canonical storage model. A compatibility API may continue exposing one representative item, but it should not pretend that the collection is still truly scalar.

Evolution Goal

This scenario demonstrates cardinality evolution: product fields move out of sales and into the repeatable lineitems collection.

sales_v1 remains the older compatibility API. It continues to expose one root-level product_type and one scalar tags value. Internally, those fields come from the primary compatibility lineitem for the sale.

sales_v2 is the newer API. It exposes lineitems as an array, where each item has its own product fields.

Documentation view	Role
`sales_v1`	Older compatibility API. Existing clients continue to read and write one root product_type and one scalar tags value.
`sales_v2`	Newer API. Newer clients read and write a `lineitems` array with one or more product rows.

Design Decision

This evolution changes the canonical storage model for product information.

Originally, product attributes belonged directly to a sale. After the evolution, products become line items owned by the sale. The lineitems collection becomes authoritative, while the original flat product shape survives only as a compatibility projection over one designated row.

Shape Change

The older product shape is flat.

{
  "_id"           : 19,
  "date"          : "2026-01-29",
  "quantity"      : 4,
  "amount"        : 18.00,
  "product_type"  : "Melon Lemonade",
  "tags"          : "fresh",
  "customer_name" : "diya",
  "email_address" : "diya@example.com",
  "stand_info"    : {
    "_id"           : 11,
    "stand_pin"     : "560011",
    "stand_address" : "theater cart, balcony"
  }
}

The newer product shape moves product information into a list.

{
  "_id"              : 20,
  "date"             : "2026-01-30",
  "total_quantity"   : 7,
  "amount"           : 32.50,
  "customer_details" : {
    "_id"   : 30,
    "name"  : "rhea",
    "email" : "rhea@example.com"
  },
  "lineitems"        : [
    {
      "_id"             : 200,
      "product_type"    : "Berry Lemonade",
      "product_subtype" : "Lemonade",
      "tags"            : "seasonal",
      "qty"             : 5
    },
    {
      "_id"             : 201,
      "product_type"    : "Lemon Cookie",
      "product_subtype" : "Cookie",
      "tags"            : "snack",
      "qty"             : 2
    }
  ],
  "stand_info"       : {
    "_id"           : 12,
    "stand_pin"     : "560012",
    "stand_address" : "arcade cart, main hall"
  }
}

The sale still has sale-level quantity and amount. The product details now live in lineitems.

Preprocessing

The goal of preprocessing is not merely to create a new table. It is to migrate the canonical storage model from one product per sale to a repeatable collection of product rows.

Before this scenario, product_type and tags are stored directly on sales.

After this scenario, sales stores sale-level data, and lineitems stores item-level product data.

The preprocessing creates the evolved product table.

CREATE TABLE lineitems (
  item_id          INTEGER GENERATED BY DEFAULT ON NULL AS IDENTITY,
  sale_id          INTEGER NOT NULL,
  is_primary       BOOLEAN DEFAULT FALSE NOT NULL,
  product_type     VARCHAR2(40) NOT NULL,
  product_subtype  VARCHAR2(40) DEFAULT 'Classic' NOT NULL,
  tags             VARCHAR2(40) DEFAULT 'classic' NOT NULL,
  qty              NUMBER(6) DEFAULT 1 NOT NULL,
  CONSTRAINT lineitems_pk PRIMARY KEY (item_id),
  CONSTRAINT lineitems_sale_fk FOREIGN KEY (sale_id)
    REFERENCES sales (sale_id)
);

The important compatibility column is is_primary.

The compatibility API can expose only one flat product field set. The evolved storage model can hold many line items for the same sale. is_primary identifies the one row that represents the old scalar product slot.

Existing sales are backfilled with one primary compatibility lineitem.

INSERT INTO lineitems (item_id, sale_id, is_primary, product_type,
                       product_subtype, tags, qty)
SELECT sale_id, sale_id, TRUE, product_type,
       CASE
         WHEN product_type LIKE '%Cookie' THEN 'Cookie'
         WHEN product_type LIKE '%Cooler' THEN 'Cooler'
         WHEN product_type LIKE '%Limeade' THEN 'Limeade'
         ELSE 'Lemonade'
       END,
       tags,
       quantity
FROM sales;

For migrated rows, item_id is set to the same value as sale_id so the old sales-rooted compatibility view can regenerate the hidden lineitem key from the root _id during writes. This keeps old flat documents addressable after product data moves from sales into lineitems.

After the lineitem rows are created, the product fields can be removed from sales.

ALTER TABLE sales DROP (product_type, tags);

After this change, sales no longer stores product fields directly. Product data lives in lineitems, which is now the canonical child collection.

Relational Shape After Preprocessing

After preprocessing, the affected tables have this shape.

SQL> DESCRIBE sales;

Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
SALE_ID                                   NOT NULL NUMBER(38)
SALE_DATE                                          DATE
QUANTITY                                  NOT NULL NUMBER
AMOUNT                                    NOT NULL NUMBER(10,2)
STAND_ID                                  NOT NULL NUMBER(38)
CUSTOMER_ID                               NOT NULL NUMBER(38)

SQL> DESCRIBE lineitems;

Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
ITEM_ID                                   NOT NULL NUMBER(38)
SALE_ID                                   NOT NULL NUMBER(38)
IS_PRIMARY                                NOT NULL BOOLEAN
PRODUCT_TYPE                              NOT NULL VARCHAR2(40)
PRODUCT_SUBTYPE                           NOT NULL VARCHAR2(40)
TAGS                                      NOT NULL VARCHAR2(40)
QTY                                       NOT NULL NUMBER(6)

The relationship is:

sales.sale_id -> lineitems.sale_id

sales owns sale-level facts. lineitems owns product-level facts.

sales_v1 Compatibility View

sales_v1 preserves the old flat product API.

The view remains rooted at sales. That keeps the old sale document identity stable: _id maps to sales.sale_id.

The old root-level product_type and tags fields now come from the primary compatibility lineitem. The view uses the is_primary: true condition so the unnested lineitem path targets only the primary compatibility row.

CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW sales_v1 AS
sales @insert @update
{
  _id: sale_id,
  date: sale_date,
  quantity,
  amount,
  customers @insert @update @nodelete @unnest
  {
    customer_id: customer_id @hidden,
    customer_name: customer_name,
    email_address: email_address
  },
  lineitems (is_primary: true) @insert @update @nodelete @unnest
  {
    item_id @hidden,
    sale_id @hidden @generated(path: "$._id", on: "write"),
    is_primary @hidden @generated(path: "true", on: "write"),
    product_type,
    tags
  },
  stand_info: stands @insert @update @nodelete
  {
    _id: stand_id,
    stand_pin: pin,
    stand_address: address
  }
};

The key block is the unnested lineitems mapping.

lineitems (is_primary: true) @insert @update @nodelete @unnest
{
  item_id @hidden,
  sale_id @hidden @generated(path: "$._id", on: "write"),
  is_primary @hidden @generated(path: "true", on: "write"),
  product_type,
  tags
}

This block preserves the older JSON shape.

Field or clause	Purpose
`lineitems` condition on `is_primary = true`	Restricts the old flat product fields to the primary compatibility item.
`@unnest`	Keeps `product_type` and `tags` at the root of the old JSON document.
`item_id @hidden`	Keeps the lineitem identity out of the old API.
`sale_id @hidden @generated(path: "$._id", on: "write")`	Connects the compatibility lineitem to the sale being written.
`is_primary @hidden @generated(path: "true", on: "write")`	Marks old-view product writes as the primary compatibility item.

sales_v1 Write Behavior

When an older client inserts through sales_v1, the document still looks flat.

{
  "_id"           : 19,
  "date"          : "2026-01-29",
  "quantity"      : 4,
  "amount"        : 18.00,
  "product_type"  : "Melon Lemonade",
  "tags"          : "fresh",
  "customer_name" : "diya",
  "email_address" : "diya@example.com",
  "stand_info"    : {
    "_id"           : 11,
    "stand_pin"     : "560011",
    "stand_address" : "theater cart, balcony"
  }
}

The compatibility view breaks that document into sale, customer, stand, and lineitem rows.

JSON field	Stored in
_id	`sales.sale_id`
date	`sales.sale_date`
quantity	`sales.quantity`
amount	`sales.amount`
`customer_name`	`customers.customer_name`
`email_address`	`customers.email_address`
`product_type`	`lineitems.product_type` on the primary compatibility row
tags	`lineitems.tags` on the primary compatibility row
stand_info	`stands`, linked from `sales.stand_id`

The compatibility API does not expose the line-item array. It reads and writes one primary compatibility line item for the sale.

Use of the is_primary Boolean Field

The compatibility API has one product slot.

The evolved API has a list of line items.

is_primary marks the row that represents the old product slot. This lets sales_v1 remain rooted at sales while still exposing product_type and tags as root-level fields.

Lineitem type	Visible through `sales_v1`	Visible through `sales_v2`
Primary lineitem, is_primary = true	Yes, as root-level product_type and tags.	Yes, as one element in the `lineitems` array.
Non-primary lineitem, is_primary = false	No.	Yes, as another element in the `lineitems` array.

This avoids forcing the compatibility API to guess among multiple line items. The old shape has one explicit compatibility slot, and that slot is marked in storage.

sales_v2 Evolved View

sales_v2 exposes the evolved model directly.

The root is still sales. The sale contains a lineitems array. Each lineitem has its own product fields.

CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW sales_v2 AS
sales @insert @update @delete
{
  _id: sale_id,
  date: sale_date,
  total_quantity: quantity,
  amount,
  customer_details: customers @insert @update @nodelete
  {
    _id: customer_id,
    name: customer_name,
    email: email_address
  },
  lineitems: lineitems @insert @update @delete
  [
    {
      _id: item_id,
      is_primary @hidden,
      product_type,
      product_subtype,
      tags,
      qty
    }
  ],
  stand_info: stands @insert @update @nodelete
  {
    _id: stand_id,
    stand_pin: pin,
    stand_address: address
  }
};

The is_primary field is hidden in the newer API. Newer clients work with the lineitem array. They do not need to know which row is used for old compatibility.

New Document Shape

A newer client can now insert a sale with multiple lineitems.

{
  "_id"              : 20,
  "date"             : "2026-01-30",
  "total_quantity"   : 7,
  "amount"           : 32.50,
  "customer_details" : {
    "_id"   : 30,
    "name"  : "rhea",
    "email" : "rhea@example.com"
  },
  "lineitems"        : [
    {
      "_id"             : 200,
      "product_type"    : "Berry Lemonade",
      "product_subtype" : "Lemonade",
      "tags"            : "seasonal",
      "qty"             : 5
    },
    {
      "_id"             : 201,
      "product_type"    : "Lemon Cookie",
      "product_subtype" : "Cookie",
      "tags"            : "snack",
      "qty"             : 2
    }
  ],
  "stand_info"       : {
    "_id"           : 12,
    "stand_pin"     : "560012",
    "stand_address" : "arcade cart, main hall"
  }
}

sales_v2 owns this shape. It can insert, update, and delete the full line-item array.

Evolution Thought Process

The key architectural decision is that the line-item collection becomes the canonical storage model for product data. The older flat document becomes a compatibility projection over one row in that collection.

Before this scenario, a sale had one product_type and one tags value. After this scenario, a sale can have many lineitems, and each lineitem has its own product fields.

sales_v1 hides that change for old clients. It still presents one flat product_type and one scalar tags value by reading and writing the primary compatibility lineitem.

sales_v2 exposes the evolved model directly. It shows lineitems as an array because newer clients understand that one sale can contain many products.

Concept	Old API	New API
Product shape	Flat product_type and tags at the sale root.	`lineitems` array.
Product cardinality	One product field set.	Many product item objects.
Storage before this topic	product_type and tags on sales.	`product_type` and `tags` on sales.
Storage after this topic	Primary compatibility row in `lineitems`.	All product rows in `lineitems`.
Write scope	One primary compatibility item.	Full `lineitems` array.

The important design move is not only creating a child table. It is defining exactly how the old one-product API maps to one row in the new collection.

Behavior

API or schema object	Behavior
`sales_v1.product_type`	Root-level field backed by the primary `lineitems` row.
`sales_v1.tags`	Root-level field backed by the primary `lineitems` row.
`lineitems.is_primary`	Marks the lineitem used by the old flat compatibility API.
`sales_v2.lineitems`	Exposes the full list of product rows for the sale.
`sales_v2.total_quantity`	Exposes sales.quantity as the sale-level total quantity.
`lineitems.sale_id`	Links each item back to its sale.
lineitems_sale_fk	Enforces that each lineitem belongs to a sale.

Recommendation

Best when

A formerly single business concept has become naturally repeatable.
Individual items need their own attributes, validation, or lifecycle.
The collection is expected to become the long-term storage model.

Avoid when

The collection is only a presentation change.
The parent still owns all business meaning.
Multiple child rows provide no additional business value.

Limitation

sales_v1 can preserve one flat product_type and one scalar tags value by mapping them to one primary compatibility line item. It cannot expose the full collection.

A sale inserted only through sales_v2 can contain only non-primary line items. In that case, sales_v1 intentionally does not flatten the sale into the old product shape because no row has been designated to represent the scalar compatibility slot. The evolved API still sees both kinds of rows: primary compatibility items and ordinary line items.

Deletes via sales_v1 are intentionally disallowed. sales_v1 preserves insert and update for the single primary compatibility item, while full sale deletion belongs to sales_v2.

Design Trade-offs

Benefit	Cost
Canonical storage reflects the real business model.	Existing data must be migrated into child rows.
Individual items can evolve independently.	Compatibility logic is required for older APIs.
Future enhancements become easier.	Legacy APIs can expose only a representative child.

New Behavior Used

This scenario introduces the move from scalar fields to a child collection.

Feature	Why it matters
`lineitems` table	Stores product details as repeatable rows instead of sale-level columns.
`is_primary` compatibility marker	Identifies the one lineitem that represents the old flat product fields.
`is_primary : true` condition	Restricts old-view product reads and writes to the primary compatibility row.
`@unnest` on `lineitems` in `sales_v1`	Keeps product_type and tags at the root of the old JSON document.
`@hidden` generated fields	Supply sale_id and is_primary for old-view writes without exposing them in the old API.
Nested array	Lets `sales_v2` expose many lineitem objects under one sale.

Evolution Check

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

product_type and tags no longer need to be stored directly on sales.
Existing sales are backfilled with one primary compatibility lineitem.
sales_v1 continues to expose product_type and tags as root-level fields.
sales_v1 inserts one primary compatibility lineitem for an old-style sale.
sales_v1 updates only the primary compatibility lineitem.
sales_v2 exposes lineitems as an array.
sales_v2 can insert multiple product rows for one sale.
sales_v2 can update a targeted lineitem.
sales_v1 does not flatten sales that have no primary compatibility item.
sales_v2 sees both old primary rows and new non-primary rows.

Topic Takeaway

This scenario demonstrates cardinality evolution: one product field set becomes a repeatable collection of line items.

The canonical storage model evolves from one product owned directly by a sale to a sale owning a collection of product rows. The older flat representation survives only as a compatibility projection over one designated item.

The important design principle is to model repeatability explicitly. Once products become independently meaningful items, the collection should become authoritative even if older APIs continue exposing one representative row.

The cost is that the compatibility API can address only its designated row. It cannot see, add, or remove the other line items on the same sale, so once a sale holds more than one product, the old shape gives an incomplete view of it.