Move Fields to Nested Objects

Move product fields into line items so a sale can contain multiple product entries.

Extending a set of fields into a list of field sets.

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 bigger change than adding a field or changing a field format. The product part of the document changes cardinality. It moves from one scalar set of fields to a repeatable list of item objects.

Evolution Goal

This scenario moves product fields out of sales and into a new lineitems table.

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.

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

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 new column is is_primary.

The older API can expose only one flat product field set. The newer storage model can hold many lineitems for the same sale. The is_primary boolean field identifies the one lineitem 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 need to store product fields directly. Product data lives in lineitems.

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 the sale-level facts. lineitems owns the product-level facts.

`sales_v1` Compatibility View

sales_v1 keeps the old flat product API alive.

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 old API does not expose the lineitem array. It writes one primary compatibility lineitem for the sale.

Use of the `is_primary` Boolean Field

The old API has one product slot.

The new API has a list of lineitems.

is_primary marks the one lineitem that represents the old product slot. That lets sales_v1 stay 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 making the old API choose among multiple lineitems. It has one compatibility slot, and that slot is marked in storage.

`sales_v2` Evolved View

sales_v2 exposes the new 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 lineitem array.

Evolution Thought Process

This topic changes the cardinality of product data.

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 new 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 new table. It is also choosing how the old one-product API maps to one row in the new child table.

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.

When to use this strategy

Use this strategy when one group of fields has become repeatable. It is a good fit when the old document had one product, contact method, address, or detail row, but the newer business process needs a list of those things.

Limitation

sales_v1 can preserve one flat product_type and one scalar tags value by mapping them to one primary compatibility lineitem. It does not expose the full lineitem list.

A sale inserted only through sales_v2 can contain only non-primary lineitems. In that case, sales_v1 intentionally does not flatten that sale into an old product document, because there is no primary compatibility item to represent the old scalar product slot. The new API still sees both kinds of rows: old primary compatibility lineitems and new non-primary lineitems.

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.

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 turns one product field set into a repeatable item list.

Older clients still see one product_type and one tags value at the root. Internally, that old shape is backed by one primary compatibility lineitem.

Newer clients get a lineitems array that can represent a real multi-item order.

The key compatibility idea is the primary lineitem. One old flat product shape maps to one marked item row, while the newer API can use the full collection.