ORA-42647: Missing '_id' Field for JSON-Relational Duality View - Oracle 23ai Fix

ORA-42647: Missing ‘_id’ Field at Root Level for JSON-Relational Duality View

Error Overview

Error Text: ORA-42647: missing '_id' field at the root level for JSON-relational duality view

This error occurs in Oracle Database 23ai when creating a JSON-Relational Duality View without designating a document identifier (_id) at the root level. Duality views require a primary key mapping through the @id annotation (or the _id field specification) so that each JSON document has a unique, stable identifier that maps to the underlying relational primary key.

New in Oracle 23ai: JSON-Relational Duality Views are a flagship feature introduced in Oracle 23ai. They allow data to be accessed simultaneously as JSON documents and relational tables, with full ACID transaction support. The _id field requirement ensures every document is uniquely addressable for both reads and writes.

Understanding JSON-Relational Duality Views

How Duality Views Work

JSON-Relational Duality Architecture
├── Relational Tables (source of truth)
│   ├── Standard tables with primary keys
│   ├── Foreign key relationships preserved
│   └── Full relational access remains available
├── Duality View (dual access layer)
│   ├── Exposes relational data as JSON documents
│   ├── @id annotation maps to primary key (_id field)
│   ├── Nested objects map to joined tables
│   └── Supports INSERT, UPDATE, DELETE via JSON
└── JSON Documents (application view)
    ├── Each document has a unique _id
    ├── Documents reflect joined relational data
    └── Changes flow back to underlying tables

Common Scenarios

• Missing @id annotation - Defining a duality view without annotating the primary key column
• Wrong annotation placement - Placing @id on a non-primary-key column
• Legacy JSON collection migration - Migrating from SODA collections where _id was auto-generated
• Incorrect GraphQL-style syntax - Misunderstanding the duality view DDL syntax
• Composite primary keys - Not handling multi-column primary keys correctly

Diagnostic Steps

1. Check the Failing Duality View Definition

-- Review the CREATE statement that caused the error
-- The most common mistake is omitting @id on the primary key column

-- INCORRECT (causes ORA-42647):
-- CREATE JSON RELATIONAL DUALITY VIEW orders_dv AS
--   orders {
--     order_id     -- missing @id annotation
--     order_date
--     customer_name
--   };

-- CORRECT:
-- CREATE JSON RELATIONAL DUALITY VIEW orders_dv AS
--   orders @insert @update @delete {
--     _id: order_id @id
--     order_date
--     customer_name
--   };

2. Examine Existing Duality Views

-- List all JSON-Relational Duality Views in the schema
SELECT
  view_name,
  root_table_name,
  json_schema,
  status
FROM user_json_duality_views
ORDER BY view_name;

-- Detailed duality view metadata from DBA perspective
SELECT
  owner,
  view_name,
  root_table_name,
  status,
  created
FROM dba_json_duality_views
WHERE owner = USER
ORDER BY view_name;

-- Check the view DDL for an existing working duality view
SELECT DBMS_METADATA.GET_DDL('VIEW', 'WORKING_DUALITY_VIEW', USER) AS ddl
FROM dual;

3. Verify the Underlying Table Structure

-- Check the primary key of the target table
SELECT
  c.constraint_name,
  c.constraint_type,
  cc.column_name,
  cc.position,
  tc.data_type,
  tc.data_length
FROM user_constraints c
  JOIN user_cons_columns cc
    ON c.constraint_name = cc.constraint_name
  JOIN user_tab_columns tc
    ON cc.table_name = tc.table_name
    AND cc.column_name = tc.column_name
WHERE c.table_name = 'YOUR_TABLE_NAME'
  AND c.constraint_type = 'P'
ORDER BY cc.position;

-- Verify the table has a primary key (required for duality views)
SELECT
  table_name,
  constraint_name,
  constraint_type,
  status
FROM user_constraints
WHERE table_name = 'YOUR_TABLE_NAME'
  AND constraint_type = 'P';

4. Check for Related Duality View Errors

-- Review recent compilation errors
SELECT
  name,
  type,
  line,
  position,
  text
FROM user_errors
WHERE type = 'VIEW'
  AND name LIKE '%_DV'
ORDER BY name, sequence;

-- Check for invalid duality views
SELECT
  object_name,
  object_type,
  status,
  created,
  last_ddl_time
FROM user_objects
WHERE object_type = 'VIEW'
  AND status = 'INVALID'
ORDER BY object_name;

Resolution Steps

Solution 1: Add the @id Annotation to the Primary Key

The most common fix — annotate the primary key column with @id and alias it as _id.

-- Step 1: Ensure the underlying table exists with a primary key
CREATE TABLE orders (
  order_id    NUMBER PRIMARY KEY,
  order_date  DATE NOT NULL,
  customer_name VARCHAR2(200),
  total_amount NUMBER(10,2),
  status       VARCHAR2(20) DEFAULT 'PENDING'
);

-- Step 2: Create the duality view with @id on the primary key
CREATE JSON RELATIONAL DUALITY VIEW orders_dv AS
  orders @insert @update @delete {
    _id: order_id @id
    order_date
    customer_name
    total_amount
    status
  };

-- Step 3: Verify the view works
SELECT json_serialize(data PRETTY) FROM orders_dv;

Solution 2: Duality View with Nested Objects (Joins)

When the duality view spans multiple tables with foreign key relationships.

-- Create parent and child tables
CREATE TABLE customers (
  customer_id   NUMBER PRIMARY KEY,
  name          VARCHAR2(200) NOT NULL,
  email         VARCHAR2(200) UNIQUE
);

CREATE TABLE orders (
  order_id      NUMBER PRIMARY KEY,
  customer_id   NUMBER NOT NULL REFERENCES customers(customer_id),
  order_date    DATE DEFAULT SYSDATE,
  total_amount  NUMBER(10,2)
);

CREATE TABLE order_items (
  item_id       NUMBER PRIMARY KEY,
  order_id      NUMBER NOT NULL REFERENCES orders(order_id),
  product_name  VARCHAR2(200),
  quantity      NUMBER,
  unit_price    NUMBER(10,2)
);

-- Create duality view with nested structure
-- Note: @id is required at the ROOT level (customers in this case)
CREATE JSON RELATIONAL DUALITY VIEW customer_orders_dv AS
  customers @insert @update @delete {
    _id: customer_id @id
    name
    email
    orders: orders @insert @update @delete {
      order_id
      order_date
      total_amount
      items: order_items @insert @update @delete {
        item_id
        product_name
        quantity
        unit_price
      }
    }
  };

Solution 3: Duality View with Composite Primary Key

When the underlying table uses a multi-column primary key.

-- Table with composite primary key
CREATE TABLE enrollment (
  student_id  NUMBER,
  course_id   NUMBER,
  enrolled_date DATE DEFAULT SYSDATE,
  grade       VARCHAR2(2),
  CONSTRAINT pk_enrollment PRIMARY KEY (student_id, course_id)
);

-- For composite keys, annotate ALL primary key columns with @id
CREATE JSON RELATIONAL DUALITY VIEW enrollment_dv AS
  enrollment @insert @update @delete {
    _id: student_id @id
    course_id @id
    enrolled_date
    grade
  };

Solution 4: Insert, Update, and Query Through Duality Views

Once the view is created correctly, use it for JSON document operations.

-- Insert a JSON document through the duality view
INSERT INTO orders_dv VALUES (
  '{"_id": 1001, "order_date": "2026-03-07", "customer_name": "Acme Corp",
    "total_amount": 2500.00, "status": "PENDING"}'
);

-- Update a document using the _id
UPDATE orders_dv
SET data = json_mergepatch(data, '{"status": "SHIPPED"}')
WHERE json_value(data, '$._id') = 1001;

-- Query documents as JSON
SELECT json_serialize(data PRETTY)
FROM orders_dv
WHERE json_value(data, '$._id') = 1001;

-- Delete a document by _id
DELETE FROM orders_dv
WHERE json_value(data, '$._id') = 1001;

-- Verify changes propagated to relational table
SELECT * FROM orders WHERE order_id = 1001;

Common Mistakes and Fixes

Mistake 1: Omitting @id Entirely

-- WRONG: No @id annotation (causes ORA-42647)
CREATE JSON RELATIONAL DUALITY VIEW emp_dv AS
  employees @insert @update @delete {
    employee_id
    first_name
    last_name
  };

-- CORRECT: Add @id to primary key column
CREATE JSON RELATIONAL DUALITY VIEW emp_dv AS
  employees @insert @update @delete {
    _id: employee_id @id
    first_name
    last_name
  };

Mistake 2: Using @id on a Non-Primary-Key Column

-- WRONG: @id on email instead of the actual primary key
CREATE JSON RELATIONAL DUALITY VIEW emp_dv AS
  employees @insert @update @delete {
    _id: email @id
    employee_id
    first_name
  };

-- CORRECT: @id must reference the primary key column
CREATE JSON RELATIONAL DUALITY VIEW emp_dv AS
  employees @insert @update @delete {
    _id: employee_id @id
    email
    first_name
  };

Mistake 3: Missing DML Annotations

-- This creates a read-only duality view (no @insert @update @delete)
CREATE JSON RELATIONAL DUALITY VIEW emp_dv AS
  employees {
    _id: employee_id @id
    first_name
    last_name
  };

-- For read-write access, include the DML annotations
CREATE JSON RELATIONAL DUALITY VIEW emp_dv AS
  employees @insert @update @delete {
    _id: employee_id @id
    first_name
    last_name
  };

Prevention Strategies

1. Follow a Standard Duality View Template

-- Template for single-table duality view
CREATE JSON RELATIONAL DUALITY VIEW <table>_dv AS
  <table> @insert @update @delete {
    _id: <primary_key_column> @id
    <column2>
    <column3>
    -- ... additional columns
  };

2. Validate Primary Keys Before Creating Duality Views

-- Pre-check script: verify tables have primary keys
SET SERVEROUTPUT ON
DECLARE
  v_pk_count NUMBER;
BEGIN
  SELECT COUNT(*) INTO v_pk_count
  FROM user_constraints
  WHERE table_name = UPPER('&table_name')
    AND constraint_type = 'P';

  IF v_pk_count = 0 THEN
    DBMS_OUTPUT.PUT_LINE('ERROR: Table has no primary key.');
    DBMS_OUTPUT.PUT_LINE('A primary key is required for JSON-Relational Duality Views.');
    DBMS_OUTPUT.PUT_LINE('Add one with: ALTER TABLE ' || UPPER('&table_name') ||
      ' ADD PRIMARY KEY (column_name);');
  ELSE
    DBMS_OUTPUT.PUT_LINE('OK: Primary key found. Table is eligible for duality view creation.');

    FOR rec IN (
      SELECT cc.column_name, cc.position
      FROM user_constraints c
        JOIN user_cons_columns cc ON c.constraint_name = cc.constraint_name
      WHERE c.table_name = UPPER('&table_name')
        AND c.constraint_type = 'P'
      ORDER BY cc.position
    ) LOOP
      DBMS_OUTPUT.PUT_LINE('  PK column ' || rec.position || ': ' || rec.column_name ||
        ' --> use @id annotation on this column');
    END LOOP;
  END IF;
END;
/

3. Use a Naming Convention for Duality Views

-- Consistent naming makes duality views easy to identify
-- Convention: <table_name>_dv

-- Examples:
-- orders     --> orders_dv
-- customers  --> customers_dv
-- products   --> products_dv

-- Query all duality views using the convention
SELECT view_name, root_table_name, status
FROM user_json_duality_views
WHERE view_name LIKE '%_DV'
ORDER BY view_name;

4. Test Duality Views After Creation

-- Validation script: test insert, read, update, delete cycle
SET SERVEROUTPUT ON
DECLARE
  v_count NUMBER;
  v_json  CLOB;
BEGIN
  -- Test SELECT
  SELECT COUNT(*) INTO v_count FROM orders_dv;
  DBMS_OUTPUT.PUT_LINE('Documents in view: ' || v_count);

  -- Test that JSON contains _id field
  SELECT json_serialize(data) INTO v_json
  FROM orders_dv
  WHERE ROWNUM = 1;

  IF v_json LIKE '%_id%' THEN
    DBMS_OUTPUT.PUT_LINE('OK: _id field present in JSON output.');
  ELSE
    DBMS_OUTPUT.PUT_LINE('WARNING: _id field not found in JSON output.');
  END IF;

  DBMS_OUTPUT.PUT_LINE('Duality view validation complete.');
END;
/

Related Errors

• ORA-40609 - Cannot create duality view (general duality view creation failure)
• ORA-42692 - Table referenced by duality view has no primary key
• ORA-42646 - Invalid JSON-Relational Duality View definition
• ORA-40441 - JSON syntax error (malformed JSON in duality view operations)
• ORA-00942 - Table or view does not exist (underlying table missing)
• ORA-00955 - Name is already used by existing object (duplicate view name)

Quick Reference

Emergency Response Steps

1. Check the duality view CREATE statement for the @id annotation
2. Verify the underlying table has a primary key constraint
3. Ensure @id is placed on the primary key column(s) at the root level
4. Use the _id: column_name @id syntax for the mapping
5. Include @insert @update @delete if read-write access is needed
6. Test the view with a SELECT after creation

Quick Commands

-- Correct duality view syntax
CREATE JSON RELATIONAL DUALITY VIEW my_dv AS
  my_table @insert @update @delete {
    _id: pk_column @id
    column2
    column3
  };

-- Check table primary key
SELECT constraint_name, column_name
FROM user_cons_columns
WHERE table_name = 'MY_TABLE'
  AND constraint_name IN (
    SELECT constraint_name FROM user_constraints
    WHERE table_name = 'MY_TABLE' AND constraint_type = 'P'
  );

-- List existing duality views
SELECT view_name, root_table_name, status
FROM user_json_duality_views;

-- Query duality view as JSON
SELECT json_serialize(data PRETTY) FROM my_dv;

-- Drop and recreate duality view
DROP VIEW my_dv;

Key Rules

• Every duality view must have @id on the root-level primary key column(s)
• _id alias maps the primary key to the JSON document identifier
• @id annotation tells Oracle which column is the document identifier
• Primary key required on underlying tables for duality view creation
• Composite keys require @id on each primary key column
• DML annotations (@insert @update @delete) are needed for write access