Prerequisites: MySQL 9.7+, HeatWave enabled, vector plugin loaded.

Scenario: Knowledge base documents stored in relational tables. JSON Duality View exposes nested JSON documents for LLM consumption. Combine with HeatWave native vector similarity search to implement RAG pipeline.

Step 1 – Create underlying relational tables

-- Main knowledge document table
CREATE TABLE knowledge_doc (
    doc_id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(255) NOT NULL,
    category VARCHAR(100),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
-- Chunk table: segmented text and embedding vectors
CREATE TABLE doc_chunk (
    chunk_id INT PRIMARY KEY AUTO_INCREMENT,
    doc_id INT NOT NULL,
    chunk_text TEXT NOT NULL,
    embedding VECTOR(1536), -- matches OpenAI ada‑002 embedding dimension
    FOREIGN KEY(doc_id) REFERENCES knowledge_doc(doc_id) ON DELETE CASCADE
);
-- Insert sample test data
INSERT INTO knowledge_doc(title,category) VALUES
('MySQL JSON Duality View Intro','Database'),
('HeatWave Vector Search Guide','Database');
INSERT INTO doc_chunk(doc_id,chunk_text,embedding) VALUES
(1,'JSON Duality View combines relational tables and JSON document model',VECTOR('[0.1,0.2,...]')),
(1,'You can write JSON document and it maps automatically to relational tables',VECTOR('[0.11,0.22,...]')),
(2,'HeatWave provides native vector similarity search for RAG applications',VECTOR('[0.3,0.4,...]'));

Step 2 – Create JSON Relational Duality View

CREATE JSON RELATIONAL DUALITY VIEW knowledge_dv AS
SELECT JSON_DUALITY_OBJECT(
    '_id': doc_id,
    'title': title,
    'category': category,
    'created_at': created_at,
    'chunks': (
        SELECT JSON_ARRAYAGG(
            JSON_DUALITY_OBJECT(
                'chunk_id': chunk_id,
                'chunk_text': chunk_text,
                'embedding': embedding
            )
        )
        FROM doc_chunk dc
        WHERE dc.doc_id = knowledge_doc.doc_id
    )
)
FROM knowledge_doc;

Step 3 – Retrieve complete nested JSON document for LLM context

-- Fetch full document as JSON for RAG context injection
SELECT data FROM knowledge_dv WHERE data->>'_id' = 1;

Sample returned JSON payload:

{
  "_id": 1,
  "title": "MySQL JSON Duality View Intro",
  "category": "Database",
  "created_at": "2026‑09‑23 10:00:00",
  "chunks": [
    {
      "chunk_id": 1,
      "chunk_text": "JSON Duality View combines relational tables and JSON document model",
      "embedding": [0.1,0.2,...]
    },
    {
      "chunk_id": 2,
      "chunk_text": "You can write JSON document and it maps automatically to relational tables",
      "embedding": [0.11,0.22,...]
    }
  ]
}

Step 4 – DML: Insert JSON document, auto‑split into underlying tables

INSERT INTO knowledge_dv(data)
VALUES('{
  "_id": 3,
  "title": "RAG with MySQL HeatWave",
  "category": "AI",
  "chunks": [
    {
      "chunk_id": 5,
      "chunk_text": "Use duality view to feed JSON context to LLM for RAG",
      "embedding": [0.5,0.6,...]
    }
  ]
}');

Atomic transaction: JSON payload is decomposed and written atomically into knowledge_doc and doc_chunk. Foreign‑key constraints are enforced automatically.

Step 5 – HeatWave vector search join with Duality View (core RAG query)

-- Retrieve top‑N similar chunks together with full document JSON
SELECT
    kd.data AS full_json_document,
    dc.chunk_text,
    VECTOR_DISTANCE(dc.embedding, VECTOR('[0.12,0.21,...]'), COSINE) AS similarity_score
FROM doc_chunk dc
JOIN knowledge_doc kd ON dc.doc_id = kd.doc_id
WHERE VECTOR_DISTANCE(dc.embedding, VECTOR('[0.12,0.21,...]'), COSINE) < 0.3
ORDER BY similarity_score ASC
LIMIT 3;

• Use high‑rank chunk_text as retrieved context for LLM prompt.
• The full JSON document from duality view can also be passed to LLM for richer context.

Step 6 – Update document through duality view

UPDATE knowledge_dv
SET data = JSON_SET(data, '$.title', 'MySQL JSON Duality View for AI RAG')
WHERE data->>'_id' = 1;

Step 7 – Delete document through duality view

DELETE FROM knowledge_dv WHERE data->>'_id' = 1;

Child rows inside doc_chunk are automatically removed due to ON DELETE CASCADE.

Step 8 – Minimal Python application snippet for RAG workflow

import mysql.connector
import json
conn = mysql.connector.connect(
    host="127.0.0.1",
    user="root",
    password="xxx",
    database="rag_demo"
)
cur = conn.cursor()
query_vec = "[0.12,0.21,...]"
sql = """
SELECT kd.data, dc.chunk_text
FROM doc_chunk dc
JOIN knowledge_doc kd ON dc.doc_id = kd.doc_id
WHERE VECTOR_DISTANCE(dc.embedding, VECTOR(%s), COSINE) <0.3
LIMIT 3
"""
cur.execute(sql, (query_vec,))
rows = cur.fetchall()
context_parts = []
for row in rows:
    full_doc_json = json.loads(row[0])
    chunk_txt = row[1]
    context_parts.append(chunk_txt)
llm_prompt = f"""
Use below context to answer user question:
{chr(10).join(context_parts)}
User question: How JSON Duality View help build AI app?
"""
print(llm_prompt)
cur.close()
conn.close()

Important notes

1. Replace placeholder vector arrays VECTOR('[0.1,0.2,...]') with real embedding output from your embedding model.
2. Grant CREATE JSON RELATIONAL DUALITY VIEW privilege to your database user.

3. Create vector index to accelerate similarity search:

   CREATE VECTOR INDEX idx_chunk_embedding ON doc_chunk(embedding);
   

4. Limitations: Duality view root cannot use arbitrary aggregate functions; child objects must map to primary‑key‑backed tables.