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

مقدمه

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

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

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

تعریف تابع ISJSON

ISJSON یک تابع اسکالر است که متن ورودی را از نظر قواعد نحوی JSON بررسی می‌کند. خروجی یک برای متن معتبر، صفر برای متن نامعتبر و در برابر NULL مقدار NULL است. در SQL Server 2022 می‌توان با محدودیت نوع، OBJECT، ARRAY، VALUE یا SCALAR را نیز جداگانه آزمود.

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

Syntax

SELECT ISJSON(expression [, json_type_constraint]);

پارامترها

• expression: عبارت کاراکتری حاوی متن مورد بررسی.
• json_type_constraint: گزینه SQL Server 2022 برای محدودکردن نوع به VALUE، ARRAY، OBJECT یا SCALAR.
• خروجی: عدد صحیح 1، 0 یا NULL؛ این تابع برای متن نامعتبر خطا تولید نمی‌کند.

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

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

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

مثال 1: اعتبارسنجی یک شیء ساده

یک Payload کوچک را پیش از استخراج مقدار بررسی می‌کنیم.

SELECT ISJSON(N'{"id":101,"name":"Sara"}') AS IsValid;

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

مثال 2: بررسی آرایه JSON

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

SELECT ISJSON(N'[10,20,30]') AS IsValidArray;

آرایه در حالت پیش‌فرض یک سند معتبر JSON محسوب می‌شود.

مثال 3: تفاوت Scalar با حالت پیش‌فرض

در SQL Server 2022 نشان می‌دهیم مقدار اسکالر سطح بالا فقط با محدودیت SCALAR پذیرفته می‌شود.

SELECT ISJSON(N'42') AS DefaultCheck,
       ISJSON(N'42', SCALAR) AS ScalarCheck;

برای APIهایی که اسکالر مستقل می‌پذیرند، محدودیت نوع از رد اشتباه داده جلوگیری می‌کند.

مثال 4: فیلتر ردیف‌های معتبر

داده‌های ورودی را در یک جدول نمونه قرار می‌دهیم و فقط JSONهای معتبر را برمی‌گردانیم.

DECLARE @Inbox TABLE(Id int, Payload nvarchar(max));
INSERT INTO @Inbox VALUES
(1,N'{"ok":true}'),(2,N'{bad json}'),(3,NULL);
SELECT Id, Payload FROM @Inbox WHERE ISJSON(Payload)=1;

مقایسه صریح با یک، ردیف NULL و نامعتبر را هم‌زمان کنار می‌گذارد.

مثال 5: کنترل نوع شیء و آرایه

در SQL Server 2022 شکل قرارداد ورودی را دقیق‌تر کنترل می‌کنیم.

SELECT ISJSON(N'{"id":1}', OBJECT) AS IsObject,
       ISJSON(N'[1,2]', ARRAY) AS IsArray;

این الگو برای تفکیک Endpointهایی که شیء یا مجموعه می‌پذیرند مفید است.

مثال 6: رفتار در برابر NULL

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

DECLARE @payload nvarchar(max)=NULL;
SELECT ISJSON(@payload) AS ValidationResult;

NULL به معنای نامعتبر بودن قطعی نیست؛ وضعیت ناشناخته است و باید جداگانه مدیریت شود.

مثال 7: کلیدهای تکراری

محدودیت ISJSON درباره یکتایی نام Propertyها را آشکار می‌کنیم.

SELECT ISJSON(N'{"code":1,"code":2}') AS IsValid;

خروجی یک است؛ اگر یکتایی کلید مهم است باید بعد از OPENJSON کنترل تجاری دیگری انجام شود.

مثال 8: قید CHECK برای کیفیت داده

یک جدول نمونه می‌سازیم که فقط JSON معتبر یا NULL را قبول کند.

CREATE TABLE #Messages
(
    Id int IDENTITY PRIMARY KEY,
    Payload nvarchar(max) NULL,
    CONSTRAINT CK_Messages_Json CHECK(Payload IS NULL OR ISJSON(Payload)=1)
);
INSERT INTO #Messages(Payload) VALUES(N'{"status":"ok"}');
SELECT Id, Payload FROM #Messages;

قید CHECK خطا را به مرز ورود داده منتقل می‌کند و کیفیت ذخیره‌سازی را بالا می‌برد.

مثال 9: محافظت از JSON_VALUE

پیش از استخراج مقدار، نامعتبر بودن متن را مدیریت می‌کنیم تا Query شکست نخورد.

DECLARE @j nvarchar(max)=N'{invalid}';
SELECT CASE WHEN ISJSON(@j)=1
            THEN JSON_VALUE(@j,'$.name')
            ELSE N'ورودی نامعتبر' END AS SafeValue;

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

مثال 10: ستون محاسباتی و ایندکس

برای جست‌وجوی پرتکرار وضعیت اعتبار، نتیجه بررسی را در ستون PERSISTED نگه می‌داریم.

CREATE TABLE #Events
(
    EventId int NOT NULL,
    Payload nvarchar(4000) NULL,
    IsValidJson AS ISJSON(Payload) PERSISTED
);
CREATE INDEX IX_Events_IsValidJson ON #Events(IsValidJson);
INSERT INTO #Events VALUES(1,N'{"type":"sale"}'),(2,N'bad');
SELECT EventId FROM #Events WHERE IsValidJson=1;

برای جدول واقعی، اثر ایندکس را با Execution Plan و آمار IO بسنجید؛ مزیت آن به توزیع داده وابسته است.

خطاهای رایج

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

• فرض اینکه ISJSON یکتایی کلیدهای هم‌سطح را کنترل می‌کند؛ چنین کنترلی انجام نمی‌شود.
• نادیده‌گرفتن خروجی NULL و استفاده از شرط مخالف صفر به‌جای مقایسه روشن با 1.
• استفاده از آرگومان نوع در نسخه‌های قبل از SQL Server 2022.
• اعتبارسنجی مکرر یک Payload بزرگ در چند بخش همان Query.

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

ISJSON محتوای رشته را بررسی می‌کند و روی ستون بزرگ هزینه پردازشی دارد. برای بار کاری خواندنی، نتیجه را هنگام ورود اعتبارسنجی کنید یا یک ستون محاسباتی PERSISTED بسازید. اجرای تابع روی همه ردیف‌ها در WHERE معمولاً جایگزین ایندکس مناسب نیست.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

جمع‌بندی

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