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

مقدمه

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

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

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

تعریف تابع JSON_QUERY

JSON_QUERY برای بازگرداندن شیء یا آرایه از مسیر مشخص استفاده می‌شود. برخلاف JSON_VALUE که اسکالر می‌خواند، خروجی JSON_QUERY یک Fragment معتبر JSON از نوع nvarchar(max) است. این تابع همچنین هنگام ساخت خروجی FOR JSON علامت می‌دهد که Fragment نباید دوباره Escape شود.

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

Syntax

SELECT JSON_QUERY(expression [, path]);

پارامترها

• expression: متن یا ستون JSON.
• path: مسیر شیء یا آرایه؛ اگر حذف شود کل expression بررسی و بازگردانده می‌شود.
• حالت مسیر lax یا strict را می‌توان پیش از علامت دلار نوشت.

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

در مسیر منتهی به شیء یا آرایه، Fragment JSON بازمی‌گردد. اگر مسیر در lax به اسکالر برسد یا پیدا نشود، نتیجه NULL است. خروجی nvarchar(max) و مناسب ترکیب با FOR JSON است.

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

مثال 1: استخراج یک شیء

شیء address را به شکل JSON معتبر جدا می‌کنیم.

SELECT JSON_QUERY(N'{"name":"Ali","address":{"city":"Qom"}}','$.address') AS AddressJson;

ساختار شیء حفظ می‌شود و برای ارسال یا پردازش بعدی آماده است.

مثال 2: استخراج آرایه

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

SELECT JSON_QUERY(N'{"roles":["Admin","Editor"]}','$.roles') AS Roles;

برای تبدیل اعضای آرایه به ردیف، خروجی را به OPENJSON بدهید.

مثال 3: تفاوت با اسکالر

نشان می‌دهیم مسیر اسکالر در حالت lax برای JSON_QUERY مناسب نیست.

SELECT JSON_QUERY(N'{"name":"Ali"}','$.name') AS ScalarResult;

برای این مسیر باید JSON_VALUE به کار رود.

مثال 4: بازگرداندن کل سند

با حذف Path، یک سند معتبر کامل را بازمی‌گردانیم.

DECLARE @j nvarchar(max)=N'{"id":1,"items":[1,2]}';
SELECT JSON_QUERY(@j) AS WholeDocument;

این روش برای معرفی متن به‌عنوان Fragment معتبر در Query ترکیبی مفید است.

مثال 5: جلوگیری از Escape در FOR JSON

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

SELECT JSON_QUERY(N'{"x":1}') AS Data
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER;

بدون JSON_QUERY کوتیشن‌ها Escape می‌شوند و Data به رشته تبدیل می‌شود.

مثال 6: خواندن زیرشیء از جدول

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

DECLARE @Users TABLE(Id int, Payload nvarchar(max));
INSERT INTO @Users VALUES(1,N'{"profile":{"city":"Shiraz"}}');
SELECT Id, JSON_QUERY(Payload,'$.profile') AS Profile FROM @Users;

Fragment را می‌توان به سرویس دیگری تحویل داد یا با OPENJSON باز کرد.

مثال 7: مسیر آرایه تو‌در‌تو

آیتم‌های سفارش را از ساختار چندسطحی جدا می‌کنیم.

DECLARE @j nvarchar(max)=N'{"order":{"items":[{"sku":"A1"},{"sku":"B2"}]}}';
SELECT JSON_QUERY(@j,'$.order.items') AS Items;

Path دقیق باعث می‌شود فقط بخش لازم جابه‌جا شود.

مثال 8: رفتار با SQL NULL

ورودی NULL را بدون ایجاد خطا بررسی می‌کنیم.

DECLARE @j nvarchar(max)=NULL;
SELECT JSON_QUERY(@j,'$.data') AS Result;

در Pipeline باید نبود سند را از نبود مسیر تجاری جدا کنید.

مثال 9: ساخت آرایه فرزند

ردیف‌های رابطه‌ای را با FOR JSON ساخته و به‌عنوان آرایه معتبر نگه می‌داریم.

DECLARE @Items TABLE(Sku varchar(10),Qty int);
INSERT INTO @Items VALUES('A1',2),('B2',1);
SELECT JSON_QUERY((SELECT Sku,Qty FROM @Items FOR JSON PATH)) AS Items;

JSON_QUERY مانع Double Escaping خروجی داخلی می‌شود.

مثال 10: تجزیه چند Fragment با AS JSON

چند بخش ساخت‌یافته را در یک گذر با OPENJSON بیرون می‌آوریم.

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

AS JSON برای چند استخراج ساخت‌یافته، جایگزین خوانا و قابل سنجش برای فراخوانی‌های متعدد است.

خطاهای رایج

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

• انتظار مقدار اسکالر از JSON_QUERY.
• فراموش‌کردن JSON_QUERY هنگام جاسازی Fragment در FOR JSON و دریافت رشته Escape‌شده.
• استفاده از strict روی داده بیرونی بدون مدیریت خطا.
• ذخیره سند بسیار بزرگ و استخراج چندباره زیرساخت مشابه.

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

اگر از یک سند بزرگ چند زیرشیء استخراج می‌کنید، تکرار JSON_QUERY می‌تواند گران شود. OPENJSON همراه AS JSON برای تجزیه یک‌مرحله‌ای گزینه بهتری است. مسیرهای ثابت و کوتاه و نگهداری سند با اندازه منطقی، هزینه CPU را کنترل می‌کند.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

جمع‌بندی

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