Add a Parent Object Above Nested Objects

Add a category nesting level above line items while preserving older line item documents.

Adding a nesting level above an existing list.

Business Step

Dave’s sale document can now represent multiple lineitems.

That was the big step in the previous topic. One sale can have more than one product, and each product can carry its own product_type, product_subtype, tags, and qty.

But as the menu grows, a flat list of lineitems is no longer enough. Dave wants the order to be easier to understand. Drinks should be grouped together. Snacks should be grouped together. Later, other groups may appear too.

The product fields are not changing in this scenario.

The structure around them is changing.

Instead of this:

"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
  }
]

Dave now wants this:

"item_categories": [
  {
    "_id"           : 220,
    "category_desc" : "Beverage",
    "lineitems"     : [
      {
        "_id"             : 220,
        "product_type"    : "Mint Lemonade",
        "product_subtype" : "Lemonade",
        "tags"            : "seasonal",
        "qty"             : 5
      }
    ]
  },
  {
    "_id"           : 221,
    "category_desc" : "Snack",
    "lineitems"     : [
      {
        "_id"             : 221,
        "product_type"    : "Lemon Biscuit",
        "product_subtype" : "Biscuit",
        "tags"            : "snack",
        "qty"             : 3
      }
    ]
  }
]

The lineitems are still there. They now sit under a category level.

Evolution Goal

This scenario adds a new nesting level between sales and lineitems.

sales_v1 remains the older compatibility API. It continues to expose one flat product_type and one scalar tags value at the root of the document. The category level is hidden from this API.

sales_v2 is the newer API. It exposes item_categories as an array, and each category contains its own lineitems array.

Documentation view Role
sales_v1 Older compatibility API. Existing clients continue to read and write one flat product_type and one scalar tags value.
sales_v2 Newer API. Newer clients read and write item_categories, with lineitems nested inside each category.

Shape Change

The old product area is flat from the older client’s point of view.

{
  "_id"           : 21,
  "date"          : "2026-02-01",
  "quantity"      : 3,
  "amount"        : 15.00,
  "product_type"  : "Peach Lemonade",
  "tags"          : "fresh",
  "customer_name" : "tara",
  "email_address" : "tara@example.com",
  "stand_info"    : {
    "_id"           : 21,
    "stand_pin"     : "560021",
    "stand_address" : "atrium cart, south door"
  }
}

The newer product area is grouped.

{
  "_id"              : 22,
  "date"             : "2026-02-02",
  "total_quantity"   : 8,
  "amount"           : 34.00,
  "customer_details" : {
    "_id"   : 32,
    "name"  : "isha",
    "email" : "isha@example.com"
  },
  "item_categories"  : [
    {
      "_id"           : 220,
      "category_desc" : "Beverage",
      "lineitems"     : [
        {
          "_id"             : 220,
          "product_type"    : "Mint Lemonade",
          "product_subtype" : "Lemonade",
          "tags"            : "seasonal",
          "qty"             : 5
        }
      ]
    },
    {
      "_id"           : 221,
      "category_desc" : "Snack",
      "lineitems"     : [
        {
          "_id"             : 221,
          "product_type"    : "Lemon Biscuit",
          "product_subtype" : "Biscuit",
          "tags"            : "snack",
          "qty"             : 3
        }
      ]
    }
  ],
  "stand_info"       : {
    "_id"           : 22,
    "stand_pin"     : "560022",
    "stand_address" : "terrace cart, east stairs"
  }
}

This is not a field rename. It is not a field split.

It is a tree-shape change. A new parent level is introduced above the existing lineitem rows.

Preprocessing

Before this scenario, lineitems belongs directly to sales.

After this scenario, lineitems belongs to item_categories, and item_categories belongs to sales.

The preprocessing creates the category table.

CREATE TABLE item_categories (
  sale_id           INTEGER NOT NULL,
  item_category_id  INTEGER NOT NULL,
  category_desc     VARCHAR2(40) DEFAULT 'Unspecified' NOT NULL,
  CONSTRAINT item_categories_pk PRIMARY KEY (sale_id, item_category_id),
  CONSTRAINT item_categories_sale_fk FOREIGN KEY (sale_id)
    REFERENCES sales (sale_id)
);

item_categories is owned by sales. Each category belongs to one sale.

Next, each lineitem receives a category reference.

ALTER TABLE lineitems ADD (item_category_id INTEGER);

In this scenario, lineitems are nested under item_categories. A lineitem is reached by the category link columns, sale_id and item_category_id, and then identified by item_id.

This unique key supports the nested writable shape.

ALTER TABLE lineitems ADD CONSTRAINT lineitems_cat_item_uk
UNIQUE (sale_id, item_category_id, item_id);

This gives the duality view a stable key for each nested lineitem row:

Without this key, the writable nested mapping may not have enough key information to identify a lineitem under its parent category.

This matters because the view needs a stable identifying key for the nested child row that includes both the parent-linking columns and the child row identity.

Before creating and evolving to new APIs existing lineitems need to be placed into categories.

For this scenario, the category rule is intentionally simple:

Product rule Category
Product type ending in Cookie Snack
Product type ending in Biscuit Snack
Product type ending in Cupcake Snack
Everything else Beverage

The preprocessing creates category rows from existing lineitem data.

INSERT INTO item_categories (sale_id, item_category_id, category_desc)
SELECT li.sale_id,
       CASE
         WHEN MAX(CASE WHEN li.is_primary THEN 1 ELSE 0 END) = 1 THEN li.sale_id
         ELSE MIN(li.item_id)
       END AS item_category_id,
       CASE
         WHEN li.product_type LIKE '%Cookie'
           OR li.product_type LIKE '%Biscuit'
           OR li.product_type LIKE '%Cupcake' THEN 'Snack'
         ELSE 'Beverage'
       END AS category_desc
FROM lineitems li
GROUP BY li.sale_id,
         CASE
           WHEN li.product_type LIKE '%Cookie'
             OR li.product_type LIKE '%Biscuit'
             OR li.product_type LIKE '%Cupcake' THEN 'Snack'
           ELSE 'Beverage'
         END;

The important compatibility decision is how item_category_id is assigned.

For sales that already have a primary compatibility lineitem, the category id is set to sale_id. That lets the old sales-rooted compatibility view route one flat document to one hidden category.

For sales inserted through the newer multi-lineitem view with no primary compatibility item, category ids use the first item id in each product group. That gives the generated category row a stable identifier without pretending that the sale has an old flat compatibility item.

After categories are created, each lineitem is moved under its matching category.

UPDATE lineitems li
SET item_category_id = (
  SELECT ic.item_category_id
  FROM item_categories ic
  WHERE ic.sale_id = li.sale_id
    AND ic.category_desc =
      CASE
        WHEN li.product_type LIKE '%Cookie'
          OR li.product_type LIKE '%Biscuit'
          OR li.product_type LIKE '%Cupcake' THEN 'Snack'
        ELSE 'Beverage'
      END
);

After this step, each lineitem belongs to a category.

The category relationship is then enforced.

ALTER TABLE lineitems MODIFY (item_category_id NOT NULL);

ALTER TABLE lineitems ADD CONSTRAINT lineitems_category_fk
  FOREIGN KEY (sale_id, item_category_id)
  REFERENCES item_categories (sale_id, item_category_id);

The foreign key uses both sale_id and item_category_id.

That matters because category identifiers are scoped inside a sale. The same category identifier could exist under different sales, but the pair of sale_id and item_category_id identifies one category within one sale.

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 item_categories;

Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
SALE_ID                                   NOT NULL NUMBER(38)
ITEM_CATEGORY_ID                          NOT NULL NUMBER(38)
CATEGORY_DESC                             NOT NULL VARCHAR2(40)

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)
ITEM_CATEGORY_ID                          NOT NULL NUMBER(38)

The relationships are:

sales.sale_id -> item_categories.sale_id

item_categories.(sale_id, item_category_id)
  -> lineitems.(sale_id, item_category_id)

sales owns the order. item_categories groups the order. lineitems stores the products inside each group.

sales_v1 Compatibility View

sales_v1 keeps the old flat product API alive.

Older clients still do not know that categories exist. They still see one root product_type and one scalar tags value. The category level is hidden from them.

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
  },
  stand_info: stands @insert @update @nodelete
  {
    _id: stand_id,
    stand_pin: pin,
    stand_address: address
  },
  item_categories @insert @update @nodelete @identifiedBy(key: ["SALE_ID", "ITEM_CATEGORY_ID"]) @unnest
  {
    category_sale_id: sale_id @hidden @generated(path: "$._id", on: "write"),
    category_id: item_category_id @hidden @generated(path: "$._id", on: "write")
  },
  lineitems (is_primary: true) @insert @update @nodelete @identifiedBy(key: ["ITEM_ID"]) @unnest
  {
    item_id @hidden @generated(path: "$._id", on: "write"),
    sale_id @hidden @generated(path: "$._id", on: "write"),
    item_category_id @hidden @generated(path: "$._id", on: "write"),
    is_primary @hidden @generated(path: "true", on: "write"),
    product_type,
    tags
  }
};

This is the same compatibility strategy as the previous topic, but with one more hidden level.

Older clients do not send item_categories or category_desc. The compatibility view supplies the hidden category identity from the old root _id, and the item_categories table supplies the default category_desc value.

That means an old-style insert can still create the relational path required by the evolved model, even though the old JSON document has no category object.

Why @identifiedBy Is Used

The old JSON document has only one _id.

After this scenario, the relational path needs more than one identity:

The compatibility view uses hidden generated fields to reuse the old root _id along the compatibility path.

category_sale_id: sale_id @hidden @generated(path: "$._id", on: "write"),
category_id: item_category_id @hidden @generated(path: "$._id", on: "write")

and:

item_id @hidden @generated(path: "$._id", on: "write"),
sale_id @hidden @generated(path: "$._id", on: "write"),
item_category_id @hidden @generated(path: "$._id", on: "write"),
is_primary @hidden @generated(path: "true", on: "write")

The item_categories block uses identifiedBy so the writable view can identify the hidden category row using the generated hidden key values.

@identifiedBy(key: ["SALE_ID", "ITEM_CATEGORY_ID"])

The lineitems block also uses identifiedBy so the writable view can identify the hidden lineitem row.

@identifiedBy(key: ["ITEM_ID"])

This keeps the old flat JSON shape while still giving the database enough identity information to write the evolved hierarchy.

sales_v1 Write Behavior

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

{
  "_id"           : 21,
  "date"          : "2026-02-01",
  "quantity"      : 3,
  "amount"        : 15.00,
  "product_type"  : "Peach Lemonade",
  "tags"          : "fresh",
  "customer_name" : "tara",
  "email_address" : "tara@example.com",
  "stand_info"    : {
    "_id"           : 21,
    "stand_pin"     : "560021",
    "stand_address" : "atrium cart, south door"
  }
}

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

JSON field Stored in
_id sales.sale_id, hidden item_categories.item_category_id, and hidden lineitems.item_id for the compatibility path
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 category identity or the lineitem array. It writes one hidden category and one primary compatibility lineitem for the sale.

sales_v2 Evolved View

sales_v2 exposes the new category-shaped model directly.

The root is sales. Under each sale, item_categories is an array. Under each category, lineitems is another array.

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
  },
  item_categories: item_categories @insert @update @delete
  [
    {
      sale_id @hidden,
      _id: item_category_id,
      category_desc,
      lineitems: lineitems @insert @update @delete
      [
        {
          _id: item_id,
          product_type,
          product_subtype,
          tags,
          qty
        }
      ]
    }
  ],
  stand_info: stands @insert @update @nodelete
  {
    _id: stand_id,
    stand_pin: pin,
    stand_address: address
  }
};

The hidden sale_id inside item_categories is needed by the relational key, but it is not part of the JSON API. The sale already owns the category array, so newer clients do not need to repeat sale_id inside each category object.

The is_primary field is also not projected in sales_v2. New category-shaped clients do not choose the old compatibility item. New lineitems use the base column default for is_primary.

New Document Shape

A newer client can now insert a sale with multiple item categories.

{
  "_id"              : 22,
  "date"             : "2026-02-02",
  "total_quantity"   : 8,
  "amount"           : 34.00,
  "customer_details" : {
    "_id"   : 32,
    "name"  : "isha",
    "email" : "isha@example.com"
  },
  "item_categories"  : [
    {
      "_id"           : 220,
      "category_desc" : "Beverage",
      "lineitems"     : [
        {
          "_id"             : 220,
          "product_type"    : "Mint Lemonade",
          "product_subtype" : "Lemonade",
          "tags"            : "seasonal",
          "qty"             : 5
        }
      ]
    },
    {
      "_id"           : 221,
      "category_desc" : "Snack",
      "lineitems"     : [
        {
          "_id"             : 221,
          "product_type"    : "Lemon Biscuit",
          "product_subtype" : "Biscuit",
          "tags"            : "snack",
          "qty"             : 3
        }
      ]
    }
  ],
  "stand_info"       : {
    "_id"           : 22,
    "stand_pin"     : "560022",
    "stand_address" : "terrace cart, east stairs"
  }
}

This shape is closer to how a real order is read by a person. The sale has categories, and each category has items.

Compatibility Item Behavior

The new API can represent multiple categories and multiple lineitems. The old API cannot.

sales_v1 can still display and write one flat product document for the primary compatibility item. It hides category identity and uses generated hidden fields to keep the evolved relational path connected.

Item path sales_v1 behavior
Primary compatibility item Can be read and updated as the old flat product_type and tags shape.
Non-primary lineitem Not exposed as the old flat product slot.
Multi-category sale with no primary item Not flattened by sales_v1. Category-specific intent belongs to sales_v2.

This is the sharp edge in this scenario. The old flat document has no place to carry category identity. The new hierarchy needs sale identity, category identity, and item identity.

Evolution Thought Process

This topic adds a parent level above a list that already exists.

Before this scenario, sales_v2 exposed lineitems directly under the sale. After this scenario, sales_v2 exposes item_categories under the sale, and each category owns its own lineitems array.

sales_v1 hides the new category level. It continues to present one flat product_type and one scalar tags value. To make that possible, the compatibility view uses hidden generated identities for the category and lineitem relationship.

sales_v2 exposes the new model honestly. It shows categories because newer clients understand the grouped order shape.

Concept Old API New API
Product shape Flat product_type and tags at root. lineitems nested under item_categories.
Category identity Hidden. Visible as item_categories._id.
Category description Hidden and defaulted. Visible as category_desc.
Lineitem location Root compatibility product slot. Inside category.lineitems.
DML scope One primary compatibility item. Full sale, categories, and items.

The category level is not just cosmetic. It changes the identity path required to reach a lineitem.

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.
sales_v1.item_categories @unnest Allows the hidden category row to participate without appearing in the old JSON shape.
sales_v1.lineitems condition on is_primary = true Restricts old-view product reads and writes to the primary compatibility item.
sales_v1 @identifiedBy Identifies hidden category and lineitem rows for writable compatibility DML.
sales_v2.item_categories Exposes categories as an array under the sale.
sales_v2.item_categories.lineitems Exposes lineitems inside each category.
item_categories_sale_fk Enforces that each category belongs to a sale.
lineitems_category_fk Enforces that each lineitem belongs to a category within the same sale.

When to use this strategy

Use this strategy when an existing list needs a grouping level. It is a good fit when a flat list has grown large enough that newer clients need sections, categories, stages, or buckets around the existing child rows.

Limitation

Old-view DML is safe only for the single primary compatibility item. The old flat document has no place to carry parent sale identity, category identity, and item identity separately.

A newer sale can contain multiple categories and multiple lineitems. sales_v1 should not be used to update non-primary items because the old document cannot express which category and item the client intends to target.

Deletes via sales_v1 are intentionally disallowed. The old flat document cannot express whether the intended delete target is an item, a category, or the whole sale.

Full sale deletion belongs to sales_v2, which owns the category-shaped document.

New Behavior Used

This scenario introduces a new nesting level above an existing child array.

Feature Why it matters
item_categories table Adds a grouping level between sales and lineitems.
Composite primary key Identifies a category by sale_id and item_category_id.
Composite foreign key Connects lineitems to item_categories using sale_id and item_category_id.
@identifiedBy Lets writable compatibility views identify hidden rows whose keys are generated from the old JSON shape.
@unnest on item_categories in sales_v1 Keeps the category level hidden while still allowing it to participate in old-view DML.
Hidden generated identity fields Fill in sale, category, and item identifiers that the old flat API cannot carry.
Nested array inside nested array Lets sales_v2 expose categories, and each category expose its own lineitems.

Evolution Check

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

Topic Takeaway

This scenario adds structure around an existing list without changing the item fields themselves.

Older clients still see a flat product_type and tags value. Newer clients see item_categories, and each category contains its own lineitems.

The key compatibility idea is hidden hierarchy. The older API does not know about categories, but the duality view supplies the hidden category identifiers needed to keep one primary compatibility item writable.

The key limitation is also about identity. Once the new document has sale identity, category identity, and item identity, the old flat API can safely update only the primary compatibility path.