JSON คืออะไร? Query ใน SQL Server พร้อมตัวอย่าง

JSON คืออะไร? JSON เป็นรูปแบบข้อความสำหรับแลกเปลี่ยนข้อมูล ใช้คู่ key–value, object และ array ได้ เช่นรายการสั่งซื้อที่มีสินค้าหลายรายการ บทนี้สาธิต JSON_VALUE, JSON_QUERY และ OPENJSON ใน SQL Server พร้อมคำตอบที่ตรวจได้ โดยใช้ตัวแปรในหน่วยความจำและไม่แก้ตารางจริง

อ่านโครงสร้าง JSON ก่อน Query

Object ใช้เครื่องหมายปีกกา ส่วน Array ใช้วงเล็บเหลี่ยม ชื่อ key และค่าข้อความต้องใช้ double quotes ตัวเลขไม่ต้องใส่ quotes หากต้องการให้เป็นตัวเลขจริง หลีกเลี่ยง trailing comma และตรวจ JSON ก่อนประมวลผล

หากยังไม่รู้จักระบบฐานข้อมูล อ่านMicrosoft SQL Server คืออะไร ก่อน ตัวอย่างนี้เป็นการใช้ฟังก์ชัน JSON ภายใน SQL Server ไม่ใช่ภาษาคิวรีชื่อ JSON ที่ใช้แทน SQL ทั้งหมด

JSON_VALUE, JSON_QUERY และ OPENJSON ต่างกันอย่างไร

คู่มือJSON ใน SQL Server ระบุความสามารถแยกตามเวอร์ชัน บทนี้ใช้ nvarchar(max) และฟังก์ชันพื้นฐานที่มีใน SQL Server 2016 ขึ้นไป ไม่ต้องใช้ native json type ของรุ่นใหม่ สำหรับ OPENJSON ให้ใช้ฐานข้อมูลที่มี compatibility level 130 ขึ้นไปตามเงื่อนไขทั่วไปในเอกสาร OPENJSON อย่าเปลี่ยน compatibility ของฐานข้อมูลผลิตจริงเพียงเพื่อทดลองบทนี้

ตัวอย่าง Query รายการสั่งซื้อ

เปิดหน้าต่าง query ในฐานข้อมูลทดสอบที่รองรับ แล้วรันโค้ดทั้งหมดเป็นชุดเดียว เพราะตัวแปร @doc มีขอบเขตใน batch นี้

DECLARE @doc nvarchar(max) = N'{
  "orderId": 1001,
  "customer": {"name": "Nida"},
  "items": [
    {"sku": "Pen", "qty": 2, "price": 20},
    {"sku": "Book", "qty": 1, "price": 100}
  ]
}';

SELECT ISJSON(@doc) AS IsValid,
       JSON_VALUE(@doc, '$.customer.name') AS CustomerName,
       JSON_QUERY(@doc, '$.items') AS ItemsJson;

SELECT sku, qty, price, qty * price AS LineTotal
FROM OPENJSON(@doc, '$.items')
WITH (
    sku nvarchar(50) '$.sku',
    qty int '$.qty',
    price decimal(10,2) '$.price'
)
ORDER BY sku;

SELECT SUM(qty * price) AS OrderTotal
FROM OPENJSON(@doc, '$.items')
WITH (qty int '$.qty', price decimal(10,2) '$.price');

ผลลัพธ์ที่ควรได้

คำสั่งแรกได้ IsValid = 1, CustomerName = Nida และ ItemsJson เป็น array สินค้าสองรายการ คำสั่งที่สองเรียงตาม sku จึงได้ Book ก่อน Pen ดังตาราง

คำสั่งสุดท้ายได้ OrderTotal = 140.00 โดยไม่รวมภาษีหรือส่วนลด การระบุชนิดข้อมูลใน WITH ช่วยให้คำนวณตามชนิดที่ตั้งใจ ไม่ปล่อยให้ราคาเป็นข้อความ

ทดลองกรณีผิดพลาดเพื่อเข้าใจ path

• ถ้าใช้ JSON_VALUE กับ $.items ซึ่งเป็น array จะไม่ได้ค่าตามที่ต้องการในโหมด lax ให้ใช้ JSON_QUERY

• ถ้าใช้ JSON_QUERY กับ $.customer.name ซึ่งเป็น scalar ให้เปลี่ยนเป็น JSON_VALUE

• ชื่อ key มีความสำคัญต่อการจับคู่ ตรวจตัวพิมพ์เล็กใหญ่และ path ให้ตรงข้อมูลจริง

• JSON ที่รูปแบบผิดอาจทำให้ query error ให้ตรวจ ISJSON ก่อนเข้าส่วนประมวลผล

• ค่า NULL อาจมาจาก path ที่ไม่มีหรือชนิดไม่ตรงในโหมดที่ใช้ อย่าสรุปว่าไม่มีรายการขายทันที

เกณฑ์ตรวจรับเพิ่มเติม

นำ JSON ไปใช้กับงานข้อมูล

ข้อมูลจาก API อาจต้องแยก nested object และ array ก่อนทำรายงาน ขั้นตอนนี้เป็นส่วนหนึ่งของETL ควรบันทึกกติกาการแปลงและเก็บหลักฐาน input ที่เหมาะสม เมื่อเลือกระหว่าง SQL, Power Query และเครื่องมือ ETL อย่าง SSIS ให้พิจารณาขนาดข้อมูล การจัดตารางงาน และผู้รับผิดชอบ ไม่เลือกเพียงเพราะเขียนสูตรแรกได้สั้นกว่า

คำถามที่พบบ่อย

เก็บทุกข้อมูลเป็น JSON ดีกว่าตารางไหม?

ไม่เสมอ ข้อมูลที่มีโครงสร้างชัดและต้อง join หรือบังคับกฎความสัมพันธ์ยังเหมาะกับตารางเชิงสัมพันธ์ อาจใช้ JSON เฉพาะส่วนที่โครงสร้างยืดหยุ่น

ISJSON = 1 แปลว่าข้อมูลธุรกิจถูกไหม?

ไม่ใช่ เป็นการตรวจรูปแบบ ไม่ได้ตรวจว่าราคาเป็นบวกหรือรหัสลูกค้ามีจริง

SQL Server รุ่นใหม่มีความสามารถเพิ่มไหม?

มีความสามารถที่ต่างตามรุ่น ให้ตรวจ Applies to และสถานะของฟีเจอร์ในเอกสาร Microsoft ก่อนนำ syntax ใหม่ไปใช้กับระบบเดิม

หลักสูตรที่เกี่ยวข้อง

Microsoft SQL Server Essential · Querying Data with T-SQL · SQL Server Programming - Stored Procedure · Microsoft SQL Server Database Development

เลือกเส้นทางเรียนตามพื้นฐานและหน้าที่งาน ตรวจรายละเอียดหลักสูตรก่อนสมัคร ไม่ได้หมายความว่าทุกหลักสูตรครอบคลุมทุกฟีเจอร์ในบทนี้

วิดีโอเดิมเพื่อประกอบบริบท

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