آموزش کامل OPENJSON در SQL Server

مقدمه

OPENJSON یک تابع جدولی است که شیء یا آرایه JSON را به Rowset تبدیل می‌کند و امکان تعریف Schema و نوع داده را می‌دهد. در سامانه‌های امروزی، JSON معمولاً از API، صف پیام یا تنظیمات انعطاف‌پذیر وارد دیتابیس می‌شود. اجرای درست OPENJSON نیازمند شناخت تفاوت متن JSON با داده رابطه‌ای، مسیرهای SQL/JSON و نوع خروجی است.

هدف این مقاله ارائه یک مرجع اجرایی است: از Syntax و رفتار NULL تا خطاهای واقعی، ملاحظات کارایی و طراحی قابل نگهداری. همه مثال‌ها مستقل‌اند و می‌توان آن‌ها را در محیط آزمایشی SQL Server اجرا کرد؛ پیش از اجرای DDL در سامانه واقعی، نام‌ها و سیاست پاک‌سازی را با استاندارد پروژه هماهنگ کنید.

بازگشت به راهنمای جامع توابع JSON در SQL Server

تعریف تابع OPENJSON

OPENJSON یک Table-Valued Function است و متن JSON را به ردیف‌ها و ستون‌ها نگاشت می‌کند. حالت پیش‌فرض ستون‌های key، value و type می‌دهد؛ WITH Schema صریح، نام، نوع و Path هر ستون را تعیین می‌کند. این قابلیت برای ورود دسته‌ای داده API و بازکردن آرایه‌ها بسیار مهم است.

JSON در نسخه‌های رایج SQL Server عمدتاً در ستون‌های nvarchar ذخیره می‌شود. بنابراین معتبر بودن Syntax به معنی صحیح بودن قواعد تجاری نیست؛ شناسه، تاریخ، طول متن و مجوز دسترسی همچنان باید با قید، تبدیل امن یا منطق برنامه کنترل شوند.

Syntax

SELECT *
FROM OPENJSON(jsonExpression [, path])
WITH (columnName dataType [column_path] [AS JSON]);

پارامترها

• jsonExpression: عبارت Unicode شامل JSON.
• path: بخش مورد نظر برای Parse، مانند $.items.
• WITH: قرارداد ستون‌ها، انواع SQL، مسیرها و AS JSON برای Fragmentهای تو‌در‌تو.

نوع خروجی و رفتار مسیر

بدون WITH سه ستون key از نوع nvarchar(4000)، value از نوع nvarchar(max) و type از نوع int برمی‌گردد. با WITH، خروجی دقیقاً Schema تعریف‌شده را دارد و تبدیل نوع نیز انجام می‌شود. تطبیق نام کلید در Schema به بزرگی و کوچکی حروف حساس است.

مثال‌های عملی

مثال 1: Schema پیش‌فرض شیء

کلید، مقدار و کد نوع Propertyهای یک شیء را مشاهده می‌کنیم.

SELECT [key],[value],[type]
FROM OPENJSON(N'{"name":"Ali","age":30}');

کد نوع یک برای رشته و دو برای عدد است و برای کشف ساختار اولیه مفید است.

مثال 2: بازکردن آرایه

هر عضو آرایه را به یک ردیف مستقل تبدیل می‌کنیم.

SELECT CONVERT(int,[key]) AS ItemIndex,[value]
FROM OPENJSON(N'["SQL","C#","Azure"]');

key در آرایه همان Index متنی است و در صورت نیاز باید تبدیل شود.

مثال 3: Schema صریح

فیلدهای سفارش را با نوع دقیق SQL استخراج می‌کنیم.

DECLARE @j nvarchar(max)=N'{"id":15,"total":120.50,"paid":true}';
SELECT Id,Total,Paid
FROM OPENJSON(@j) WITH
(Id int '$.id',Total decimal(10,2) '$.total',Paid bit '$.paid');

تبدیل نوع در مرز Parse، قرارداد خروجی را روشن می‌کند.

مثال 4: Parse زیرآرایه با Path

فقط items را از سند سفارش به ردیف تبدیل می‌کنیم.

DECLARE @j nvarchar(max)=N'{"orderId":5,"items":[{"sku":"A1"},{"sku":"B2"}]}';
SELECT Sku
FROM OPENJSON(@j,'$.items') WITH(Sku varchar(10) '$.sku');

پارامتر Path مانع پردازش بخش‌های نامرتبط در خروجی منطقی می‌شود.

مثال 5: CROSS APPLY روی جدول

آرایه تگ هر محصول را به ردیف‌های قابل Join تبدیل می‌کنیم.

DECLARE @Products TABLE(Id int,Data nvarchar(max));
INSERT INTO @Products VALUES(1,N'{"tags":["new","sale"]}');
SELECT p.Id,j.[value] AS Tag
FROM @Products AS p
CROSS APPLY OPENJSON(p.Data,'$.tags') AS j;

CROSS APPLY برای هر ردیف والد، مجموعه فرزند متناظر را تولید می‌کند.

مثال 6: نگهداری Fragment با AS JSON

شیء customer و آرایه items را بدون Escape استخراج می‌کنیم.

DECLARE @j nvarchar(max)=N'{"customer":{"id":2},"items":[1,2]}';
SELECT Customer,Items
FROM OPENJSON(@j) WITH
(Customer nvarchar(max) '$.customer' AS JSON,Items nvarchar(max) '$.items' AS JSON);

AS JSON برای ستون‌های ساخت‌یافته ضروری است؛ در غیر این صورت مقدار مناسب دریافت نمی‌شود.

مثال 7: تبدیل تاریخ و عدد

داده متنی سرویس را مستقیماً به انواع SQL تبدیل می‌کنیم.

DECLARE @j nvarchar(max)=N'{"created":"2026-07-20T10:30:00","amount":99.95}';
SELECT CreatedAt,Amount
FROM OPENJSON(@j) WITH
(CreatedAt datetime2 '$.created',Amount decimal(10,2) '$.amount');

نوع نامتناسب ممکن است خطا یا NULL ایجاد کند؛ قرارداد ورودی را اعتبارسنجی کنید.

مثال 8: Path متغیر

از SQL Server 2017 بخش انتخابی سند را با متغیر تعیین می‌کنیم.

DECLARE @j nvarchar(max)=N'{"active":[1,2],"archived":[9]}';
DECLARE @path nvarchar(50)=N'$.active';
SELECT [value] FROM OPENJSON(@j,@path);

Path متغیر انعطاف می‌دهد، اما فقط مقادیر از پیش مجاز را بپذیرید تا رفتار Query کنترل شود.

مثال 9: ورود دسته‌ای به جدول

آرایه سفارش‌ها را با یک INSERT مبتنی بر مجموعه وارد می‌کنیم.

CREATE TABLE #Orders(Id int,Customer nvarchar(50));
DECLARE @j nvarchar(max)=N'[{"id":1,"customer":"مریم"},{"id":2,"customer":"سارا"}]';
INSERT INTO #Orders(Id,Customer)
SELECT Id,Customer FROM OPENJSON(@j)
WITH(Id int '$.id',Customer nvarchar(50) '$.customer');
SELECT * FROM #Orders;

عملیات Set-Based از حلقه‌زدن روی اعضای JSON ساده‌تر و معمولاً کارآمدتر است.

مثال 10: استخراج یک‌مرحله‌ای ستون‌های لازم

برای گزارش، فقط فیلدهای مصرفی را در WITH تعریف می‌کنیم.

DECLARE @j nvarchar(max)=N'[{"id":1,"status":"Paid","largeText":"ignored"},{"id":2,"status":"New"}]';
SELECT Id,Status
FROM OPENJSON(@j) WITH(Id int '$.id',Status varchar(20) '$.status')
WHERE Status='Paid';

Schema محدود، Query را خوانا می‌کند؛ برای تصمیم کارایی آمار IO، CPU و Plan را مقایسه کنید.

خطاهای رایج

خطاهای OPENJSON معمولاً از فرض نادرست درباره ساختار ورودی یا نسخه موتور ناشی می‌شوند. فهرست زیر را در Code Review و تست خودکار کنترل کنید.

• Compatibility Level کمتر از 130 و ناشناخته‌بودن تابع.
• اتکا به تطبیق نام در WITH با اختلاف حروف بزرگ و کوچک.
• فراموش‌کردن AS JSON برای شیء یا آرایه فرزند.
• تعریف نوع کوچک و قطع‌شدن یا شکست تبدیل داده ورودی.

نکات کارایی و بهینه‌سازی

Schema صریح فقط ستون‌های لازم را می‌سازد و معمولاً از Parseهای پراکنده خواناتر است. Payload را یک‌بار باز کنید، تبدیل نوع را در WITH انجام دهید و از CROSS APPLY فقط برای ردیف‌های لازم استفاده کنید. OPENJSON در حالت معمول به Compatibility Level 130 یا بالاتر نیاز دارد.

• قبل و بعد از تغییر، SET STATISTICS IO, TIME و Actual Execution Plan را ثبت کنید.
• روی داده نزدیک به حجم و توزیع محیط تولید آزمایش کنید؛ نتیجه جدول کوچک معیار کافی نیست.
• از پردازش چندباره همان Path در SELECT، WHERE و ORDER BY بدون ارزیابی جلوگیری کنید.
• اندازه Payload، طول ستون و هزینه شبکه را در کنار زمان Query گزارش کنید.

بهترین روش‌ها

• ورودی خارجی را با ISJSON و قواعد تجاری معتبر کنید.
• مسیرها را ثابت یا از فهرست مجاز انتخاب کنید و نوع مقصد را صریح بنویسید.
• برای فیلد پرتکرار و قابل جست‌وجو، ستون رابطه‌ای یا محاسباتی ایندکس‌پذیر را ارزیابی کنید.
• رفتار lax، strict، NULL و مسیر گمشده را در قرارداد API مستند کنید.
• از Concatenate دستی JSON خودداری و توابع داخلی Serializer را استفاده کنید.

کاربرد واقعی در پروژه

در یک معماری سازمانی، OPENJSON می‌تواند بخشی از مرحله ورود داده، گزارش‌گیری یا تولید پاسخ باشد؛ اما مرز مسئولیت باید روشن بماند. داده تراکنشی پرتکرار معمولاً از ستون‌های نوع‌دار و قیدهای رابطه‌ای سود می‌برد، در حالی که بخش اختیاری و کم‌جست‌وجوی Payload می‌تواند JSON باقی بماند. تصمیم نهایی را با شاخص‌های قابل اندازه‌گیری بگیرید.

سازگاری نسخه و استقرار

پیش از استقرار OPENJSON نسخه SQL Server، Compatibility Level، رفتار Collation و اندازه واقعی داده را کنترل کنید. اسکریپت Deployment باید روی نسخه‌ای همسان با تولید اجرا شود و Rollback، تست Payload نامعتبر و مانیتورینگ Query Store را دربر بگیرد.

سؤالات متداول

۱. OPENJSON دقیقاً چه مسئله‌ای را حل می‌کند؟

این قابلیت برای تبدیل JSON به ردیف و ستون درون موتور SQL Server طراحی شده است و نیاز به دستکاری شکننده رشته‌ها را کم می‌کند. استفاده درست زمانی ارزشمند است که قرارداد JSON، نوع داده و مسیرها روشن باشند.

۲. برای شروع یادگیری OPENJSON چه پیش‌نیازی لازم است؟

آشنایی با SELECT، نوع nvarchar(max)، تفاوت SQL NULL و JSON null و مفهوم SQL/JSON Path کافی است. سپس مثال‌ها را در دیتابیس آزمایشی اجرا و خروجی و خطاها را مقایسه کنید.

۳. آیا OPENJSON برای پروژه سازمانی و API مناسب است؟

بله، اگر Schema ورودی، محدودیت اندازه، اعتبارسنجی و پایش کارایی تعریف شود. در پروژه حساس بهتر است نمونه بار واقعی، برنامه اجرا و هزینه شبکه نیز پیش از استقرار ارزیابی شود.

۴. هزینه پیاده‌سازی حرفه‌ای OPENJSON به چه عواملی بستگی دارد؟

حجم و تنوع Payload، تعداد مسیرها، نرخ درخواست، نیاز به Migration و ایندکس‌گذاری تعیین‌کننده‌اند. مشاوره SQL Server می‌تواند طراحی رابطه‌ای، JSON یا مدل ترکیبی را بر پایه اندازه‌گیری انتخاب کند.

۵. تفاوت OPENJSON با روش‌های دیگر پردازش JSON چیست؟

این قابلیت داخل T-SQL اجرا می‌شود و جابه‌جایی داده را کاهش می‌دهد، اما همه منطق دامنه نباید الزاماً وارد دیتابیس شود. مقایسه صحیح به محل مصرف، حجم داده و نیاز تراکنشی وابسته است.

۶. آیا می‌توان برای طراحی Queryهای OPENJSON خدمات تخصصی گرفت؟

برای Queryهای پیچیده، بازبینی مسیرها، طراحی Index و تحلیل Execution Plan می‌توان از آموزش یا مشاوره تخصصی استفاده کرد. خروجی مطلوب باید همراه تست تکرارپذیر و معیار قبل و بعد تحویل شود.

۷. رایج‌ترین خطا هنگام استفاده از OPENJSON چیست؟

فرض‌کردن شکل ثابت JSON بدون اعتبارسنجی، تبدیل نوع ضمنی و بی‌توجهی به مسیر گمشده رایج است. قرارداد داده و تست حالت‌های NULL، نامعتبر و مرزی جلوی بیشتر خطاهای عملی را می‌گیرد.

۸. چگونه کارایی OPENJSON را اندازه‌گیری کنیم؟

از Actual Execution Plan، SET STATISTICS IO, TIME و نمونه داده نزدیک به تولید استفاده کنید. CPU، Logical Read، Memory Grant، اندازه خروجی و زمان انتقال را جدا ثبت و نسخه بهینه را با خط پایه مقایسه کنید.

۹. بهترین روش استفاده از OPENJSON چیست؟

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

۱۰. OPENJSON در کدام نسخه‌های SQL Server قابل استفاده است؟

سازگاری دقیق به قابلیت وابسته است؛ برای OPENJSON باید نسخه موتور و Database Compatibility Level بررسی شود. پیش از Deploy مستندات نسخه هدف و اجرای آزمایشی روی همان محیط ملاک نهایی است.

سؤالات مصاحبه تخصصی

1. تفاوت SQL NULL، JSON null و مسیر گمشده هنگام کار با OPENJSON چیست؟
2. چگونه Query مبتنی بر OPENJSON را برای یک میلیون ردیف ارزیابی می‌کنید؟
3. چه زمانی مدل رابطه‌ای را به نگهداری JSON برای سناریوی OPENJSON ترجیح می‌دهید؟
4. برای جلوگیری از تبدیل نوع ضمنی در خروجی OPENJSON چه می‌کنید؟
5. چه تست‌هایی برای مسیرهای نامعتبر و Payload ناقص OPENJSON می‌نویسید؟

پاسخ حرفه‌ای باید فقط Syntax را تکرار نکند؛ انتظار می‌رود نامزد درباره قرارداد داده، نسخه SQL Server، مسیر خطا، قابلیت ایندکس‌گذاری و روش اندازه‌گیری با Plan و آمار IO توضیح دهد.

چک‌لیست نهایی

• Syntax روی نسخه هدف اجرا شده است.
• حالت NULL، مسیر گمشده و JSON نامعتبر تست شده است.
• نوع خروجی و تبدیل عدد، تاریخ یا Boolean صریح است.
• تعداد Logical Read و زمان CPU ثبت شده است.
• مسیرها و قرارداد خروجی در مستندات پروژه درج شده‌اند.
• مجوزها و داده حساس در Payload بازبینی شده‌اند.

جمع‌بندی

تابع OPENJSON وقتی ارزشمند است که همراه قرارداد داده، تست مرزی و سنجش کارایی استفاده شود. مثال‌های این مقاله از حالت پایه تا سناریوی جدول، NULL، خطا و بهینه‌سازی را پوشش دادند. برای انتخاب تابع مکمل و دیدن نقشه کامل پردازش JSON، بازگشت به راهنمای جامع توابع JSON در SQL Server را مطالعه کنید.