จัดการคอลัมน์และดัชนี JSON

ฐานข้อมูลเชิงสัมพันธ์จะทํางานได้ดีที่สุดเมื่อทุกแถวในตารางมีคอลัมน์เดียวกัน คุณกําหนดโครงสร้างเพียงครั้งเดียว และทุกเรกคอร์ดจะเป็นไปตามนั้น การออกแบบนี้ทํางานได้ดีกับข้อมูล เช่น ลูกค้า ใบสั่ง หรือใบแจ้งหนี้ที่สามารถคาดการณ์ฟิลด์ได้ แต่ข้อมูลบางอย่างแตกต่างกันไปในแต่ละบันทึก แอตทริบิวต์ที่คุณต้องจัดเก็บจะขึ้นอยู่กับชนิดของรายการ แหล่งที่มาของข้อมูล หรือตัวเลือกของผู้ใช้ การออกแบบตารางแบบดั้งเดิมบังคับให้คุณสร้างคอลัมน์จํานวนมากที่ว่างเปล่าสําหรับแถวส่วนใหญ่ หรือแยกข้อมูลออกเป็นหลายตาราง คอลัมน์ JSON มีอีกทางเลือกหนึ่ง: จัดเก็บส่วนที่แปรผันเป็น JSON ในขณะที่เก็บส่วนที่คาดเดาได้ในคอลัมน์ปกติ

ตัวอย่างเช่น แค็ตตาล็อกผลิตภัณฑ์อีคอมเมิร์ซมีฟิลด์ทั่วไป เช่น ชื่อผลิตภัณฑ์ ราคา และประเภทที่ใช้กับทุกรายการ แล็ปท็อปต้องการความเร็วโปรเซสเซอร์และขนาดหน้าจอ และหนังสือต้องการผู้แต่งและคุณลักษณะอื่นๆ เมื่อใช้ JSON คุณจะจัดเก็บฟิลด์ทั่วไปเป็นคอลัมน์และใส่แอตทริบิวต์เฉพาะหมวดหมู่ในคอลัมน์ JSON คุณสามารถเพิ่มประเภทสินค้าใหม่ได้โดยไม่ต้องเปลี่ยนโครงสร้างตาราง

ทําความเข้าใจว่าเมื่อใดควรใช้คอลัมน์ JSON

คอลัมน์ JSON ช่วยให้คุณสามารถสืบค้นและจัดทําดัชนีข้อมูลกึ่งโครงสร้างโดยใช้ไวยากรณ์ SQL ที่คุ้นเคย คุณไม่จําเป็นต้องมีฐานข้อมูล NoSQL แยกต่างหากเพื่อจัดการข้อมูลที่ยืดหยุ่น พิจารณา JSON สําหรับสถานการณ์เหล่านี้:

• การตั้งค่าของผู้ใช้ - การตั้งค่า เช่น ธีม ภาษา และตัวเลือกการแจ้งเตือนจะแตกต่างกันไปตามผู้ใช้แต่ละราย และเปลี่ยนแปลงเมื่อคุณเพิ่มคุณสมบัติ
• การตอบสนอง API - ข้อมูลจากบริการภายนอกมีโครงสร้างที่ซ้อนกันซึ่งอาจเปลี่ยนแปลงเมื่อผู้ให้บริการอัปเดต API ของตน
• บันทึกการตรวจสอบ - เรกคอร์ดที่บันทึกสถานะก่อนและหลังจําเป็นต้องปรับเปลี่ยนตามการพัฒนาสคีมาตารางของคุณ
• แอปพลิเคชันแบบหลายผู้เช่า - ลูกค้าแต่ละรายต้องการฟิลด์แบบกําหนดเองที่แตกต่างกัน
• ข้อมูลเมตาที่ยืดหยุ่น - แท็ก ป้ายชื่อ และคุณสมบัติที่แตกต่างกันไปตามเรกคอร์ดและไม่พอดีกับสคีมาคงที่

สร้างและสืบค้นคอลัมน์ JSON

SQL Server 2025 แนะนําประเภทข้อมูล json ดั้งเดิมที่จัดเก็บเอกสาร JSON ในรูปแบบไบนารีที่ปรับให้เหมาะสมสําหรับการสืบค้นและการจัดการ ประเภทเนทีฟให้การอ่านที่มีประสิทธิภาพมากขึ้น (เอกสารถูกแยกวิเคราะห์แล้ว) การเขียนที่มีประสิทธิภาพมากขึ้น (การอัปเดตสามารถแก้ไขค่าแต่ละค่าได้โดยไม่ต้องเขียนเอกสารทั้งหมดใหม่) และการบีบอัดพื้นที่จัดเก็บข้อมูลที่ดีกว่าเมื่อเทียบกับการจัดเก็บ JSON เป็น NVARCHAR(MAX).

สําหรับ SQL Server รุ่นก่อนหน้า คุณเก็บ JSON ไว้ใน NVARCHAR(MAX) คอลัมน์

ในการอ่านค่าจาก JSON คุณใช้ ฟังก์ชัน JSON เช่น JSON_VALUE การแยกค่าเดียวหรือ JSON_QUERY ส่งคืนวัตถุหรืออาร์เรย์ หากคุณสืบค้นคุณสมบัติ JSON บ่อยๆ คุณสามารถสร้างดัชนีในคอลัมน์ที่คํานวณซึ่งแยกคุณสมบัตินั้นได้

ตัวอย่างต่อไปนี้สร้างตารางที่มีคอลัมน์ JSON แทรกเอกสาร สืบค้นคุณสมบัติเฉพาะ อัปเดตค่า และสร้างดัชนีในฟิลด์ที่เข้าถึงบ่อย:

-- Create table with native JSON type (SQL Server 2025+)
CREATE TABLE ConfigurationData (
    ConfigID INT PRIMARY KEY,
    ConfigSettings JSON NOT NULL
);

-- Insert JSON documents
INSERT INTO ConfigurationData (ConfigID, ConfigSettings) 
VALUES (1, '{"theme":"dark","language":"en","notifications":true}');

INSERT INTO ConfigurationData (ConfigID, ConfigSettings) 
VALUES (2, '{"theme":"light","language":"fr","notifications":false}');

-- Query JSON properties
SELECT ConfigID,
       JSON_VALUE(ConfigSettings, '$.theme') AS Theme,
       JSON_VALUE(ConfigSettings, '$.language') AS Language,
       JSON_QUERY(ConfigSettings, '$') AS FullConfig
FROM ConfigurationData;

-- Update a single property using the modify method (SQL Server 2025+ preview)
UPDATE ConfigurationData
SET ConfigSettings.modify('$.theme', 'light')
WHERE ConfigID = 1;

-- Alternative: JSON_MODIFY works with both JSON and NVARCHAR(MAX) columns
UPDATE ConfigurationData
SET ConfigSettings = JSON_MODIFY(CAST(ConfigSettings AS NVARCHAR(MAX)), '$.notifications', CAST(0 AS BIT))
WHERE ConfigID = 1;

-- Create index on frequently queried JSON property
ALTER TABLE ConfigurationData
ADD ThemeValue AS JSON_VALUE(ConfigSettings, '$.theme');

CREATE INDEX IX_Theme ON ConfigurationData(ThemeValue);

ตัวอย่างนี้สร้างตารางที่มี JSON คอลัมน์ที่จัดเก็บการตั้งค่าคอนฟิกของผู้ใช้ คําสั่งจะ INSERT เพิ่มเอกสาร JSON เป็นสัญพจน์สตริง หากต้องการอ่านค่าเฉพาะ ให้ JSON_VALUE แยกค่าสเกลา เช่น ธีมและภาษา ในขณะที่ JSON_QUERY ส่งคืนออบเจ็กต์ JSON ทั้งหมด .modify()เมธอด (ขณะนี้อยู่ในการแสดงตัวอย่าง) จะอัปเดตคุณสมบัติเดียวโดยไม่ต้องเขียนเอกสารใหม่ทั้งหมด เนื่องจากไม่สามารถใช้ชนิดเป็น json คอลัมน์คีย์ดัชนีได้ ตัวอย่างจึงสร้างคอลัมน์ที่คํานวณซึ่งแยกค่าธีม จากนั้นจัดทําดัชนีคอลัมน์ที่คํานวณนั้น

รวมโครงสร้างเชิงสัมพันธ์และ JSON

คอลัมน์ JSON ทํางานได้ดีที่สุดสําหรับข้อมูลที่แตกต่างกันไปตามเรกคอร์ด ถ้าทุกแถวมีเขตข้อมูลเดียวกันที่มีชนิดข้อมูลที่สอดคล้องกัน คุณจะได้รับการตรวจสอบความถูกต้องของประเภทข้อมูลดั้งเดิม การสืบค้นที่ง่ายขึ้นโดยไม่ต้องใช้ไวยากรณ์เส้นทาง JSON และการจัดทําดัชนีโดยตรงบนคอลัมน์ ใช้ JSON สําหรับส่วนของข้อมูลที่ต้องการความยืดหยุ่น และเก็บส่วนที่คาดเดาได้ไว้ในคอลัมน์ที่พิมพ์

คุณสามารถรวมโครงสร้างเชิงสัมพันธ์กับความยืดหยุ่นของ JSON สําหรับผลิตภัณฑ์ที่ต้องการข้อมูลเมตาแบบแปรผัน ตัวอย่างมีดังนี้:

-- Product with flexible metadata (SQL Server 2025+)
CREATE TABLE ProductMetadata (
    ProductID INT PRIMARY KEY,
    AdditionalAttributes JSON NOT NULL
        CHECK (JSON_PATH_EXISTS(AdditionalAttributes, '$.weight') = 1),
    FOREIGN KEY (ProductID) REFERENCES Product(ProductID)
);

-- Store flexible product attributes
INSERT INTO ProductMetadata (ProductID, AdditionalAttributes) 
VALUES (1, '{"dimensions":{"length":10,"width":5,"height":8},"weight":2.5,"color":"blue"}');

-- Query nested JSON properties
SELECT ProductID,
       JSON_VALUE(AdditionalAttributes, '$.weight') AS Weight,
       JSON_VALUE(AdditionalAttributes, '$.dimensions.length') AS Length
FROM ProductMetadata;

พิจารณาหลักการออกแบบ JSON

ใช้หลักการเหล่านี้เมื่อใช้คอลัมน์ JSON:

• ใช้ JSON สําหรับข้อมูลกึ่งโครงสร้าง - จัดเก็บโครงสร้างข้อมูลที่ยืดหยุ่นซึ่งแตกต่างกันไปตามระเบียน ไม่ใช่ข้อมูลที่มีสคีมาที่สอดคล้องกัน
• จัดทําดัชนีเส้นทางที่สืบค้นบ่อย - สร้างคอลัมน์ที่คํานวณด้วยดัชนีในคุณสมบัติ JSON ที่คุณสืบค้นบ่อยๆ
• ตรวจสอบคุณสมบัติที่จําเป็น - ใช้ CHECK ข้อจํากัดเพื่อให้แน่ใจว่า JSON_PATH_EXISTS มีฟิลด์ที่จําเป็น
• สร้างสมดุลระหว่างความยืดหยุ่นกับโครงสร้าง - เก็บข้อมูลที่คาดการณ์ได้ในคอลัมน์ปกติและใช้ JSON สําหรับส่วนที่แปรผันเท่านั้น

คอลัมน์ JSON ให้ความยืดหยุ่นของ Schema สําหรับข้อมูลตัวแปรในขณะที่ยังคงรักษาความสามารถในการสืบค้น SQL แต่ควรเสริมแทนที่จะแทนที่การออกแบบเชิงสัมพันธ์สําหรับข้อมูลที่มีโครงสร้าง