استخراج داده JSON در SQL Server 2025 با توابع JSON VALUE QUERY OPENJSON

استخراج داده‌های JSON در SQL Server 2025 با تابع OPENJSON: راهنمای جامع برای متخصصان پایگاه داده

JSON به رایج‌ترین فرمت داده متنی برای تبادل اطلاعات تبدیل شده است، چه برای تبادل داده‌های API و چه برای ذخیره‌سازی داده‌های نیمه‌ساختاریافته در پایگاه داده. SQL Server 2025 نیز، همانند نسخه‌های پیشین، قابلیت‌های قدرتمندی برای کار با داده‌های JSON ارائه می‌دهد که به توسعه‌دهندگان و مدیران پایگاه داده امکان ذخیره‌سازی، جستجو و دستکاری JSON را درون موتور پایگاه داده می‌دهد.

توابع JSON در SQL Server

SQL Server مجموعه‌ای از توابع داخلی را برای کار با داده‌های JSON ارائه می‌دهد. این توابع به شما امکان می‌دهند داده‌های JSON را ایجاد، اعتبارسنجی، اصلاح و استخراج کنید. سه مورد از پرکاربردترین توابع برای استخراج داده‌ها عبارتند از: JSON_VALUE، JSON_QUERY و OPENJSON.

برای نشان دادن کارکرد این توابع، یک مثال اولیه از استفاده از آنها را بررسی می‌کنیم:

SELECT
    GETDATE() AS CurrentDateTime,
    JSON_QUERY('{"name": "John Doe", "age": 30, "details": {"city": "New York"}}', '$.details') AS DetailsObject,
    JSON_VALUE('{"name": "Jane Doe", "age": 25}', '$.age') AS AgeValue;

ذخیره‌سازی داده‌های JSON

ذخیره‌سازی داده‌های JSON در SQL Server بسیار ساده است. شما می‌توانید داده‌های JSON را در ستون‌هایی با نوع داده NVARCHAR(MAX) ذخیره کنید. SQL Server هیچ نوع داده اختصاصی برای JSON ندارد، اما توابع JSON آن، اعتبارسنجی و قابلیت‌های قدرتمندی برای مدیریت این داده‌ها فراهم می‌کنند.

برای شروع، یک جدول ایجاد می‌کنیم که شامل یک ستون برای ذخیره داده‌های JSON باشد:

CREATE TABLE SalesOrders (
    OrderID INT PRIMARY KEY IDENTITY(1,1),
    OrderDate DATETIME DEFAULT GETDATE(),
    CustomerInfo NVARCHAR(MAX) CHECK (ISJSON(CustomerInfo) > 0),
    OrderDetails NVARCHAR(MAX) CHECK (ISJSON(OrderDetails) > 0)
);
GO

در این مثال، از محدودیت CHECK (ISJSON(ColumnName) > 0) استفاده شده تا اطمینان حاصل شود که داده‌های وارد شده در ستون‌ها فرمت JSON معتبری دارند. این یک روش عالی برای حفظ یکپارچگی داده‌ها است.

حالا چندین رکورد را با داده‌های JSON وارد می‌کنیم:

INSERT INTO SalesOrders (CustomerInfo, OrderDetails) VALUES
(
    '{"name": "Alice Smith", "email": "alice@example.com", "address": {"street": "123 Main St", "city": "Anytown"}}',
    '[{"product": "Laptop", "quantity": 1, "price": 1200}, {"product": "Mouse", "quantity": 2, "price": 25}]'
),
(
    '{"name": "Bob Johnson", "email": "bob@example.com", "address": {"street": "456 Oak Ave", "city": "Otherville"}}',
    '[{"product": "Keyboard", "quantity": 1, "price": 75}, {"product": "Monitor", "quantity": 1, "price": 300}]'
);
GO

استخراج داده‌های JSON

پس از ذخیره‌سازی داده‌های JSON، گام بعدی استخراج اطلاعات خاص از آنها است. این کار را می‌توان با استفاده از توابع JSON_VALUE، JSON_QUERY و OPENJSON انجام داد.

استفاده از JSON_VALUE

تابع JSON_VALUE برای استخراج مقادیر اسکالر (مانند رشته‌ها، اعداد، بولین‌ها) از یک شیء JSON استفاده می‌شود. این تابع یک مسیر JSON را به عنوان آرگومان دوم می‌پذیرد.

مثال: استخراج نام و شهر مشتری:

SELECT
    OrderID,
    JSON_VALUE(CustomerInfo, '$.name') AS CustomerName,
    JSON_VALUE(CustomerInfo, '$.address.city') AS CustomerCity
FROM SalesOrders;

اگر مسیر JSON به یک شیء یا آرایه اشاره کند، JSON_VALUE مقدار NULL را برمی‌گرداند. اینجاست که JSON_QUERY وارد عمل می‌شود.

استفاده از JSON_QUERY

تابع JSON_QUERY برای استخراج یک شیء یا آرایه JSON کامل از یک رشته JSON استفاده می‌شود. این تابع تضمین می‌کند که خروجی یک رشته JSON معتبر باشد.

مثال: استخراج اطلاعات کامل آدرس مشتری:

SELECT
    OrderID,
    JSON_QUERY(CustomerInfo, '$.address') AS CustomerAddressObject
FROM SalesOrders;

توجه داشته باشید که JSON_QUERY یک شیء JSON را برمی‌گرداند، در حالی که JSON_VALUE فقط مقادیر اسکالر را باز می‌گرداند.

استفاده از OPENJSON

تابع OPENJSON یک تابع جدولی است که به شما امکان می‌دهد یک رشته JSON را به سطرها و ستون‌ها تجزیه کنید و داده‌های JSON را به فرمت جدولی سنتی تبدیل نمایید. این تابع به ویژه برای کار با آرایه‌های JSON یا زمانی که نیاز به استخراج چندین ویژگی دارید مفید است.

مثال: استخراج جزئیات سفارش از آرایه OrderDetails:

SELECT
    so.OrderID,
    so.OrderDate,
    p.product,
    p.quantity,
    p.price
FROM SalesOrders AS so
CROSS APPLY OPENJSON(so.OrderDetails)
WITH (
    product NVARCHAR(100) '$.product',
    quantity INT '$.quantity',
    price DECIMAL(10, 2) '$.price'
) AS p;

در این پرس و جو، OPENJSON آرایه OrderDetails را به سطرها تجزیه می‌کند. بخش WITH طرح ستون‌های خروجی و مسیرهای JSON مربوطه را برای استخراج مقادیر تعریف می‌کند.

جستجو در آرایه‌های JSON تودرتو با OPENJSON

OPENJSON می‌تواند برای جستجو در ساختارهای JSON تودرتو نیز استفاده شود. فرض کنید داده‌های CustomerInfo شامل یک آرایه تودرتو برای شماره تلفن‌ها باشد.

ابتدا، جدول SalesOrders را با یک مشتری که دارای چندین شماره تلفن است، به‌روزرسانی می‌کنیم:

UPDATE SalesOrders
SET CustomerInfo = '{"name": "Alice Smith", "email": "alice@example.com", "address": {"street": "123 Main St", "city": "Anytown"}, "phones": [{"type": "mobile", "number": "555-1234"}, {"type": "home", "number": "555-5678"}]}'
WHERE OrderID = 1;

UPDATE SalesOrders
SET CustomerInfo = '{"name": "Bob Johnson", "email": "bob@example.com", "address": {"street": "456 Oak Ave", "city": "Otherville"}, "phones": [{"type": "work", "number": "555-9876"}]}'
WHERE OrderID = 2;

حالا، برای استخراج اطلاعات تماس و شماره تلفن‌ها، می‌توانیم از دو CROSS APPLY OPENJSON استفاده کنیم:

SELECT
    so.OrderID,
    JSON_VALUE(so.CustomerInfo, '$.name') AS CustomerName,
    phone.type AS PhoneType,
    phone.number AS PhoneNumber
FROM SalesOrders AS so
CROSS APPLY OPENJSON(so.CustomerInfo, '$.phones')
WITH (
    type NVARCHAR(50) '$.type',
    number NVARCHAR(50) '$.number'
) AS phone;

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

جمع‌بندی

SQL Server 2025 با قابلیت‌های پیشرفته JSON، ابزارهای قدرتمندی را برای مدیریت و استخراج داده‌های نیمه‌ساختاریافته فراهم می‌کند. توابع JSON_VALUE، JSON_QUERY و OPENJSON به شما امکان می‌دهند داده‌های JSON را به طور موثر دستکاری کنید، از استخراج مقادیر اسکالر گرفته تا تبدیل آرایه‌های پیچیده به فرمت‌های جدولی قابل مدیریت. با استفاده صحیح از این توابع، می‌توانید از انعطاف‌پذیری JSON بهره‌مند شوید در حالی که از قدرت و قابلیت‌های SQL Server برای ذخیره‌سازی، جستجو و تحلیل داده‌ها استفاده می‌کنید. این توانایی‌ها برای توسعه‌دهندگانی که با داده‌های مدرن و APIs کار می‌کنند، بسیار حیاتی هستند.