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

مقدمه

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

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

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

تعریف تابع JSON_MODIFY

JSON_MODIFY یک سند JSON را در محل تغییر نمی‌دهد، بلکه متن جدیدی با تغییر خواسته‌شده بازمی‌گرداند. با آن می‌توان مقدار موجود را عوض کرد، Property تازه افزود، کلید را در lax حذف کرد یا عضو آرایه را Append نمود. تفاوت lax و strict در رفتار مسیر گمشده و NULL اهمیت زیادی دارد.

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

Syntax

SELECT JSON_MODIFY(expression, path, newValue);

پارامترها

• expression: متن JSON ورودی.
• path: مسیر هدف، با گزینه‌های lax، strict یا append.
• newValue: مقدار جدید؛ نوع آن روی عددی، Boolean یا رشته‌شدن خروجی اثر دارد.

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

خروجی متن JSON اصلاح‌شده است. در lax، مقدار NULL معمولاً Property موجود را حذف می‌کند؛ در strict، همان Property به JSON null تبدیل می‌شود. برای جاسازی شیء یا آرایه باید newValue را با JSON_QUERY معتبر معرفی کرد.

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

مثال 1: به‌روزرسانی مقدار موجود

نام موجود را تغییر می‌دهیم و خروجی جدید را نگه می‌داریم.

DECLARE @j nvarchar(max)=N'{"name":"Ali","age":30}';
SET @j=JSON_MODIFY(@j,'$.name',N'Reza');
SELECT @j AS UpdatedJson;

بدون SET یا UPDATE، نتیجه تابع ذخیره نمی‌شود.

مثال 2: افزودن Property جدید

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

SELECT JSON_MODIFY(N'{"id":10}','$.status',N'Paid') AS Result;

در lax و با والد موجود، Property جدید ساخته می‌شود.

مثال 3: حذف Property در lax

یک مقدار حساس را با SQL NULL حذف می‌کنیم.

SELECT JSON_MODIFY(N'{"user":"a","token":"secret"}','$.token',NULL) AS Result;

در حالت پیش‌فرض lax، NULL به معنی حذف Property موجود است.

مثال 4: ثبت JSON null در strict

به‌جای حذف، مقدار Property موجود را null می‌کنیم.

SELECT JSON_MODIFY(N'{"name":"Ali"}','strict $.name',NULL) AS Result;

تفاوت strict با lax را در قرارداد داده مستند کنید تا مصرف‌کننده غافلگیر نشود.

مثال 5: افزودن عضو آرایه

یک مهارت تازه را به انتهای آرایه اضافه می‌کنیم.

SELECT JSON_MODIFY(N'{"skills":["SQL"]}','append $.skills',N'C#') AS Result;

append فقط زمانی قابل اتکاست که مسیر به آرایه مناسب برسد.

مثال 6: افزایش شمارنده عددی

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

DECLARE @j nvarchar(max)=N'{"count":5}';
SET @j=JSON_MODIFY(@j,'$.count',CONVERT(int,JSON_VALUE(@j,'$.count'))+1);
SELECT @j AS Result;

تبدیل صریح باعث می‌شود مقدار نهایی عدد JSON باشد، نه رشته کوتیشن‌دار.

مثال 7: تغییر نام کلید

مقدار را در نام جدید می‌نویسیم و کلید قدیمی را حذف می‌کنیم.

DECLARE @j nvarchar(max)=N'{"price":49.90}';
SET @j=JSON_MODIFY(@j,'$.unitPrice',TRY_CONVERT(decimal(10,2),JSON_VALUE(@j,'$.price')));
SET @j=JSON_MODIFY(@j,'$.price',NULL);
SELECT @j AS Result;

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

مثال 8: افزودن شیء بدون Escape

یک شیء معتبر را با JSON_QUERY در Property جدید قرار می‌دهیم.

DECLARE @address nvarchar(max)=N'{"city":"Rasht"}';
SELECT JSON_MODIFY(N'{"id":1}','$.address',JSON_QUERY(@address)) AS Result;

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

مثال 9: به‌روزرسانی جدول با Guard

فقط Payloadهای معتبر و دارای وضعیت را اصلاح می‌کنیم.

DECLARE @Jobs TABLE(Id int, Payload nvarchar(max));
INSERT INTO @Jobs VALUES(1,N'{"status":"New"}'),(2,N'bad');
UPDATE @Jobs
SET Payload=JSON_MODIFY(Payload,'$.status',N'Done')
WHERE ISJSON(Payload)=1 AND JSON_VALUE(Payload,'$.status')=N'New';
SELECT Id,Payload FROM @Jobs;

Guard از شکست روی متن ناسالم جلوگیری می‌کند و دامنه UPDATE را محدود نگه می‌دارد.

مثال 10: چند تغییر زنجیره‌ای

برای Payload کوچک چند Property را در یک عبارت اصلاح می‌کنیم.

DECLARE @j nvarchar(max)=N'{"id":1,"status":"New"}';
SELECT JSON_MODIFY(
         JSON_MODIFY(@j,'$.status',N'Paid'),
         '$.updatedAt',N'2026-07-20T19:00:00'
       ) AS Result;

روی اسناد بزرگ تعداد بازنویسی‌ها را با اندازه Log و زمان CPU اندازه‌گیری کنید.

خطاهای رایج

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

• فراموش‌کردن انتساب خروجی به متغیر یا ستون.
• انتظار ساخت خودکار همه والدهای گمشده در lax.
• ارسال شیء JSON به‌صورت nvarchar ساده و دریافت متن Escape‌شده.
• حذف ناخواسته Property با newValue برابر SQL NULL در lax.

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

هر فراخوانی متن JSON تازه‌ای می‌سازد؛ زنجیره طولانی تغییرات روی Payload بزرگ پرهزینه است. تغییرات دسته‌ای را در لایه مناسب طراحی کنید و اگر یک فیلد مرتب به‌روزرسانی می‌شود، شاید ستون رابطه‌ای انتخاب بهتری باشد. Log و اندازه Row را نیز پایش کنید.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

جمع‌بندی

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