آموزش کامل JSON_VALUE در SQL Server
مقدمه
JSON_VALUE مقدارهای اسکالر مانند نام، شناسه، تاریخ یا وضعیت را از مسیر مشخص JSON استخراج میکند و در فیلتر و گزارشگیری کاربرد فراوان دارد. در سامانههای امروزی، JSON معمولاً از API، صف پیام یا تنظیمات انعطافپذیر وارد دیتابیس میشود. اجرای درست JSON_VALUE نیازمند شناخت تفاوت متن JSON با داده رابطهای، مسیرهای SQL/JSON و نوع خروجی است.
هدف این مقاله ارائه یک مرجع اجرایی است: از Syntax و رفتار NULL تا خطاهای واقعی، ملاحظات کارایی و طراحی قابل نگهداری. همه مثالها مستقلاند و میتوان آنها را در محیط آزمایشی SQL Server اجرا کرد؛ پیش از اجرای DDL در سامانه واقعی، نامها و سیاست پاکسازی را با استاندارد پروژه هماهنگ کنید.
بازگشت به راهنمای جامع توابع JSON در SQL Server
تعریف تابع JSON_VALUE
JSON_VALUE یک مقدار اسکالر را از متن JSON و مسیر SQL/JSON میخواند. در SQL Server 2022 و نسخههای قدیمیتر خروجی nvarchar(4000) است و برای اشیاء یا آرایهها باید JSON_QUERY به کار رود. حالت lax برای مسیر گمشده NULL میدهد، در حالی که strict خطای قابل مشاهده ایجاد میکند.
JSON در نسخههای رایج SQL Server عمدتاً در ستونهای nvarchar ذخیره میشود. بنابراین معتبر بودن Syntax به معنی صحیح بودن قواعد تجاری نیست؛ شناسه، تاریخ، طول متن و مجوز دسترسی همچنان باید با قید، تبدیل امن یا منطق برنامه کنترل شوند.
Syntax
SELECT JSON_VALUE(expression, path);
پارامترها
- expression: ستون، متغیر یا عبارت کاراکتری شامل JSON.
- path: مسیر SQL/JSON مانند $.customer.name؛ از SQL Server 2017 میتواند متغیر باشد.
- خروجی در SQL Server 2022: nvarchar(4000) با Collation همان عبارت ورودی.
نوع خروجی و رفتار مسیر
برای اسکالر موجود، متن مقدار بازگردانده میشود. مسیر گمشده در lax برابر NULL است. مقدار بزرگتر از 4000 نویسه در lax نیز NULL میشود و برای آن باید OPENJSON استفاده شود. شیء و آرایه خروجی JSON_VALUE نیستند.
مثالهای عملی
مثال 1: خواندن مقدار سطح اول
نام مشتری را از یک شیء کوچک استخراج میکنیم.
DECLARE @j nvarchar(max)=N'{"name":"مینا","id":7}';
SELECT JSON_VALUE(@j,'$.name') AS CustomerName;
JSON_VALUE برای رشته، عدد و Boolean اسکالر طراحی شده است.
مثال 2: مسیر تودرتو
شهر را از شیء address درون سند میخوانیم.
DECLARE @j nvarchar(max)=N'{"address":{"city":"تبریز"}}';
SELECT JSON_VALUE(@j,'$.address.city') AS City;
هر نقطه یک سطح Property را مشخص میکند و نامها نسبت به شکل سند حساس هستند.
مثال 3: عضو آرایه با Index
اولین مهارت کاربر را با Index صفر بازیابی میکنیم.
SELECT JSON_VALUE(N'{"skills":["SQL","C#"]}','$.skills[0]') AS FirstSkill;
اندیس آرایه از صفر آغاز میشود؛ خارجشدن از محدوده در lax مقدار NULL میدهد.
مثال 4: استخراج از جدول
از چند Payload جدولی یک ستون رابطهای موقت میسازیم.
DECLARE @Orders TABLE(Id int, Data nvarchar(max));
INSERT INTO @Orders VALUES
(1,N'{"status":"Paid"}'),(2,N'{"status":"Pending"}');
SELECT Id, JSON_VALUE(Data,'$.status') AS Status FROM @Orders;
این روش برای گزارش سبک مناسب است؛ برای تحلیل سنگین طراحی رابطهای را هم ارزیابی کنید.
مثال 5: فیلتر WHERE
فقط سفارشهای پرداختشده را از داده نمونه جدا میکنیم.
DECLARE @Orders TABLE(Id int, Data nvarchar(max));
INSERT INTO @Orders VALUES(1,N'{"status":"Paid"}'),(2,N'{"status":"New"}');
SELECT Id FROM @Orders
WHERE JSON_VALUE(Data,'$.status')=N'Paid';
برای حجم بالا، ستون محاسباتی Status و ایندکس آن معمولاً قابل بررسی است.
مثال 6: مسیر گمشده و NULL
رفتار lax را برای Property موجودنبودن مشاهده میکنیم.
SELECT JSON_VALUE(N'{"id":1}','$.missing') AS MissingValue;
NULL ممکن است هم از نبود مسیر و هم از مقدار نامناسب ناشی شود؛ در منطق تجاری این دو را تفکیک کنید.
مثال 7: تبدیل عددی امن
قیمت متنی JSON را برای محاسبه به decimal تبدیل میکنیم.
DECLARE @j nvarchar(max)=N'{"price":"125.50"}';
SELECT TRY_CONVERT(decimal(10,2),JSON_VALUE(@j,'$.price'))*2 AS Total;
TRY_CONVERT ورودی ناسازگار را به NULL تبدیل میکند و از شکست کل Batch جلوگیری میکند.
مثال 8: کلید دارای فاصله
نام Property دارای فاصله را با Quote مناسب در Path میخوانیم.
SELECT JSON_VALUE(N'{"order id":9001}', '$."order id"') AS OrderId;
نامهای ویژه باید در Path داخل Double Quote قرار گیرند.
مثال 9: ستون محاسباتی قابل ایندکس
وضعیت سفارش را به ستون PERSISTED تبدیل میکنیم تا جستوجو سریعتر شود.
CREATE TABLE #JsonOrders
(
Id int PRIMARY KEY,
Data nvarchar(1000) NOT NULL,
Status AS JSON_VALUE(Data,'$.status') PERSISTED
);
CREATE INDEX IX_JsonOrders_Status ON #JsonOrders(Status);
INSERT INTO #JsonOrders VALUES(1,N'{"status":"Paid"}');
SELECT Id FROM #JsonOrders WHERE Status=N'Paid';
عبارت ستون محاسباتی و Query باید همسان باشد تا Optimizer بتواند از ایندکس بهره ببرد.
مثال 10: استخراج چند مقدار در یک گذر
بهجای چند فراخوانی تکراری، سند را با OPENJSON و WITH به ستون تبدیل میکنیم.
DECLARE @j nvarchar(max)=N'{"id":12,"name":"رضا","active":true}';
SELECT Id, Name, Active
FROM OPENJSON(@j) WITH
(
Id int '$.id',
Name nvarchar(50) '$.name',
Active bit '$.active'
);
برای استخراج چندین فیلد، OPENJSON با Schema صریح خواناتر است و میتواند پردازش تکراری را کم کند.
خطاهای رایج
خطاهای JSON_VALUE معمولاً از فرض نادرست درباره ساختار ورودی یا نسخه موتور ناشی میشوند. فهرست زیر را در Code Review و تست خودکار کنترل کنید.
- استفاده برای استخراج شیء یا آرایه بهجای JSON_QUERY.
- فراموشکردن محدودیت 4000 نویسه در SQL Server 2022.
- مقایسه قیمت و شناسه بهصورت رشتهای بدون TRY_CONVERT.
- نوشتن مسیر کلیدهای دارای فاصله بدون Double Quote در Path.
نکات کارایی و بهینهسازی
فراخوانی JSON_VALUE روی هر ردیف و چند بار برای یک مسیر هزینه CPU دارد. برای مسیرهای پرتکرار، ستون محاسباتی همسان با عبارت Query و ایندکس مناسب بسازید. تبدیل نوع را صریح انجام دهید تا مقایسه عددی به مقایسه رشتهای تبدیل نشود.
- قبل و بعد از تغییر، SET STATISTICS IO, TIME و Actual Execution Plan را ثبت کنید.
- روی داده نزدیک به حجم و توزیع محیط تولید آزمایش کنید؛ نتیجه جدول کوچک معیار کافی نیست.
- از پردازش چندباره همان Path در SELECT، WHERE و ORDER BY بدون ارزیابی جلوگیری کنید.
- اندازه Payload، طول ستون و هزینه شبکه را در کنار زمان Query گزارش کنید.
بهترین روشها
- ورودی خارجی را با ISJSON و قواعد تجاری معتبر کنید.
- مسیرها را ثابت یا از فهرست مجاز انتخاب کنید و نوع مقصد را صریح بنویسید.
- برای فیلد پرتکرار و قابل جستوجو، ستون رابطهای یا محاسباتی ایندکسپذیر را ارزیابی کنید.
- رفتار lax، strict، NULL و مسیر گمشده را در قرارداد API مستند کنید.
- از Concatenate دستی JSON خودداری و توابع داخلی Serializer را استفاده کنید.
کاربرد واقعی در پروژه
در یک معماری سازمانی، JSON_VALUE میتواند بخشی از مرحله ورود داده، گزارشگیری یا تولید پاسخ باشد؛ اما مرز مسئولیت باید روشن بماند. داده تراکنشی پرتکرار معمولاً از ستونهای نوعدار و قیدهای رابطهای سود میبرد، در حالی که بخش اختیاری و کمجستوجوی Payload میتواند JSON باقی بماند. تصمیم نهایی را با شاخصهای قابل اندازهگیری بگیرید.
سازگاری نسخه و استقرار
پیش از استقرار JSON_VALUE نسخه SQL Server، Compatibility Level، رفتار Collation و اندازه واقعی داده را کنترل کنید. اسکریپت Deployment باید روی نسخهای همسان با تولید اجرا شود و Rollback، تست Payload نامعتبر و مانیتورینگ Query Store را دربر بگیرد.
سؤالات متداول
۱. JSON_VALUE دقیقاً چه مسئلهای را حل میکند؟
این قابلیت برای استخراج مقدار اسکالر از JSON درون موتور SQL Server طراحی شده است و نیاز به دستکاری شکننده رشتهها را کم میکند. استفاده درست زمانی ارزشمند است که قرارداد JSON، نوع داده و مسیرها روشن باشند.
۲. برای شروع یادگیری JSON_VALUE چه پیشنیازی لازم است؟
آشنایی با SELECT، نوع nvarchar(max)، تفاوت SQL NULL و JSON null و مفهوم SQL/JSON Path کافی است. سپس مثالها را در دیتابیس آزمایشی اجرا و خروجی و خطاها را مقایسه کنید.
۳. آیا JSON_VALUE برای پروژه سازمانی و API مناسب است؟
بله، اگر Schema ورودی، محدودیت اندازه، اعتبارسنجی و پایش کارایی تعریف شود. در پروژه حساس بهتر است نمونه بار واقعی، برنامه اجرا و هزینه شبکه نیز پیش از استقرار ارزیابی شود.
۴. هزینه پیادهسازی حرفهای JSON_VALUE به چه عواملی بستگی دارد؟
حجم و تنوع Payload، تعداد مسیرها، نرخ درخواست، نیاز به Migration و ایندکسگذاری تعیینکنندهاند. مشاوره SQL Server میتواند طراحی رابطهای، JSON یا مدل ترکیبی را بر پایه اندازهگیری انتخاب کند.
۵. تفاوت JSON_VALUE با روشهای دیگر پردازش JSON چیست؟
این قابلیت داخل T-SQL اجرا میشود و جابهجایی داده را کاهش میدهد، اما همه منطق دامنه نباید الزاماً وارد دیتابیس شود. مقایسه صحیح به محل مصرف، حجم داده و نیاز تراکنشی وابسته است.
۶. آیا میتوان برای طراحی Queryهای JSON_VALUE خدمات تخصصی گرفت؟
برای Queryهای پیچیده، بازبینی مسیرها، طراحی Index و تحلیل Execution Plan میتوان از آموزش یا مشاوره تخصصی استفاده کرد. خروجی مطلوب باید همراه تست تکرارپذیر و معیار قبل و بعد تحویل شود.
۷. رایجترین خطا هنگام استفاده از JSON_VALUE چیست؟
فرضکردن شکل ثابت JSON بدون اعتبارسنجی، تبدیل نوع ضمنی و بیتوجهی به مسیر گمشده رایج است. قرارداد داده و تست حالتهای NULL، نامعتبر و مرزی جلوی بیشتر خطاهای عملی را میگیرد.
۸. چگونه کارایی JSON_VALUE را اندازهگیری کنیم؟
از Actual Execution Plan، SET STATISTICS IO, TIME و نمونه داده نزدیک به تولید استفاده کنید. CPU، Logical Read، Memory Grant، اندازه خروجی و زمان انتقال را جدا ثبت و نسخه بهینه را با خط پایه مقایسه کنید.
۹. بهترین روش استفاده از JSON_VALUE چیست؟
فقط ستون و مسیر لازم را پردازش کنید، نوعها را صریح تبدیل کنید، ورودی خارجی را کنترل و منطق پرتکرار را قابل ایندکس طراحی کنید. تست واحد و تست بار باید حالتهای نامعتبر را نیز پوشش دهد.
۱۰. JSON_VALUE در کدام نسخههای SQL Server قابل استفاده است؟
سازگاری دقیق به قابلیت وابسته است؛ برای JSON_VALUE باید نسخه موتور و Database Compatibility Level بررسی شود. پیش از Deploy مستندات نسخه هدف و اجرای آزمایشی روی همان محیط ملاک نهایی است.
سؤالات مصاحبه تخصصی
- تفاوت SQL NULL، JSON null و مسیر گمشده هنگام کار با JSON_VALUE چیست؟
- چگونه Query مبتنی بر JSON_VALUE را برای یک میلیون ردیف ارزیابی میکنید؟
- چه زمانی مدل رابطهای را به نگهداری JSON برای سناریوی JSON_VALUE ترجیح میدهید؟
- برای جلوگیری از تبدیل نوع ضمنی در خروجی JSON_VALUE چه میکنید؟
- چه تستهایی برای مسیرهای نامعتبر و Payload ناقص JSON_VALUE مینویسید؟
پاسخ حرفهای باید فقط Syntax را تکرار نکند؛ انتظار میرود نامزد درباره قرارداد داده، نسخه SQL Server، مسیر خطا، قابلیت ایندکسگذاری و روش اندازهگیری با Plan و آمار IO توضیح دهد.
چکلیست نهایی
- Syntax روی نسخه هدف اجرا شده است.
- حالت NULL، مسیر گمشده و JSON نامعتبر تست شده است.
- نوع خروجی و تبدیل عدد، تاریخ یا Boolean صریح است.
- تعداد Logical Read و زمان CPU ثبت شده است.
- مسیرها و قرارداد خروجی در مستندات پروژه درج شدهاند.
- مجوزها و داده حساس در Payload بازبینی شدهاند.
جمعبندی
تابع JSON_VALUE وقتی ارزشمند است که همراه قرارداد داده، تست مرزی و سنجش کارایی استفاده شود. مثالهای این مقاله از حالت پایه تا سناریوی جدول، NULL، خطا و بهینهسازی را پوشش دادند. برای انتخاب تابع مکمل و دیدن نقشه کامل پردازش JSON، بازگشت به راهنمای جامع توابع JSON در SQL Server را مطالعه کنید.