تبدیل داده JSON به جدول رابطه‌ای توسط OPENJSON در SQL Server

SQL Server, JSON Functions, JSON VALUE, JSON QUERY, JSON MODIFY, FOR JSON, ISJSON, JSON Path, WITH Clause, CROSS APPLY, OUTER APPLY, Table Valued Function, SQL Server JSON, Parse JSON SQL Server, Nested JSON, SQL Server Performance, Computed Column, SQL Server 2016, SQL Server 2019, SQL Server 2022, پردازش JSON در SQL Server, OPENJSON, تبدیل JSON به جدول, توابع JSON در SQL Server, آموزش OPENJSON, JSON VALUE, JSON QUERY, JSON MODIFY, FOR JSON, ISJSON, JSON Path, JSON در SQL Server, پردازش داده JSON, تبدیل JSON به Table, آموزش SQL Server, بهینه سازی SQL Server, عملکرد OPENJSON, Compatibility Level SQL Server, CROSS APPLY, OUTER APPLY, WITH Clause, Nested JSON, SQL Server JSON Functions

فهرست مطالب

در سال‌های اخیر، تبادل داده بین سیستم‌های سازمانی به‌شدت به سمت فرمت JSON حرکت کرده است. سرویس‌های REST، صف‌های پیام‌رسانی، لاگ‌های اپلیکیشن و حتی برخی جداول Configuration در سیستم‌های Legacy، اغلب داده را در قالب JSON ذخیره یا منتقل می‌کنند. این روند تیم‌های توسعه SQL Server را با یک چالش همیشگی روبه‌رو کرده است: چگونه می‌توان داده‌ای که در قالب یک رشته JSON ذخیره شده را در دنیای رابطه‌ای و جدولی SQL Server پردازش کرد؟

پیش از معرفی پشتیبانی داخلی JSON در SQL Server 2016، پاسخ به این سؤال معمولاً نیازمند Parse کردن دستی رشته در لایه اپلیکیشن، استفاده از CLR سفارشی یا نوشتن توابع پیچیده مبتنی بر رشته بود. تابع OPENJSON این مسئله را در سطح موتور پایگاه داده حل کرده و امکان تبدیل مستقیم یک سند JSON به یک نتیجه جدولی قابل Query را فراهم می‌کند.

تعریف OPENJSON و جایگاه آن در خانواده توابع JSON

OPENJSON یک تابع Table-Valued در SQL Server است که یک رشته متنی حاوی JSON را به‌عنوان ورودی می‌گیرد و آن را به یک مجموعه از سطر و ستون تبدیل می‌کند که می‌توان مانند هر جدول دیگری روی آن SELECT، JOIN یا فیلتر اعمال کرد.

نکته مهمی که باید در ابتدا روشن شود این است که SQL Server یک نوع داده اختصاصی برای JSON ندارد. برخلاف XML که دارای نوع داده مستقل xml است، JSON در SQL Server همیشه در قالب یک ستون از نوع NVARCHAR ذخیره می‌شود. OPENJSON، JSON_VALUE، JSON_QUERY، JSON_MODIFY و ISJSON همگی روی همین رشته‌های متنی عمل می‌کنند، هرکدام با هدف متفاوتی.

در این خانواده توابع، هرکدام نقش مشخصی دارند. JSON_VALUE یک مقدار اسکالر واحد را از یک مسیر مشخص در JSON استخراج می‌کند. JSON_QUERY یک بخش کامل از JSON، مانند یک Object یا Array، را استخراج می‌کند. JSON_MODIFY امکان تغییر مقدار یک فیلد در JSON را بدون نیاز به بازسازی کامل رشته فراهم می‌کند. FOR JSON مسیر معکوس را طی می‌کند و نتیجه یک Query رابطه‌ای را به JSON تبدیل می‌کند. ISJSON اعتبار یک رشته را از نظر ساختار JSON بررسی می‌کند. OPENJSON در میان این توابع، تنها گزینه‌ای است که می‌تواند یک سند کامل JSON، شامل چندین فیلد و حتی آرایه‌ای از رکوردها، را یک‌جا به یک نتیجه جدولی کامل تبدیل کند.

Compatibility Level پایگاه داده

تابع OPENJSON از SQL Server 2016 معرفی شده است، اما صرف نصب SQL Server 2016 یا نسخه‌های جدیدتر برای استفاده از آن کافی نیست. دیتابیس باید روی Compatibility Level 130 یا بالاتر قرار داشته باشد، در غیر این صورت SQL Server این تابع را شناسایی نخواهد کرد و اجرای Query با خطا متوقف می‌شود. این نکته به‌خصوص در سازمان‌هایی که دیتابیس‌های قدیمی را از نسخه‌های پیش از 2016 مهاجرت داده‌اند اهمیت زیادی دارد، زیرا در بسیاری از این موارد Compatibility Level به‌صورت عمدی روی سطح پایین‌تر نگه داشته شده تا رفتار برنامه‌های قدیمی تغییر نکند.

SELECT name, compatibility_level
FROM sys.databases
WHERE name = DB_NAME();

در صورتی که Compatibility Level پایین‌تر از 130 باشد، می‌توان آن را با دستور زیر تغییر داد، البته پیش از این تغییر باید تأثیر آن بر سایر بخش‌های اپلیکیشن به‌طور کامل بررسی شود.

ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 150;

بنابراین در زمان مهاجرت دیتابیس‌های قدیمی یا استفاده از OPENJSON در یک محیط سازمانی ناآشنا، بررسی Compatibility Level باید همیشه یکی از اولین اقدامات تیم توسعه باشد.

حالت پیش‌فرض OPENJSON

اگر OPENJSON بدون مشخص کردن ساختار خروجی فراخوانی شود، خروجی آن یک جدول با سه ستون ثابت خواهد بود: key، value و type. این حالت برای بررسی سریع ساختار یک JSON ناشناخته یا پردازش JSONهایی با ساختار پویا بسیار مفید است.

DECLARE @json NVARCHAR(MAX) = N'
{
    "OrderID": 1024,
    "CustomerName": "شرکت فناوران پارس",
    "OrderDate": "2025-03-15",
    "Amount": 45000000,
    "IsPaid": true
}';

SELECT [key], [value], [type]
FROM OPENJSON(@json);

نتیجه این Query به شکل زیر خواهد بود.

key	value	type
OrderID	1024	2
CustomerName	شرکت فناوران پارس	1
OrderDate	2025-03-15	1
Amount	45000000	2
IsPaid	true	3

ستون type مقداری عددی است که نوع داده هر فیلد را در JSON اصلی مشخص می‌کند. مقدار صفر به معنای Null، یک به معنای رشته متنی، دو به معنای عدد، سه به معنای مقدار منطقی True یا False و چهار به معنای یک Array یا Object تودرتو است. توجه به این ستون برای پردازش صحیح مقادیر، به‌خصوص هنگام تبدیل نوع داده در مراحل بعدی، ضروری است.

مفهوم JSON Path و نحوه آدرس‌دهی به فیلدها

پیش از بررسی حالت WITH و آرگومان دوم OPENJSON، باید با مفهوم JSON Path آشنا شد، زیرا هر دو این قابلیت‌ها بر پایه آن کار می‌کنند. JSON Path روشی استاندارد برای آدرس‌دهی به یک فیلد یا بخش مشخص در ساختار JSON است، شبیه به نقشی که XPath برای XML ایفا می‌کند.

نماد $ نشان‌دهنده ریشه سند JSON است و هر مسیر همیشه از همین نقطه آغاز می‌شود. برای دسترسی به یک فیلد ساده در سطح اول سند، کافی است نام آن فیلد پس از $ و یک نقطه نوشته شود، مانند $.CustomerName. برای دسترسی به یک فیلد داخل یک Object تودرتو، مسیر با نقطه ادامه پیدا می‌کند، مانند $.Customer.Name که به فیلد Name داخل Object با نام Customer اشاره دارد. برای دسترسی به یک عنصر مشخص در یک آرایه، از براکت و شماره اندیس استفاده می‌شود، مانند $.Items[0] که به اولین عنصر آرایه Items اشاره می‌کند، در حالی که $.Items[*] به معنای تمام عناصر آن آرایه است و معمولاً در ترکیب با OPENJSON برای پردازش کل آرایه به کار می‌رود، نه برای استخراج یک مقدار تکی.

درک درست این نمادها برای نوشتن صحیح آرگومان دوم OPENJSON و همچنین مسیرهای استفاده‌شده در JSON_VALUE و JSON_QUERY ضروری است و در ادامه مقاله بارها به کار خواهد رفت.

آرگومان دوم OPENJSON انتخاب مسیر مشخص از سند JSON

OPENJSON دارای دو آرگومان اصلی است:

رشته JSON
مسیر شروع پردازش (اختیاری)

اول همیشه رشته JSON است.
دوم که اختیاری است، یک JSON Path مشخص می‌کند که OPENJSON باید پردازش خود را از همان نقطه در سند آغاز کند، نه از ریشه کامل آن.

تعریف مسیر اختصاص‏ی‏‏‏ برای هر ستون در بند WITH

علاوه بر مسیر کلی که به‌عنوان آرگومان دوم OPENJSON مشخص می‌شود، می‌توان در بند WITH برای هر ستون به‌صورت جداگانه یک JSON Path تعریف کرد. این قابلیت زمانی مفید است که:

نام ستون خروجی با نام فیلد در JSON متفاوت باشد
ساختار JSON تودرتو باشد
بخواهیم فقط یک زیرمسیر خاص را برای یک ستون استخراج کنیم

ساختار کلی به شکل زیر است:

OPENJSON(@json, '$.Rooعبارت AS JSON در بند WITH نکته‌ای کلیدی است که باید به‌دقت رعایت شود. بدون این عبارت، OPENJSON تلاش می‌کند مقدار Items را به‌عنوان یک رشته ساده تفسیر کند که منجر به نتیجه Null یا خطا خواهد tPath')
WITH
(
    ColumnName DataType '$.Custom.Path',
    AnotherColumn DataType '$.Another.Path'
);

استفاده از آرگومان دوم به‌خصوص زمانی مفید است که داده موردنیاز در یک سطح داخلی از JSON قرار دارد و نیازی به پردازش سطح بیرونی سند نیست.

DECLARE @json NVARCHAR(MAX) = N'
{
    "OrderID": 1024,
    "CustomerName": "شرکت فناوران پارس",
    "Items":
    [
        { "ProductName": "لپ‌تاپ", "Quantity": 3, "UnitPrice": 12000000 },
        { "ProductName": "مانیتور", "Quantity": 5, "UnitPrice": 3200000 }
    ]
}';

SELECT *
FROM OPENJSON(@json, '$.Items')
WITH
(
    ProductName NVARCHAR(200),
    Quantity INT,
    UnitPrice DECIMAL(18,2)
);

در این مثال، به‌جای پردازش کل سند و سپس رسیدن به آرایه Items با یک OPENJSON جداگانه، مستقیماً با آرگومان دوم به مسیر $.Items اشاره شده است و OPENJSON از همان نقطه شروع به تولید ردیف می‌کند. این روش در مواردی که فقط بخش داخلی JSON موردنیاز است و اطلاعات سطح بیرونی سند اهمیتی ندارد، از ترکیب OPENJSON با CROSS APPLY ساده‌تر و مستقیم‌تر است. با این حال، اگر همزمان به فیلدهای سطح بیرونی مانند OrderID و به آرایه Items نیاز باشد، همچنان باید از الگوی ترکیبی OPENJSON و APPLY استفاده کرد که در ادامه توضیح داده می‌شود.

حالت WITH در OPENJSON برای تعریف ساختار خروجی

در پروژه‌های واقعی، معمولاً ساختار JSON از قبل مشخص است و نیازی به دریافت خروجی به‌صورت key-value نیست. در چنین شرایطی، استفاده از بند WITH امکان تعریف مستقیم نام و نوع داده ستون‌های خروجی را فراهم می‌کند و نتیجه آن یک جدول با ساختار دقیقاً منطبق بر نیاز Query است.

DECLARE @json NVARCHAR(MAX) = N'
{
    "OrderID": 1024,
    "CustomerName": "شرکت فناوران پارس",
    "OrderDate": "2025-03-15",
    "Amount": 45000000,
    "IsPaid": true
}';

SELECT *
FROM OPENJSON(@json)
WITH
(
    OrderID INT,
    CustomerName NVARCHAR(200),
    OrderDate DATE,
    Amount DECIMAL(18,2),
    IsPaid BIT
);

در این حالت، خروجی یک ردیف واحد با ستون‌های دقیقاً مشخص‌شده خواهد بود و نوع داده هر ستون نیز مطابق تعریف WITH تبدیل می‌شود. این روش برای Integration با جداول موجود در پایگاه داده، به‌مراتب مناسب‌تر از حالت پیش‌فرض key-value است، زیرا نتیجه مستقیماً قابل INSERT در یک جدول رابطه‌ای استاندارد است.

رفتار OPENJSON در برابر تبدیل نوع داده نامعتبر

نکته‌ای که باید در طراحی WITH با دقت در نظر گرفته شود، رفتار OPENJSON زمانی است که مقدار موجود در JSON با نوع داده تعریف‌شده در WITH سازگار نیست. برای مثال اگر ستونی مانند Amount در WITH به‌صورت INT تعریف شود، اما مقدار واقعی در JSON یک رشته متنی غیرعددی مانند "نامشخص" باشد، رفتار موتور بسته به تنظیمات LAX یا STRICT مسیر متفاوت است.

باید توجه داشت که strict و lax تنها نحوه برخورد SQL Server با وجود یا عدم وجود مسیر JSON را کنترل می‌کنند و ارتباطی با تبدیل نوع داده ندارند. اگر مسیر موردنظر وجود نداشته باشد، در حالت lax مقدار NULL بازگردانده می‌شود، اما در حالت strict خطا ایجاد خواهد شد. در مقابل، اگر مسیر وجود داشته باشد ولی مقدار آن قابل تبدیل به نوع داده تعریف‌شده در WITH نباشد، مانند تبدیل رشته "ABC" به INT، مستقل از strict یا lax خطای تبدیل نوع داده رخ خواهد داد.

SELECT *
FROM OPENJSON(@json)
WITH
(
    OrderID INT 'strict $.OrderID',
    Amount DECIMAL(18,2)
);

با افزودن پیشوند strict به مسیر یک ستون، در صورت نبود آن مسیر در JSON، به‌جای بازگرداندن NULL، یک خطای صریح تولید می‌شود که در برخی سناریوهای اعتبارسنجی سخت‌گیرانه مفید است.

پردازش آرایه‌ای از اشیاء JSON

سناریوی بسیار رایج‌تر در پروژه‌های سازمانی، دریافت یک آرایه از چندین رکورد در قالب JSON است، مانند پاسخ یک Web API یا پیام دریافتی از یک صف پیام‌رسانی.

DECLARE @json NVARCHAR(MAX) = N'
[
    { "OrderID": 1024, "CustomerName": "شرکت فناوران پارس", "Amount": 45000000 },
    { "OrderID": 1025, "CustomerName": "گروه صنعتی البرز", "Amount": 72000000 },
    { "OrderID": 1026, "CustomerName": "بازرگانی نوین تجارت", "Amount": 31000000 }
]';

SELECT *
FROM OPENJSON(@json)
WITH
(
    OrderID INT,
    CustomerName NVARCHAR(200),
    Amount DECIMAL(18,2)
);

در این حالت، OPENJSON به‌ازای هر عنصر آرایه یک ردیف مستقل در خروجی تولید می‌کند و نتیجه دقیقاً همان ساختاری است که برای درج مستقیم در یک جدول سفارش‌ها موردنیاز است.

INSERT INTO Orders (OrderID, CustomerName, Amount)
SELECT OrderID, CustomerName, Amount
FROM OPENJSON(@json)
WITH
(
    OrderID INT,
    CustomerName NVARCHAR(200),
    Amount DECIMAL(18,2)
);

این الگو یکی از پرکاربردترین کاربردهای OPENJSON در پروژه‌های سازمانی است، به‌خصوص در سناریوهایی که یک اپلیکیشن یا سرویس بیرونی، مجموعه‌ای از رکوردها را یک‌جا در قالب JSON برای درج گروهی رکوردها به Stored Procedure ارسال می‌کند و از این طریق نیاز به چندین فراخوانی جداگانه INSERT از سمت اپلیکیشن برطرف می‌شود.

پردازش ساختارهای JSON تودرتو با استفاده از OPENJSON و APPLY

بسیاری از اسناد JSON واقعی، ساختار تودرتو دارند؛ یعنی یک شیء اصلی شامل یک یا چند آرایه یا شیء داخلی است. برای پردازش این نوع ساختار، به‌خصوص زمانی که هم فیلدهای سطح بیرونی و هم آرایه داخلی موردنیاز باشند، باید OPENJSON را با APPLY ترکیب کرد.

DECLARE @json NVARCHAR(MAX) = N'
{
    "OrderID": 1024,
    "CustomerName": "شرکت فناوران پارس",
    "OrderDate": "2025-03-15",
    "Items":
    [
        { "ProductName": "لپ‌تاپ", "Quantity": 3, "UnitPrice": 12000000 },
        { "ProductName": "مانیتور", "Quantity": 5, "UnitPrice": 3200000 }
    ]
}';

SELECT
    o.OrderID,
    o.CustomerName,
    o.OrderDate,
    i.ProductName,
    i.Quantity,
    i.UnitPrice
FROM OPENJSON(@json)
WITH
(
    OrderID INT,
    CustomerName NVARCHAR(200),
    OrderDate DATE,
    Items NVARCHAR(MAX) AS JSON
) AS o
CROSS APPLY OPENJSON(o.Items)
WITH
(
    ProductName NVARCHAR(200),
    Quantity INT,
    UnitPrice DECIMAL(18,2)
) AS i;

در این Query، ابتدا سطح بیرونی JSON با OPENJSON اول پردازش می‌شود. ستون Items با استفاده از عبارت AS JSON به‌عنوان یک بخش JSON خام نگه داشته می‌شود، نه یک مقدار اسکالر ساده. سپس با CROSS APPLY، یک OPENJSON دوم روی همین بخش اجرا می‌شود تا آرایه اقلام سفارش نیز به سطر و ستون تبدیل شود.

نتیجه نهایی، ترکیبی از اطلاعات سطح سفارش و اطلاعات هر قلم کالا در قالب یک نتیجه صاف و رابطه‌ای است، دقیقاً مشابه آنچه از یک JOIN بین دو جدول Orders و OrderDetails انتظار می‌رود.

تفاوت CROSS APPLY و OUTER APPLY در ترکیب با OPENJSON

مثال بالا از CROSS APPLY استفاده می‌کند، اما این انتخاب یک فرض مهم دارد: اینکه فیلد Items همیشه در سند JSON وجود دارد و حداقل یک عنصر در آن آرایه قرار دارد. در پروژه‌های واقعی، این فرض همیشه برقرار نیست. اگر یک سفارش خاص فاقد هیچ‌گونه Item باشد یا فیلد Items اصلاً در JSON آن رکورد وجود نداشته باشد، رفتار CROSS APPLY باعث می‌شود آن سفارش به‌طور کامل از نتیجه نهایی حذف شود، دقیقاً مشابه رفتار INNER JOIN در نبود رکورد متناظر.

اگر هدف این باشد که سفارش‌های فاقد Item نیز در نتیجه باقی بمانند، هرچند با مقادیر NULL در ستون‌های مربوط به Item، باید از OUTER APPLY استفاده کرد.

SELECT
    o.OrderID,
    o.CustomerName,
    i.ProductName,
    i.Quantity,
    i.UnitPrice
FROM OPENJSON(@json)
WITH
(
    OrderID INT,
    CustomerName NVARCHAR(200),
    Items NVARCHAR(MAX) AS JSON
) AS o
OUTER APPLY OPENJSON(o.Items)
WITH
(
    ProductName NVARCHAR(200),
    Quantity INT,
    UnitPrice DECIMAL(18,2)
) AS i;

با استفاده از OUTER APPLY، حتی اگر Items برای یک سفارش خالی یا Null باشد، ردیف مربوط به آن سفارش با مقادیر NULL در ستون‌های سمت Item همچنان در نتیجه باقی می‌ماند. انتخاب بین این دو باید بر اساس نیاز واقعی گزارش انجام شود؛ اگر سفارش‌های بدون Item از نظر تحلیلی بی‌معنا هستند و باید کنار گذاشته شوند، CROSS APPLY کافی است، اما اگر لازم است تمام سفارش‌ها صرف‌نظر از وجود Item در نتیجه دیده شوند، OUTER APPLY انتخاب صحیح است.

استفاده از OPENJSON برای پردازش لیست‌های ساده به‌جای Split String

یکی از کاربردهای کمتر شناخته‌شده اما بسیار کاربردی OPENJSON، جایگزینی توابعی مانند STRING_SPLIT برای پردازش لیستی از مقادیر ورودی به یک Stored Procedure است. اگر لیست ورودی در قالب یک آرایه ساده JSON ارسال شود، OPENJSON می‌تواند آن را مستقیماً به یک جدول تبدیل کند.

DECLARE @productIds NVARCHAR(MAX) = N'[101, 205, 340, 512]';

SELECT p.ProductID, p.ProductName, p.ListPrice
FROM Products p
WHERE p.ProductID IN
(
    SELECT [value]
    FROM OPENJSON(@productIds)
);

در این مثال، آرایه ساده‌ای از شناسه محصولات که از لایه اپلیکیشن ارسال شده، با OPENJSON به یک لیست از مقادیر تبدیل و در بند IN استفاده می‌شود. این الگو به‌خصوص در APIهایی که پارامترهای چندگانه را در قالب JSON به Stored Procedure ارسال می‌کنند، جایگزین تمیزتری نسبت به روش‌های قدیمی‌تر مانند Table-Valued Parameter یا Split کردن رشته کاماجدا است.

نقش ISJSON در اعتبارسنجی پیش از پردازش

پیش از اجرای OPENJSON روی یک ورودی، به‌خصوص در Stored Procedureهایی که JSON را از منابع بیرونی یا غیرقابل اعتماد دریافت می‌کنند، بررسی معتبر بودن ساختار JSON با تابع ISJSON یک اقدام ضروری برای جلوگیری از خطاهای اجرایی است.

DECLARE @json NVARCHAR(MAX) = N'{ "OrderID": 1024, "Amount": 45000000 }';

IF ISJSON(@json) = 1
BEGIN
    SELECT *
    FROM OPENJSON(@json)
    WITH
    (
        OrderID INT,
        Amount DECIMAL(18,2)
    );
END
ELSE
BEGIN
    THROW 51000, N'ورودی JSON نامعتبر است.', 1;
END;

اگر رشته ورودی ساختار معتبر JSON نداشته باشد، ISJSON مقدار صفر برمی‌گرداند و می‌توان پیش از رسیدن به OPENJSON، خطا را به‌شکل کنترل‌شده مدیریت کرد. بدون این بررسی، اجرای مستقیم OPENJSON روی یک رشته نامعتبر باعث بروز خطای اجرایی غیرقابل پیش‌بینی در میانه Stored Procedure می‌شود که مدیریت آن در لایه اپلیکیشن دشوارتر است.

استخراج مقدار تکی با JSON_VALUE در کنار OPENJSON

در برخی سناریوها نیازی به تبدیل کامل یک JSON به جدول نیست و فقط یک یا دو مقدار اسکالر از آن موردنیاز است. در چنین حالتی، استفاده از JSON_VALUE به‌تنهایی، بدون درگیر کردن OPENJSON، ساده‌تر و کم‌هزینه‌تر است.

DECLARE @json NVARCHAR(MAX) = N'{ "OrderID": 1024, "CustomerName": "شرکت فناوران پارس" }';

SELECT
    JSON_VALUE(@json, '$.OrderID') AS OrderID,
    JSON_VALUE(@json, '$.CustomerName') AS CustomerName;

این روش زمانی مناسب است که تنها یک یا چند مقدار مشخص از یک سند JSON ساده، بدون آرایه یا ساختار تودرتو، موردنیاز باشد. اما به محض آنکه نیاز به پردازش یک آرایه از رکوردها یا استخراج چندین ستون از یک ساختار تکراری پیش بیاید، OPENJSON انتخاب مناسب‌تری نسبت به فراخوانی مکرر JSON_VALUE برای هر فیلد است.

بروزرسانی JSON با JSON_MODIFY پیش از ذخیره‌سازی

در سناریوهایی که یک ستون JSON در جدول ذخیره شده و نیاز به تغییر یک فیلد خاص در آن وجود دارد، بدون آنکه کل رشته بازنویسی شود، JSON_MODIFY کاربرد دارد. اگرچه این تابع مستقیماً با OPENJSON ترکیب نمی‌شود، اما اغلب در همان لایه‌ای از کد به کار می‌رود که OPENJSON برای خواندن داده استفاده شده است.

UPDATE Orders
SET OrderDetails = JSON_MODIFY(OrderDetails, '$.IsPaid', CAST(1 AS BIT))
WHERE OrderID = 1024;

این Query تنها مقدار فیلد IsPaid را در ستون JSON تغییر می‌دهد، بدون آنکه سایر فیلدهای موجود در آن دست‌نخورده باقی نمانند.

مسیر معکوس تبدیل نتیجه Query به JSON با FOR JSON

در مقابل OPENJSON که JSON را به جدول تبدیل می‌کند، بند FOR JSON مسیر معکوس را طی می‌کند و نتیجه یک Query رابطه‌ای معمولی را به فرمت JSON تبدیل می‌کند. این قابلیت معمولاً در ساخت پاسخ برای APIهایی که مستقیماً از SQL Server سرویس‌دهی می‌شوند کاربرد دارد.

SELECT OrderID, CustomerName, Amount
FROM Orders
WHERE OrderID = 1024
FOR JSON PATH;

با اینکه FOR JSON و OPENJSON از نظر کاربرد دقیقاً برعکس یکدیگر عمل می‌کنند، در بسیاری از معماری‌های Integration سازمانی، این دو در کنار هم استفاده می‌شوند؛ برای مثال داده از یک سیستم با FOR JSON خروجی گرفته می‌شود، از طریق یک Message Queue منتقل می‌شود و در سیستم مقصد با OPENJSON مجدداً به جدول تبدیل می‌شود.

مقایسه پردازش JSON و XML در SQL Server

از آنجا که SQL Server سال‌ها پیش از پشتیبانی از JSON، پشتیبانی کاملی از XML با نوع داده مستقل و زبان XQuery داشته است، مقایسه این دو مسیر برای تیم‌هایی که پیش‌تر با XML کار کرده‌اند مفید است. جدول زیر توابع و مفاهیم معادل در این دو مسیر را نشان می‌دهد.

XML	JSON
نوع داده مستقل xml	ذخیره در قالب NVARCHAR
زبان XQuery	JSON Path
تابع nodes برای تبدیل به سطر و ستون	OPENJSON
تابع value برای استخراج مقدار تکی	JSON_VALUE
تابع query برای استخراج بخشی از سند	JSON_QUERY
دستور FOR XML برای تولید خروجی	دستور FOR JSON برای تولید خروجی
XML Index	معادل مستقیمی ندارد

تفاوت بنیادی این دو مسیر در سطح موتور پایگاه داده این است که XML به دلیل داشتن نوع داده اختصاصی، امکاناتی مانند XML Index را در اختیار توسعه‌دهنده قرار می‌دهد، در حالی که JSON چنین نوع داده و Indexگذاری اختصاصی ندارد و همان‌طور که در بخش Performance این مقاله بررسی می‌شود، برای Indexگذاری باید به روش‌های غیرمستقیم مانند ستون محاسباتی متوسل شد.

محدودیت‌های OPENJSON

اگرچه OPENJSON ابزار قدرتمندی برای پردازش JSON است، اما محدودیت‌هایی نیز دارد. این تابع تنها روی داده‌های معتبر JSON عمل می‌کند و از فرمت‌هایی مانند JSON5 پشتیبانی نمی‌کند. همچنین امکان به‌روزرسانی مستقیم خروجی OPENJSON وجود ندارد، زیرا خروجی آن یک مجموعه موقتی از داده‌هاست. علاوه بر این، OPENJSON تنها در Compatibility Level 130 یا بالاتر قابل استفاده است.

تأثیر OPENJSON بر Performance در پردازش حجم بالا

از منظر موتور اجرایی، OPENJSON یک تابع Table-Valued است که در Execution Plan معمولاً به‌عنوان یک عملگر مجزا ظاهر می‌شود. برخلاف Queryهای معمولی روی جداول دائمی که می‌توانند از Index بهره ببرند، Parse کردن یک رشته JSON همیشه یک عملیات محاسباتی است و امکان Indexگذاری مستقیم روی محتوای JSON در حالت پیش‌فرض وجود ندارد.

در پردازش JSONهای کوچک تا متوسط، مانند پارامترهای ورودی یک Stored Procedure یا پیام‌های تکی از یک صف، هزینه Parse معمولاً ناچیز است. اما در سناریوهایی که حجم بسیار زیادی از داده JSON، مانند چندین هزار رکورد در یک آرایه واحد، باید یک‌جا پردازش شود، هزینه Parse می‌تواند قابل‌توجه شود.

نبود Statistics روی خروجی OPENJSON

نکته مهمی که در بسیاری از تحلیل‌های Performance نادیده گرفته می‌شود این است که خروجی OPENJSON، برخلاف یک جدول دائمی یا حتی یک متغیر جدولی از پیش پر شده، هیچ آماری یا Statistics در اختیار Query Optimizer قرار نمی‌دهد. موتور SQL Server از قبل نمی‌داند یک سند JSON مشخص، پس از Parse شدن، چند ردیف تولید خواهد کرد. به همین دلیل Cardinality Estimation برای عملگر OPENJSON در Execution Plan معمولاً بر اساس یک مقدار ثابت و از پیش تعیین‌شده تخمین زده می‌شود، نه بر اساس تحلیل واقعی داده.

در Queryهایی که خروجی OPENJSON بسیار بزرگ است، یکی از راهکارهای رایج این است که ابتدا نتیجه OPENJSON در یک جدول موقت (#Temp) ذخیره شود. از آنجا که SQL Server برای جدول موقت Statistics ایجاد می‌کند، Query Optimizer می‌تواند در ادامه برنامه اجرایی دقیق‌تری تولید کند.

توصیه برای بهبود Performance در حجم بالا

اگر یک ستون JSON به‌طور مکرر و در حجم بالا Query می‌شود، توصیه می‌شود مقادیر پرکاربرد آن، مانند فیلدهایی که در WHERE یا JOIN استفاده می‌شوند، با استفاده از ستون‌های محاسباتی مبتنی بر JSON_VALUE از JSON استخراج شوند. سپس می‌توان روی این ستون‌های محاسباتی Index تعریف کرد.

ALTER TABLE Orders
ADD CustomerNameFromJson AS JSON_VALUE(OrderDetails, '$.CustomerName');

CREATE INDEX IX_Orders_CustomerNameFromJson
ON Orders (CustomerNameFromJson);

Expressionهای مبتنی بر JSON_VALUE در بسیاری از سناریوها Deterministic محسوب می‌شوند و در صورت رعایت سایر شرایط SQL Server امکان ایجاد Index روی ستون محاسباتی وجود دارد.

تفاوت اصلی این دو رویکرد در جایی است که مقدار محاسبه‌شده ذخیره و نگهداری می‌شود.

در حالت PERSISTED، مقدار ستون محاسباتی به‌صورت فیزیکی در دیسک ذخیره می‌شود و در هر INSERT یا UPDATE مجدداً محاسبه و نوشته می‌شود، این روش هزینه نوشتن اندکی بیشتر دارد اما در عوض خواندن از طریق Index بسیار سریع است، زیرا مقدار از پیش آماده در دسترس است.

در حالت Non-Persisted، مقدار ستون به‌صورت فیزیکی ذخیره نمی‌شود، اما همچنان می‌توان روی آن Index ساخت، این هنگام SQL Server مقدار را در زمان ساخت و نگهداری Index محاسبه می‌کند، بدون آنکه فضای اضافی برای ذخیره خود ستون در سطح جدول اصلی مصرف شود.

در جداولی که فضای ذخیره‌سازی محدودیت مهمی محسوب می‌شود و عملیات نوشتن بسیار پرتکرار است، Non-Persisted Index می‌تواند گزینه سبک‌تری باشد. اما در سناریوهایی که این ستون محاسباتی به‌طور مستقیم و مکرر در خروجی Queryها نیز خوانده می‌شود، نه صرفاً برای فیلتر کردن، استفاده از PERSISTED معمولاً Performance بهتری در عملیات خواندن ارائه می‌دهد. انتخاب بین این دو باید بر اساس الگوی واقعی خواندن و نوشتن هر جدول انجام شود.

این تکنیک باعث می‌شود Queryهایی که بر اساس این فیلد فیلتر می‌کنند، بدون نیاز به Parse مکرر کل JSON در هر اجرا، از یک Index استاندارد بهره ببرند. این رویکرد به‌خصوص در جداولی که ستون JSON آن‌ها به‌طور مداوم در شرط‌های WHERE استفاده می‌شود، تفاوت محسوسی در Performance ایجاد می‌کند.

اشتباهات رایج در استفاده از OPENJSON

فراموش کردن AS JSON برای فیلدهای تودرتو

یکی از رایج‌ترین خطاها، عدم استفاده از عبارت AS JSON برای ستون‌هایی در بند WITH که خودشان یک Object یا Array تودرتو هستند. بدون این عبارت، مقدار بازگردانده‌شده به‌جای ساختار قابل پردازش، رشته‌ای ناقص یا Null خواهد بود.

عدم بررسی معتبر بودن JSON پیش از پردازش

اجرای مستقیم OPENJSON روی ورودی‌های دریافتی از منابع بیرونی، بدون بررسی اولیه با ISJSON، می‌تواند منجر به خطاهای اجرایی غیرمنتظره در محیط عملیاتی شود، به‌خصوص زمانی که ورودی از سیستم‌های خارج از کنترل مستقیم تیم توسعه دریافت می‌شود.

استفاده نادرست از CROSS APPLY در جایی که OUTER APPLY لازم است

همان‌طور که پیش‌تر بررسی شد، استفاده از CROSS APPLY در سناریویی که برخی رکوردها فاقد آرایه داخلی هستند، باعث حذف کامل آن رکوردها از نتیجه می‌شود. این خطا اغلب در فاز توسعه با داده تستی که همیشه کامل است دیده نمی‌شود و تنها در محیط عملیاتی، با داده واقعی و ناقص، خودش را نشان می‌دهد.

نادیده گرفتن Compatibility Level در محیط‌های چند دیتابیسی

در سازمان‌هایی که چندین دیتابیس با تاریخچه مهاجرت متفاوت دارند، ممکن است یک Stored Procedure حاوی OPENJSON به‌درستی روی یک دیتابیس اجرا شود اما روی دیتابیس دیگری با Compatibility Level پایین‌تر با خطا مواجه شود. بررسی این تنظیم باید بخشی از فرآیند استقرار Stored Procedureهای مبتنی بر JSON در محیط‌های چندگانه باشد.

استفاده از OPENJSON در حجم بسیار بالا بدون بررسی جایگزین‌های ساختاری

اگر یک سیستم به‌طور مداوم و در حجم بسیار بالا نیاز به پردازش JSON دارد، باید بررسی شود که آیا طراحی جدولی مستقیم به‌جای ذخیره JSON، از ابتدا گزینه مناسب‌تری برای آن سناریو نبوده است. OPENJSON ابزاری کارآمد برای پردازش نقطه‌ای و Integration است، نه لزوماً جایگزینی برای طراحی صحیح Schema رابطه‌ای در سناریوهای پرتکرار.

Best Practiceهای استفاده از OPENJSON در پروژه‌های سازمانی

پیش از استفاده از OPENJSON در هر دیتابیس، باید از تنظیم بودن Compatibility Level روی 130 یا بالاتر اطمینان حاصل شود، به‌خصوص در محیط‌های سازمانی با دیتابیس‌های مهاجرت‌یافته از نسخه‌های قدیمی.

پیش از پردازش هر JSON دریافتی از منبع بیرونی، اعتبارسنجی آن با ISJSON باید به‌عنوان یک لایه محافظتی استاندارد در Stored Procedureها لحاظ شود.

استفاده از بند WITH برای تعریف صریح ساختار خروجی، نسبت به حالت پیش‌فرض key-value، در اکثر سناریوهای Integration ترجیح داده می‌شود، زیرا نتیجه مستقیماً با ساختار جداول مقصد سازگار است. با این حال باید توجه داشت در صورت ناسازگاری نوع داده بین JSON و WITH، رفتار موتور بین بازگرداندن NULL و ایجاد خطا بسته به شرایط متفاوت است و باید از قبل بررسی شود.

برای ساختارهای تودرتو، ترکیب OPENJSON با APPLY و استفاده صحیح از AS JSON باید به‌دقت رعایت شود. انتخاب بین CROSS APPLY و OUTER APPLY باید بر اساس این تصمیم آگاهانه انجام شود که آیا رکوردهای فاقد آرایه داخلی باید از نتیجه حذف شوند یا با مقادیر NULL در نتیجه باقی بمانند.

در سناریوهایی که یک ستون JSON به‌طور مکرر در شرط‌های فیلتر یا Join استفاده می‌شود، باید امکان استخراج فیلدهای پرکاربرد در قالب ستون محاسباتی همراه با Index مناسب بررسی شود، با در نظر گرفتن اینکه Persisted بودن ستون همیشه الزامی نیست و انتخاب آن باید بر اساس الگوی خواندن و نوشتن جدول انجام شود.

انتخاب بین OPENJSON و سایر توابع خانواده JSON باید بر اساس نیاز واقعی Query انجام شود؛ برای استخراج یک مقدار تکی JSON_VALUE کافی است، برای استخراج یک بخش کامل JSON_QUERY مناسب است و تنها زمانی که نیاز به تبدیل کامل یک ساختار یا آرایه به نتیجه جدولی وجود دارد، OPENJSON باید انتخاب شود.

از ذخیره دائمی داده‌های کاملاً رابطه‌ای در قالب JSON خودداری شود. ستون‌های JSON برای داده‌های نیمه‌ساخت‌یافته، تنظیمات، پیام‌های Integration و ساختارهایی که شکل ثابتی ندارند مناسب هستند، اما جایگزین طراحی صحیح Schema رابطه‌ای نیستند. استفاده بی‌رویه از JSON برای داده‌های کاملاً ساخت‌یافته، امکان استفاده مؤثر از قیود، روابط، Indexها و بهینه‌سازی‌های موتور SQL Server را محدود می‌کند و در بلندمدت هزینه نگهداری و تحلیل داده را افزایش می‌دهد.

جمع‌بندی

OPENJSON یکی از ابزارهای کلیدی SQL Server برای پل زدن میان دنیای غیررابطه‌ای JSON و دنیای رابطه‌ای پایگاه داده است. این تابع امکان تبدیل مستقیم اسناد JSON، از ساده‌ترین اشیاء تک‌فیلدی تا ساختارهای تودرتو و آرایه‌ای پیچیده، به نتایج جدولی قابل Query را فراهم می‌کند و در بسیاری از سناریوهای Integration، درج گروهی رکوردها و پردازش پیام، جایگزین کارآمدتری نسبت به روش‌های سنتی مبتنی بر Parse دستی رشته یا CLR سفارشی است.

با این حال، استفاده صحیح از OPENJSON نیازمند توجه به چند نکته فنی مهم است: اطمینان از Compatibility Level مناسب دیتابیس، درک دقیق مفهوم JSON Path، آگاهی از هر دو حالت آرگومان OPENJSON، انتخاب آگاهانه بین CROSS APPLY و OUTER APPLY، و درک رفتار موتور در برابر ناسازگاری نوع داده. همچنین باید تفاوت این تابع با سایر اعضای خانواده JSON مانند JSON_VALUE، JSON_QUERY، JSON_MODIFY، FOR JSON و ISJSON به‌روشنی شناخته شود تا در هر سناریو ابزار مناسب انتخاب گردد. در پردازش حجم بالا نیز، توجه به نبود Statistics روی خروجی OPENJSON و بررسی امکان استفاده از ستون محاسباتی برای Index، نقش مهمی در حفظ Performance سیستم‌های سازمانی دارد.

سوالات متداول FAQ

آیا نصب SQL Server 2016 یا بالاتر برای استفاده از OPENJSON کافی است؟
خیر. علاوه بر نسخه مناسب موتور، دیتابیس باید روی Compatibility Level 130 یا بالاتر تنظیم شده باشد. در دیتابیس‌های مهاجرت‌یافته از نسخه‌های قدیمی‌تر، این تنظیم ممکن است هنوز روی سطح پایین‌تر باقی مانده باشد.

تفاوت آرگومان دوم OPENJSON با ترکیب OPENJSON و CROSS APPLY چیست؟
اگر فقط بخش داخلی JSON، مانند یک آرایه مشخص، موردنیاز باشد و فیلدهای سطح بیرونی سند اهمیتی نداشته باشند، می‌توان مستقیماً با آرگومان دوم OPENJSON به آن مسیر اشاره کرد. اما اگر همزمان به فیلدهای سطح بیرونی و آرایه داخلی نیاز باشد، باید از ترکیب دو OPENJSON با APPLY استفاده کرد.

چه زمانی باید از OUTER APPLY به‌جای CROSS APPLY با OPENJSON استفاده کرد؟
اگر ممکن است برخی رکوردهای سطح بیرونی فاقد آرایه داخلی باشند و بخواهیم آن رکوردها همچنان در نتیجه نهایی، با مقادیر NULL برای فیلدهای داخلی، حفظ شوند، باید از OUTER APPLY استفاده کرد. CROSS APPLY رکوردهای فاقد آرایه داخلی را به‌طور کامل حذف می‌کند.

چرا Cardinality Estimation برای OPENJSON همیشه دقیق نیست؟
خروجی OPENJSON مانند جداول دائمی Statistics مستقل و پایداری در اختیار Query Optimizer قرار نمی‌دهد.

آیا برای Index کردن یک فیلد JSON حتماً باید ستون محاسباتی Persisted تعریف کرد؟
خیر. اگر Expression مورد استفاده، مانند JSON_VALUE، ویژگی Deterministic داشته باشد، می‌توان روی یک ستون محاسباتی Non-Persisted نیز Index تعریف کرد. انتخاب بین این دو باید بر اساس الگوی واقعی خواندن و نوشتن جدول و محدودیت فضای ذخیره‌سازی انجام شود.

آیا OPENJSON ترتیب عناصر آرایه را حفظ می‌کند؟
بله. هنگام پردازش یک آرایه JSON، ردیف‌ها مطابق ترتیب عناصر موجود در آرایه تولید می‌شوند. در صورت نیاز می‌توان از ستون key که اندیس هر عنصر را نگه می‌دارد، برای مرتب‌سازی یا بازسازی ترتیب اولیه استفاده کرد.

پردازش حرفه‌ای JSON در SQL Server با لاندا

کار با JSON در SQL Server تنها به استفاده از چند تابع محدود نمی‌شود. انتخاب صحیح بین OPENJSON، JSON_VALUE، JSON_QUERY و JSON_MODIFY، طراحی مناسب ساختار داده، بهینه‌سازی Queryها و رعایت نکات Performance، نقش مهمی در سرعت، مقیاس‌پذیری و نگهداری سیستم‌های سازمانی دارد.

اگر سازمان شما در زمینه طراحی پایگاه داده، بهینه‌سازی SQL Server، پیاده‌سازی راهکارهای Integration، توسعه Stored Procedureها یا ارتقای Performance سامانه‌های داده‌محور نیاز به مشاوره و اجرای تخصصی دارد، توسعه فناوری اطلاعات لاندا آماده است با تکیه بر تجربه عملی در پروژه‌های سازمانی، راهکارهایی استاندارد، پایدار و مقیاس‌پذیر ارائه دهد.

برای دریافت مشاوره تخصصی، ارزیابی Performance یا برگزاری دوره‌های آموزشی عملی SQL Server، با کارشناسان لاندا تماس ✆ بگیرید.

روزبه امیرعصامی

1405/05/16