Add a Parent Object Above Nested Objects

Introduce a new parent level above line items while preserving the compatibility API.

Inserting a new parent level into an existing document hierarchy.

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 hierarchy 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.

Best Practice

Add a parent level only when the grouping has become part of the business model.

A new hierarchy level should carry meaning, identity, or lifecycle of its own. Do not add structural depth merely to reorganize presentation. When the hierarchy is real, make it part of the canonical storage model and keep older flat APIs on a deliberately limited compatibility path.

Evolution Goal

This scenario demonstrates hierarchy evolution: a new category level is inserted 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 or decomposition. It is a tree-shape change: a new parent level is introduced above the existing line-item rows.

Design Decision

The category level joins the canonical storage model because grouping is now a business concept rather than a display choice. The cost is that a flat document has no way to address a grouped structure, so the compatibility API is limited to one designated path through the hierarchy.

Preprocessing

Before this scenario, lineitems belongs directly to sales.

After this scenario, lineitems belongs to item_categories, and item_categories belongs to sales. The grouped hierarchy becomes the canonical storage model.

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:

sale_id identifies the sale.
item_category_id identifies the category within that sale.
item_id identifies the lineitem inside that category.

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 provides the grouping level. lineitems stores the products inside each group.

sales_v1 Compatibility View

sales_v1 preserves the old flat product API.

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 extends the previous compatibility strategy by adding one more hidden level to the relational path.

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.

An old-style insert can therefore create the relational path required by the canonical hierarchy even though the compatibility 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:

sales.sale_id
item_categories.item_category_id
lineitems.item_id

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 giving the database enough identity information to write the canonical 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 compatibility API does not expose category identity or the line-item array. It writes one hidden category and one primary compatibility line item for the sale.

sales_v2 Evolved View

sales_v2 exposes the evolved category hierarchy 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 reflects the evolved business hierarchy directly: a sale has categories, and each category has items.

Compatibility Item Behavior

The evolved API can represent multiple categories and multiple line items. The compatibility 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 central compatibility limit. The old flat document has no place to carry category identity, while the evolved hierarchy requires sale identity, category identity, and item identity.

Evolution Thought Process

This topic inserts 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 evolved model directly 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 cosmetic. It changes the identity path required to reach a line item and therefore changes the writable hierarchy.

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.

Recommendation

Use this strategy when an existing list needs a genuine grouping level. It is a good fit when sections, categories, stages, or buckets have become part of the business model rather than merely a presentation concern.

Limitation

Compatibility DML is safe only for the single primary compatibility item. The old flat document has no place to carry 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.

Design Trade-offs

Benefit	Cost
The canonical hierarchy reflects the grouped business model.	Existing data must be assigned to new parent rows.
Categories can carry their own identity and attributes.	Compatibility DML requires hidden parent identity.
Newer APIs can address the full hierarchy.	Older flat APIs can safely address only a limited compatibility path.

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:

lineitems now sit under item_categories in the evolved storage model.
item_categories groups lineitems by sale and category.
Existing lineitems are backfilled into categories.
Compatibility sales keep a hidden category path that old flat DML can use.
sales_v1 continues to expose one flat product_type and one scalar tags value.
sales_v1 inserts one hidden category and one primary compatibility item for an old-style sale.
sales_v1 updates the single primary compatibility item.
sales_v2 exposes item_categories as an array.
Each item category can contain its own lineitems array.
Newer clients can insert and update the category-shaped sale document.
Deletes via sales_v1 are intentionally disallowed.
Full sale deletion is handled through sales_v2.

Topic Takeaway

This scenario demonstrates hierarchy evolution: a new parent level is inserted above an existing child collection.

The canonical storage model evolves from a sale owning line items directly to a sale owning categories that in turn own line items. The compatibility API preserves one flat product slot by hiding the category path and generating the identities the old document cannot carry.

The important design principle is to treat hierarchy changes as identity changes. Once a new parent level becomes part of the business model, the compatibility API can safely preserve only the paths whose parent and child identities can be reconstructed unambiguously.