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

مقدمه

عبارت FOR JSON خروجی رابطه‌ای SELECT را در حالت PATH یا AUTO به JSON مناسب API، سرویس و تبادل داده تبدیل می‌کند. در سامانه‌های امروزی، JSON معمولاً از API، صف پیام یا تنظیمات انعطاف‌پذیر وارد دیتابیس می‌شود. اجرای درست FOR JSON نیازمند شناخت تفاوت متن JSON با داده رابطه‌ای، مسیرهای SQL/JSON و نوع خروجی است.

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

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

تعریف تابع FOR JSON

FOR JSON در انتهای SELECT قرار می‌گیرد و Result Set را به متن JSON تبدیل می‌کند. PATH کنترل دقیق نام و ساختار تو‌در‌تو را می‌دهد و AUTO ساختار را بیشتر از شکل Query استنتاج می‌کند. گزینه‌هایی مانند ROOT، INCLUDE_NULL_VALUES و WITHOUT_ARRAY_WRAPPER قرارداد خروجی را تنظیم می‌کنند.

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

Syntax

SELECT column_list
FROM source
FOR JSON { PATH | AUTO }
[ , ROOT('rootName') ]
[ , INCLUDE_NULL_VALUES ]
[ , WITHOUT_ARRAY_WRAPPER ];

پارامترها

• PATH: نام Aliasها را برای ساخت Property و آبجکت تو‌در‌تو تفسیر می‌کند.
• AUTO: ساختار JSON را از ترتیب جدول‌ها و ستون‌ها استنتاج می‌کند.
• ROOT، INCLUDE_NULL_VALUES و WITHOUT_ARRAY_WRAPPER شکل Envelope و NULLها را کنترل می‌کنند.

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

خروجی nvarchar(max) شامل JSON معتبر است. حالت عادی یک آرایه می‌سازد؛ WITHOUT_ARRAY_WRAPPER براکت خارجی را برای نتیجه تک‌ردیفی حذف می‌کند. به‌طور پیش‌فرض ستون‌های SQL NULL از خروجی حذف می‌شوند.

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

مثال 1: خروجی ساده با PATH

دو ستون ثابت را به آرایه‌ای شامل یک شیء تبدیل می‌کنیم.

SELECT 1 AS Id,N'علی' AS Name
FOR JSON PATH;

PATH برای کنترل نام Propertyها انتخاب پیش‌فرض مناسبی است.

مثال 2: حالت AUTO روی جدول

ساختار ساده را از نام و جدول منبع استنتاج می‌کنیم.

DECLARE @People TABLE(Id int,Name nvarchar(20));
INSERT INTO @People VALUES(1,N'مینا');
SELECT Id,Name FROM @People FOR JSON AUTO;

در Joinهای پیچیده، شکل AUTO به ساختار Query وابسته است و PATH کنترل بیشتری دارد.

مثال 3: افزودن ROOT

برای قرارداد API یک Envelope با نام data ایجاد می‌کنیم.

SELECT 1 AS Id,N'Active' AS Status
FOR JSON PATH,ROOT('data');

ROOT توسعه قرارداد و افزودن Metadata کنار داده را آسان‌تر می‌کند.

مثال 4: حفظ NULL

ستون NULL را صریحاً در Payload نگه می‌داریم.

SELECT 1 AS Id,CAST(NULL AS nvarchar(20)) AS Note
FOR JSON PATH,INCLUDE_NULL_VALUES;

بین حذف Property و JSON null تفاوت معنایی وجود دارد و باید با مصرف‌کننده هماهنگ شود.

مثال 5: حذف Array Wrapper

برای نتیجه تضمین‌شده تک‌ردیفی یک شیء مستقل می‌سازیم.

SELECT 10 AS Id,N'Paid' AS Status
FOR JSON PATH,WITHOUT_ARRAY_WRAPPER;

این گزینه را فقط وقتی Cardinality یک ردیف تضمین شده است استفاده کنید.

مثال 6: ساخت شیء تو‌در‌تو با Alias

با Dot در Alias ساختار address را ایجاد می‌کنیم.

SELECT 7 AS Id,N'رشت' AS [address.city],N'ایران' AS [address.country]
FOR JSON PATH,WITHOUT_ARRAY_WRAPPER;

Aliasهای PATH ساخت مدل چندسطحی را بدون دستکاری رشته ممکن می‌کنند.

مثال 7: آرایه فرزند تو‌در‌تو

سفارش‌های مشتری را با زیرQuery و JSON_QUERY به‌عنوان آرایه واقعی جاسازی می‌کنیم.

DECLARE @Orders TABLE(CustomerId int,OrderId int);
INSERT INTO @Orders VALUES(1,101),(1,102);
SELECT 1 AS CustomerId,
       JSON_QUERY((SELECT OrderId FROM @Orders WHERE CustomerId=1 FOR JSON PATH)) AS Orders
FOR JSON PATH,WITHOUT_ARRAY_WRAPPER;

JSON_QUERY از تبدیل آرایه فرزند به رشته Escape‌شده جلوگیری می‌کند.

مثال 8: ترتیب قطعی آرایه

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

DECLARE @Scores TABLE(Name nvarchar(20),Score int);
INSERT INTO @Scores VALUES(N'ب',70),(N'الف',90);
SELECT Name,Score FROM @Scores ORDER BY Score DESC FOR JSON PATH;

مصرف‌کننده نباید به ترتیب تصادفی Plan وابسته باشد؛ ORDER BY قرارداد را پایدار می‌کند.

مثال 9: Escape خودکار نویسه‌ها

متنی شامل Double Quote را با قواعد JSON امن Serialize می‌کنیم.

SELECT N'او گفت "سلام"' AS Message
FOR JSON PATH,WITHOUT_ARRAY_WRAPPER;

رشته JSON را دستی Concatenate نکنید؛ Serializer نویسه‌های ویژه را درست Escape می‌کند.

مثال 10: Payload صفحه‌بندی‌شده API

تعداد محدود ردیف را با Envelope استاندارد برمی‌گردانیم.

DECLARE @Products TABLE(Id int,Name nvarchar(20));
INSERT INTO @Products VALUES(1,N'کالا یک'),(2,N'کالا دو'),(3,N'کالا سه');
SELECT Id,Name FROM @Products
ORDER BY Id OFFSET 0 ROWS FETCH NEXT 2 ROWS ONLY
FOR JSON PATH,ROOT('data');

صفحه‌بندی اندازه Payload، مصرف حافظه و زمان انتقال شبکه را قابل کنترل می‌کند.

خطاهای رایج

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

• استفاده از SELECT ستاره و افزایش ناخواسته Payload.
• WITHOUT_ARRAY_WRAPPER برای چند ردیف و تولید قطعات نامناسب برای مصرف‌کننده.
• فراموش‌کردن JSON_QUERY در زیرQuery و Double Escaping.
• اتکا به ترتیب خروجی بدون ORDER BY صریح.

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

ساخت JSON نیازمند Serialize کردن Result Set و انتقال متن است. ستون‌ها و ردیف‌های لازم را انتخاب کنید، Paging و ORDER BY قطعی داشته باشید و تولید Payload بسیار بزرگ را یک‌جا انجام ندهید. شبکه، Memory Grant و زمان CPU را جداگانه اندازه بگیرید.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

جمع‌بندی

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