[SQL] แปลงข้อมูลรูปแบบ JSON ให้เป็น Table โดยใช้ Function OPENJSON บน Microsoft SQL Server

JSON format นิยมใช้กันมากในปัจจุบัน เพราะสามารถจัดการและเข้าใจได้ง่าย ซึ่งลักษณะของ JSON จะเป็น string ยาวๆ แต่มี structure ของข้อมูลอยู่ภายในสามารถนำมา Query ได้ ซึ่ง Microsoft SQL Server เองก็มี function ไว้สำหรับ convert ข้อมูลในรูปแบบ JSON ให้แสดงเป็นข้อมูลในรูปแบบ Table ได้ โดยใช้ function ที่ชื่อว่า OPENJSON โดย function นี้จะ support ตั้งแต่ Microsoft SQL Server 2016 เป็นต้นไปเท่านั้น

ก่อนจะใช้งาน OPENJSON ต้อง execute command นี้ก่อนเพื่อให้ database รู้จัก function ดังกล่าว โดน execute เพียงแค่ครั้งเดียวเท่านั้น

ALTER DATABASE <database_name> SET COMPATIBILITY_LEVEL = 130

วิธีใช้งาน function ดังกล่าวแสดงดังตัวอย่าง

DECLARE @json NVARCHAR(4000) = N'{

"FirstName":"John",

"LastName":"Smith",

"Age": 32,

"DOB": "1986-10-07",

"Married": false

}'

SELECT * FROM OPENJSON(@json)

Result

จะเห็นว่า SQL Server จะทำการสร้าง Table จาก JSON String ภายใต้ function OPENJSON แต่ลักษณะจะเป็น table แบบ key/value pair table อยู่

ทีนี้ถ้าเราอยากจะให้มันแสดงเป็นรูปแบบ Table ที่สามารถนำไปใช้งานได้สามารถใช้ With Clause เข้ามาช่วย ดังตัวอย่างต่อไปนี้

SELECT * FROM OPENJSON(@json) WITH (

FirstName NVARCHAR(50) '$.FirstName',

LastName NVARCHAR(100) '$.LastName',

Age int '$.Age',

DOB date '$.DOB',

Married bit '$.Married'

)

Result

จะเห็นว่าแสดงเป็นรูปแบบ Table ทั่วไปที่เราคุ้ยเคยกันแล้ว

ส่วน JSON ที่มีรูปแบบเป็น multi-dimensional structure ก็สามรถ convert เป็นรูปแบบ

Table ได้เช่นกันดังตัวอย่าง

DECLARE @json NVARCHAR(4000) = N'{ "FirstName":"Supot", "LastName":"Saejang", "Age": 32,

"DOB": "1986-10-07",

"Married": false,

"Address": {

"HouseNumber":"45/2",

"Road":"Sukumwit",

"City":"Bangkok"

}}'

รูปแบบ JSON String แบบ multi-dimensional structure โดยสังเกตที่ข้อมูล Address จะเป็น sub data

SELECT * FROM OPENJSON(@json) WITH (

FirstName NVARCHAR(50) '$.FirstName',

LastName NVARCHAR(100) '$.LastName',

Age int '$.Age',

DOB date '$.DOB',

Married bit '$.Married',

HouseNumber VARCHAR(200) '$.Address.HouseNumber',

Road VARCHAR(200) '$.Address.Road',

City VARCHAR(200) '$.Address.City'

)

Result

เราสามารถนำ function OPENJSON มาประยุกต์ใช้ได้หลายรูปแบบ โดยข้อมูลอาจจะเป็นข้อมูลบน table ไม่จำเป็นการสร้างตัวแปรแบบที่อธิบายในบทความก็ได้ อีกทั้งเราสามารถใช้ร่วมกับ Where Clause รวมถึง command ต่างๆ ที่ใช้จัดการกับข้อมูลบน table ได้อีกด้วย

ศึกษาข้อมูลเพิ่มเติมได้จาก

https://docs.microsoft.com/en-us/sql/t-sql/functions/openjson-transact-sql?view=sql-server-2017