راهنمای جامع توابع JSON در SQL Server
مقدمه
SQL Server از نسخه 2016 مجموعهای از قابلیتهای داخلی برای کار با JSON ارائه میکند. این قابلیتها متن JSON را اعتبارسنجی میکنند، مقدار اسکالر یا Fragment ساختیافته را بیرون میآورند، سند را تغییر میدهند، آرایه را به ردیف تبدیل میکنند و نتیجه SELECT را به JSON برمیگردانند. SQL Server 2022 نیز بررسی مستقیم وجود مسیر را افزوده است.
JSON جایگزین کامل مدل رابطهای نیست. ستونهای نوعدار، کلید خارجی، قید و ایندکس برای هسته تراکنشی همچنان مزیت دارند؛ JSON زمانی مفید است که بخشی از داده انعطافپذیر، اختیاری یا متعلق به قرارداد تبادل باشد. معماری حرفهای اغلب مدل ترکیبی را انتخاب میکند: هویت و فیلدهای جستوجوشونده رابطهای و Metadata کممصرف در Payload.
در این راهنما هر قابلیت با هدف، نوع خروجی، محدودیت نسخه و لینک مقاله مستقل معرفی میشود. مثالها زنجیره کامل دریافت، کنترل، Parse، تغییر و تولید پاسخ را نشان میدهند. معیار انتخاب تابع باید شکل خروجی موردنیاز باشد: اسکالر، شیء، آرایه، Rowset یا سند نهایی.
دسترسی سریع به مقالهها
نقشه انتخاب تابع
اگر فقط معتبر بودن Syntax مهم است از ISJSON شروع کنید. برای یک مقدار ساده JSON_VALUE، برای شیء یا آرایه JSON_QUERY، برای تغییر JSON_MODIFY، برای تبدیل سند به ردیف OPENJSON و برای تبدیل ردیف به سند FOR JSON مناسب است. در SQL Server 2022، JSON_PATH_EXISTS وجود Path را بدون استخراج مقدار میسنجد.
| تابع | کاربرد اصلی | نوع خروجی یا نکته مهم | لینک آموزش کامل |
|---|
| ISJSON | اعتبارسنجی JSON | تابع ISJSON معتبر بودن متن JSON را بدون ایجاد خطا بررسی میکند و برای کنترل ورودی API، قیدهای کیفیت داده و پاکسازی اطلاعات مناسب است. | مقاله ISJSON |
| JSON_VALUE | استخراج مقدار اسکالر از JSON | JSON_VALUE مقدارهای اسکالر مانند نام، شناسه، تاریخ یا وضعیت را از مسیر مشخص JSON استخراج میکند و در فیلتر و گزارشگیری کاربرد فراوان دارد. | مقاله JSON_VALUE |
| JSON_QUERY | استخراج شیء و آرایه JSON | JSON_QUERY بخش ساختیافته یک سند، یعنی شیء یا آرایه، را بدون تبدیل آن به متن Escaped استخراج میکند. | مقاله JSON_QUERY |
| JSON_MODIFY | ویرایش امن سند JSON | JSON_MODIFY مقدار یک Property را بهروزرسانی، اضافه یا حذف میکند و متن JSON جدید را برمیگرداند. | مقاله JSON_MODIFY |
| OPENJSON | تبدیل JSON به ردیف و ستون | OPENJSON یک تابع جدولی است که شیء یا آرایه JSON را به Rowset تبدیل میکند و امکان تعریف Schema و نوع داده را میدهد. | مقاله OPENJSON |
| FOR JSON | تبدیل نتیجه Query به JSON | عبارت FOR JSON خروجی رابطهای SELECT را در حالت PATH یا AUTO به JSON مناسب API، سرویس و تبادل داده تبدیل میکند. | مقاله FOR JSON |
| JSON_PATH_EXISTS | بررسی وجود مسیر JSON در SQL Server 2022 | JSON_PATH_EXISTS در SQL Server 2022 وجود یک مسیر یا دنباله غیرخالی را در متن JSON با خروجی 1، 0 یا NULL بررسی میکند. | مقاله JSON_PATH_EXISTS |
مدل داده، نوع ذخیرهسازی و قرارداد JSON
در SQL Server 2022 و نسخههای پیش از نوع native جدید، JSON معمولاً در nvarchar(max) ذخیره میشود. موتور توابع JSON را روی این متن اجرا میکند. انتخاب nvarchar از نویسههای فارسی محافظت میکند، اما اجازه نمیدهد هر Property بهطور خودکار قید نوع یا Foreign Key داشته باشد. برای همین ورودی باید هم از نظر Syntax و هم از نظر قواعد دامنه کنترل شود.
مسیر SQL/JSON با علامت دلار از ریشه شروع میشود. نقطه Property و براکت عضو آرایه را مشخص میکند. حالت lax برای مسیر گمشده معمولاً NULL میدهد و برای ورودی متغیر مقاومتر است؛ strict خطا را آشکار میکند و برای قرارداد قطعی مفید است. انتخاب این حالت باید در API و تستها مستند باشد.
SQL NULL، مقدار JSON null و نبود Property سه وضعیت متفاوتاند. تابع وجود مسیر ممکن است برای Property با مقدار null یک بدهد، در حالی که استخراج اسکالر NULL برگرداند. اگر این تفاوت روی قیمت، مجوز یا وضعیت کسبوکار اثر دارد، آن را به یک ستون یا Flag صریح تبدیل کنید.
معرفی توابع و مسیر مطالعه
تابع ISJSON در SQL Server
تابع ISJSON معتبر بودن متن JSON را بدون ایجاد خطا بررسی میکند و برای کنترل ورودی API، قیدهای کیفیت داده و پاکسازی اطلاعات مناسب است. ISJSON یک تابع اسکالر است که متن ورودی را از نظر قواعد نحوی JSON بررسی میکند. خروجی یک برای متن معتبر، صفر برای متن نامعتبر و در برابر NULL مقدار NULL است. در SQL Server 2022 میتوان با محدودیت نوع، OBJECT، ARRAY، VALUE یا SCALAR را نیز جداگانه آزمود.
آموزش کامل ISJSON با مثالهای عملی
تابع JSON_VALUE در SQL Server
JSON_VALUE مقدارهای اسکالر مانند نام، شناسه، تاریخ یا وضعیت را از مسیر مشخص JSON استخراج میکند و در فیلتر و گزارشگیری کاربرد فراوان دارد. JSON_VALUE یک مقدار اسکالر را از متن JSON و مسیر SQL/JSON میخواند. در SQL Server 2022 و نسخههای قدیمیتر خروجی nvarchar(4000) است و برای اشیاء یا آرایهها باید JSON_QUERY به کار رود. حالت lax برای مسیر گمشده NULL میدهد، در حالی که strict خطای قابل مشاهده ایجاد میکند.
آموزش کامل JSON_VALUE با مثالهای عملی
تابع JSON_QUERY در SQL Server
JSON_QUERY بخش ساختیافته یک سند، یعنی شیء یا آرایه، را بدون تبدیل آن به متن Escaped استخراج میکند. JSON_QUERY برای بازگرداندن شیء یا آرایه از مسیر مشخص استفاده میشود. برخلاف JSON_VALUE که اسکالر میخواند، خروجی JSON_QUERY یک Fragment معتبر JSON از نوع nvarchar(max) است. این تابع همچنین هنگام ساخت خروجی FOR JSON علامت میدهد که Fragment نباید دوباره Escape شود.
آموزش کامل JSON_QUERY با مثالهای عملی
تابع JSON_MODIFY در SQL Server
JSON_MODIFY مقدار یک Property را بهروزرسانی، اضافه یا حذف میکند و متن JSON جدید را برمیگرداند. JSON_MODIFY یک سند JSON را در محل تغییر نمیدهد، بلکه متن جدیدی با تغییر خواستهشده بازمیگرداند. با آن میتوان مقدار موجود را عوض کرد، Property تازه افزود، کلید را در lax حذف کرد یا عضو آرایه را Append نمود. تفاوت lax و strict در رفتار مسیر گمشده و NULL اهمیت زیادی دارد.
آموزش کامل JSON_MODIFY با مثالهای عملی
تابع OPENJSON در SQL Server
OPENJSON یک تابع جدولی است که شیء یا آرایه JSON را به Rowset تبدیل میکند و امکان تعریف Schema و نوع داده را میدهد. OPENJSON یک Table-Valued Function است و متن JSON را به ردیفها و ستونها نگاشت میکند. حالت پیشفرض ستونهای key، value و type میدهد؛ WITH Schema صریح، نام، نوع و Path هر ستون را تعیین میکند. این قابلیت برای ورود دستهای داده API و بازکردن آرایهها بسیار مهم است.
آموزش کامل OPENJSON با مثالهای عملی
تابع FOR JSON در SQL Server
عبارت FOR JSON خروجی رابطهای SELECT را در حالت PATH یا AUTO به JSON مناسب API، سرویس و تبادل داده تبدیل میکند. FOR JSON در انتهای SELECT قرار میگیرد و Result Set را به متن JSON تبدیل میکند. PATH کنترل دقیق نام و ساختار تودرتو را میدهد و AUTO ساختار را بیشتر از شکل Query استنتاج میکند. گزینههایی مانند ROOT، INCLUDE_NULL_VALUES و WITHOUT_ARRAY_WRAPPER قرارداد خروجی را تنظیم میکنند.
آموزش کامل FOR JSON با مثالهای عملی
تابع JSON_PATH_EXISTS در SQL Server
JSON_PATH_EXISTS در SQL Server 2022 وجود یک مسیر یا دنباله غیرخالی را در متن JSON با خروجی 1، 0 یا NULL بررسی میکند. JSON_PATH_EXISTS از SQL Server 2022 برای آزمون وجود مسیر SQL/JSON ارائه شده است. اگر مسیر وجود داشته باشد یا دنباله غیرخالی تولید کند یک، در غیر این صورت صفر و برای ورودی SQL NULL مقدار NULL میدهد. این تابع هنگام داده نامعتبر نیز خطا برنمیگرداند و برای Guard و فیلتر مناسب است.
آموزش کامل JSON_PATH_EXISTS با مثالهای عملی
مثالهای ترکیبی و کاربردی
مثال 1: اعتبارسنجی و استخراج امن
ورودی API را پیش از خواندن شناسه کنترل میکنیم.
DECLARE @j nvarchar(max)=N'{"orderId":101,"status":"Paid"}';
SELECT CASE WHEN ISJSON(@j)=1 THEN JSON_VALUE(@j,'$.orderId') END AS OrderId;
ISJSON نقش Guard و JSON_VALUE نقش استخراج اسکالر را دارد.
مثال 2: تبدیل آرایه به ردیف
آیتمهای سفارش را برای Join و محاسبه باز میکنیم.
DECLARE @j nvarchar(max)=N'{"items":[{"sku":"A1","qty":2},{"sku":"B2","qty":1}]}';
SELECT Sku,Qty FROM OPENJSON(@j,'$.items')
WITH(Sku varchar(10) '$.sku',Qty int '$.qty');
OPENJSON با Schema صریح نوعها را همان ابتدای Pipeline تعیین میکند.
مثال 3: ویرایش کنترلشده
وضعیت و زمان بهروزرسانی را در سند تغییر میدهیم.
DECLARE @j nvarchar(max)=N'{"id":1,"status":"New"}';
SET @j=JSON_MODIFY(@j,'$.status',N'Paid');
SET @j=JSON_MODIFY(@j,'$.updatedAt',N'2026-07-20T19:00:00');
SELECT @j AS Result;
| status | updatedAt |
|---|
| Paid | 2026-07-20T19:00:00 |
JSON_MODIFY خروجی جدید میسازد و باید نتیجه آن ذخیره شود.
مثال 4: تولید پاسخ API
ردیفهای رابطهای را با Envelope مشخص به JSON تبدیل میکنیم.
DECLARE @T TABLE(Id int,Name nvarchar(20));
INSERT INTO @T VALUES(1,N'کالا یک'),(2,N'کالا دو');
SELECT Id,Name FROM @T ORDER BY Id FOR JSON PATH,ROOT('data');
FOR JSON PATH شکل خروجی را قابل پیشبینی و ORDER BY ترتیب را قطعی میکند.
مثال 5: بررسی وجود مسیر
در SQL Server 2022 وجود فیلد الزامی را پیش از مصرف میسنجیم.
DECLARE @j nvarchar(max)=N'{"customer":{"id":7}}';
SELECT JSON_PATH_EXISTS(@j,'$.customer.id') AS HasCustomerId;
وجود مسیر با مقدار غیرNULL یک مفهوم نیست و باید جداگانه تحلیل شود.
مثال 6: رفتوبرگشت رابطهای و JSON
آرایه را باز، فیلتر و دوباره Serialize میکنیم.
DECLARE @j nvarchar(max)=N'[{"id":1,"active":true},{"id":2,"active":false}]';
SELECT Id FROM OPENJSON(@j) WITH(Id int '$.id',Active bit '$.active')
WHERE Active=1
FOR JSON PATH;
ترکیب OPENJSON و FOR JSON برای تبدیل، پالایش و بازسازی Payload مناسب است.
کارایی، ایندکس و اندازهگیری
توابع JSON پردازش متنی انجام میدهند و هزینه آنها با تعداد ردیف، اندازه Payload و تعداد Pathها رشد میکند. عبارت تابعی در WHERE الزاماً از ایندکس معمول ستون متن استفاده نمیکند. برای Property پرتکرار میتوان ستون محاسباتی همسان با JSON_VALUE و سپس ایندکس ساخت، یا فیلد را به ستون رابطهای منتقل کرد.
بهینهسازی بدون خط پایه قابل اعتماد نیست. با SET STATISTICS IO, TIME، Actual Execution Plan و Query Store میزان CPU، خواندن منطقی، Memory Grant و مدت اجرا را ثبت کنید. اندازه خروجی FOR JSON و زمان شبکه را جدا بسنجید؛ ممکن است Query سریع باشد اما Payload بزرگ تجربه سرویس را کند کند.
در OPENJSON، Schema صریح به خوانایی و تبدیل نوع کمک میکند. در Queryهایی که چند مقدار از یک سند میخوانند، یک OPENJSON WITH میتواند از تکرار عبارتهای استخراج جلوگیری کند. بااینحال نتیجه به داده و Plan وابسته است و باید روی حجم واقعی آزمایش شود.
امنیت و کیفیت داده
- هر Payload بیرونی را از نظر اندازه، معتبر بودن JSON و قواعد تجاری کنترل کنید.
- از ساخت دستی JSON با Concatenate رشتهها خودداری کنید تا Escape و Injection منطقی رخ ندهد.
- Propertyهای حساس مانند Token، اطلاعات هویتی و داده مالی را بدون سیاست رمزنگاری و دسترسی ذخیره نکنید.
- Path متغیر را از فهرست مجاز انتخاب کنید و رفتار lax یا strict را ثبت کنید.
- قید CHECK، تست واحد، Schema مستند و Logging داده ردشده را در مرز ورود قرار دهید.
سازگاری نسخهها
توابع اصلی ISJSON، JSON_VALUE، JSON_QUERY، JSON_MODIFY و FOR JSON از SQL Server 2016 در دسترساند. OPENJSON در حالت معمول به Database Compatibility Level 130 یا بالاتر نیاز دارد. محدودیت نوع ISJSON و تابع JSON_PATH_EXISTS متعلق به SQL Server 2022 هستند. قابلیتهای SQL Server 2025 مانند نوع native json یا Wildcardهای توسعهیافته را نباید بدون کنترل نسخه در کد 2022 استفاده کرد.
سؤالات متداول
۱. توابع JSON دقیقاً چه مسئلهای را حل میکند؟
این قابلیت برای پردازش کامل JSON درون موتور SQL Server طراحی شده است و نیاز به دستکاری شکننده رشتهها را کم میکند. استفاده درست زمانی ارزشمند است که قرارداد JSON، نوع داده و مسیرها روشن باشند.
۲. برای شروع یادگیری توابع JSON چه پیشنیازی لازم است؟
آشنایی با SELECT، نوع nvarchar(max)، تفاوت SQL NULL و JSON null و مفهوم SQL/JSON Path کافی است. سپس مثالها را در دیتابیس آزمایشی اجرا و خروجی و خطاها را مقایسه کنید.
۳. آیا توابع JSON برای پروژه سازمانی و API مناسب است؟
بله، اگر Schema ورودی، محدودیت اندازه، اعتبارسنجی و پایش کارایی تعریف شود. در پروژه حساس بهتر است نمونه بار واقعی، برنامه اجرا و هزینه شبکه نیز پیش از استقرار ارزیابی شود.
۴. هزینه پیادهسازی حرفهای توابع JSON به چه عواملی بستگی دارد؟
حجم و تنوع Payload، تعداد مسیرها، نرخ درخواست، نیاز به Migration و ایندکسگذاری تعیینکنندهاند. مشاوره SQL Server میتواند طراحی رابطهای، JSON یا مدل ترکیبی را بر پایه اندازهگیری انتخاب کند.
۵. تفاوت توابع JSON با روشهای دیگر پردازش JSON چیست؟
این قابلیت داخل T-SQL اجرا میشود و جابهجایی داده را کاهش میدهد، اما همه منطق دامنه نباید الزاماً وارد دیتابیس شود. مقایسه صحیح به محل مصرف، حجم داده و نیاز تراکنشی وابسته است.
۶. آیا میتوان برای طراحی Queryهای توابع JSON خدمات تخصصی گرفت؟
برای Queryهای پیچیده، بازبینی مسیرها، طراحی Index و تحلیل Execution Plan میتوان از آموزش یا مشاوره تخصصی استفاده کرد. خروجی مطلوب باید همراه تست تکرارپذیر و معیار قبل و بعد تحویل شود.
۷. رایجترین خطا هنگام استفاده از توابع JSON چیست؟
فرضکردن شکل ثابت JSON بدون اعتبارسنجی، تبدیل نوع ضمنی و بیتوجهی به مسیر گمشده رایج است. قرارداد داده و تست حالتهای NULL، نامعتبر و مرزی جلوی بیشتر خطاهای عملی را میگیرد.
۸. چگونه کارایی توابع JSON را اندازهگیری کنیم؟
از Actual Execution Plan، SET STATISTICS IO, TIME و نمونه داده نزدیک به تولید استفاده کنید. CPU، Logical Read، Memory Grant، اندازه خروجی و زمان انتقال را جدا ثبت و نسخه بهینه را با خط پایه مقایسه کنید.
۹. بهترین روش استفاده از توابع JSON چیست؟
فقط ستون و مسیر لازم را پردازش کنید، نوعها را صریح تبدیل کنید، ورودی خارجی را کنترل و منطق پرتکرار را قابل ایندکس طراحی کنید. تست واحد و تست بار باید حالتهای نامعتبر را نیز پوشش دهد.
۱۰. توابع JSON در کدام نسخههای SQL Server قابل استفاده است؟
سازگاری دقیق به قابلیت وابسته است؛ برای توابع JSON باید نسخه موتور و Database Compatibility Level بررسی شود. پیش از Deploy مستندات نسخه هدف و اجرای آزمایشی روی همان محیط ملاک نهایی است.
سؤالات مصاحبه تخصصی
- تفاوت SQL NULL، JSON null و مسیر گمشده هنگام کار با توابع JSON چیست؟
- چگونه Query مبتنی بر توابع JSON را برای یک میلیون ردیف ارزیابی میکنید؟
- چه زمانی مدل رابطهای را به نگهداری JSON برای سناریوی توابع JSON ترجیح میدهید؟
- برای جلوگیری از تبدیل نوع ضمنی در خروجی توابع JSON چه میکنید؟
- چه تستهایی برای مسیرهای نامعتبر و Payload ناقص توابع JSON مینویسید؟
پاسخ حرفهای باید فقط Syntax را تکرار نکند؛ انتظار میرود نامزد درباره قرارداد داده، نسخه SQL Server، مسیر خطا، قابلیت ایندکسگذاری و روش اندازهگیری با Plan و آمار IO توضیح دهد.
چکلیست طراحی
- شکل JSON و نسخه قرارداد مشخص است.
- فیلدهای کلیدی و پرتکرار بهصورت رابطهای یا قابل ایندکس طراحی شدهاند.
- SQL NULL، JSON null و Property غایب تست شدهاند.
- نسخه موتور و Compatibility Level کنترل شده است.
- Payload نامعتبر، بزرگ و دارای نویسه ویژه در تستها وجود دارد.
- Plan، IO، CPU و اندازه شبکه پیش و پس از تغییر ثبت شدهاند.
جمعبندی و ادامه مطالعه
خانواده JSON در SQL Server یک Pipeline کامل میسازد: ISJSON برای کنترل ورودی، JSON_VALUE و JSON_QUERY برای خواندن، JSON_MODIFY برای تغییر، OPENJSON برای رابطهایکردن، FOR JSON برای Serialize و JSON_PATH_EXISTS برای بررسی مسیر در نسخه 2022. طراحی درست یعنی انتخاب تابع بر پایه نوع خروجی و نگهداشتن قواعد هسته کسبوکار در ساختاری قابل کنترل.