معالجة بيانات JSON مع الوظائف المدمجة

12 دقائق

تخيل سيناريو حيث يخزن تطبيق التجارة الإلكترونية تفضيلات العملاء وبيانات الطلبات الوصفية كوثائق JSON. تطبيق الهاتف المحمول يرسل بيانات عربة التسوق بصيغة JSON، ويحتاج نظام التقارير الخاص بك إلى تصدير فهارس المنتجات ك JSON لواجهة برمجة تطبيقات الويب. العمل مباشرة مع JSON في قاعدة بياناتك يلغي الحاجة إلى تحويلات طبقة التطبيق ويحافظ على كفاءة معالجة البيانات.

توفر قواعد بيانات SQL Server وAzure SQL وSQL في Fabric دعما مدمجا لJSON يتيح لك تحليل بيانات JSON واستعلامها وإنشائها وتحويلها مباشرة في T-SQL. في هذه الوحدة، ستتعلم كيفية استخدام دوال JSON لاستخراج القيم، وبناء مخرجات JSON، وتجميع البيانات في مصفوفات JSON، والتحقق من محتوى JSON.

استخلاص القيم باستخدام JSON_VALUE و JSON_QUERY

عند العمل مع JSON المخزن في قاعدة بياناتك، تحتاج إلى استخراج قيم محددة للتصفية أو الانضمام أو العرض. يوفر SQL Server وظيفتين لهذا الغرض:

JSON_VALUE() يستخرج قيمة قياسية (سلسلة، رقم، بوليان) من سلسلة JSON:

DECLARE @json NVARCHAR(MAX) = N'{
    "customer": {
        "id": 12345,
        "name": "Contoso Ltd",
        "active": true
    },
    "orderTotal": 1599.99
}';

SELECT 
    JSON_VALUE(@json, '$.customer.id') AS CustomerID,
    JSON_VALUE(@json, '$.customer.name') AS CustomerName,
    JSON_VALUE(@json, '$.orderTotal') AS OrderTotal;

مجموعة النتائج ستكون:

CustomerID   CustomerName   OrderTotal
----------   ------------   ----------
12345        Contoso Ltd    1599.99

تتنقل الدالة في بنية JSON باستخدام تعبير المسار وتعيد القيمة كسلسلة NVARCHAR(4000) نصية. يمكنك تحويل النتيجة إلى أنواع بيانات أخرى حسب الحاجة للحسابات أو المقارنات.

JSON_QUERY() يستخرج كائن أو مصفوفة JSON (قيم غير عددية):

DECLARE @json NVARCHAR(MAX) = N'{
    "customer": {
        "id": 12345,
        "name": "Contoso Ltd"
    },
    "items": [
        {"product": "Widget", "qty": 5},
        {"product": "Gadget", "qty": 3}
    ]
}';

SELECT 
    JSON_QUERY(@json, '$.customer') AS CustomerObject,
    JSON_QUERY(@json, '$.items') AS ItemsArray;

مجموعة النتائج ستكون:

CustomerObject                          ItemsArray
--------------------------------------  ------------------------------------------------
{"id": 12345,"name": "Contoso Ltd"}     [{"product": "Widget", "qty": 5},{"product": "Gadget", "qty": 3}]

على عكس JSON_VALUE()، JSON_QUERY() يحافظ على بنية JSON، حيث يعيد الكائنات والمصفوفات كسلاسل JSON صالحة يمكنك تخزينها أو تمريرها إلى دوال أخرى أو إرجاعها إلى التطبيقات.

يستخدم $ تعبير المسار لتمثيل عنصر الجذر، مع تدوين النقاط للخصائص المتداخلة وترميز الأقواس لعناصر المصفوفة، كما في المثال التالي:

-- Access array elements by index (0-based)
SELECT JSON_VALUE(@json, '$.items[0].product') AS FirstProduct;

والنتيجة ستكون:

FirstProduct
------------
Widget

تبدأ مؤشرات المصفوفة من الصفر، أي $.items[0] تشير إلى العنصر الأول. استخدم هذا النحو لاستخراج عناصر محددة عندما تعرف الموقع، أو دمجه مع OPENJSON عندما تحتاج لمعالجة جميع عناصر المصفوفة.

نصيحة

استخدمها JSON_VALUE() عندما تحتاج إلى قيمة قياسية للمقارنات أو الحسابات. استخدمها JSON_QUERY() عندما تحتاج إلى الحفاظ على بنية JSON للكائنات أو المصفوفات المتداخلة.

تحليل مصفوفات JSON مع `OPENJSON`

OPENJSON هي دالة ذات قيم جدولية تحول بيانات JSON إلى مجموعة صفوف علائقية. استخدم هذه الوظيفة لربط بيانات JSON مع الجداول العلائقية أو عناصر مصفوفة العمليات بشكل فردي.

يقوم الاستعلام التالي بتحليل مصفوفة JSON إلى صفوف ذات مخطط افتراضي:

DECLARE @json NVARCHAR(MAX) = N'[
    {"id": 1, "name": "Widget", "price": 29.99},
    {"id": 2, "name": "Gadget", "price": 49.99},
    {"id": 3, "name": "Gizmo", "price": 19.99}
]';

SELECT * FROM OPENJSON(@json);

مجموعة النتائج ستكون:

key   value                                          type
---   --------------------------------------------   ----
0     {"id": 1, "name": "Widget", "price": 29.99}   5
1     {"id": 2, "name": "Gadget", "price": 49.99}   5
2     {"id": 3, "name": "Gizmo", "price": 19.99}    5

بدون مخطط، OPENJSON يعيد ثلاثة أعمدة: key (فهرس المصفوفة أو اسم الخاصية)، value (محتوى JSON)، و type (رقم يشير إلى نوع بيانات JSON: 0=null، 1=string، 2=number، 3=بولياني، 4=مصفوفة، 5=object).

يحدد الاستعلام التالي مخططا صريحا لاستخراج أعمدة محددة بأنواع بيانات مناسبة:

SELECT 
    ProductID,
    ProductName,
    Price
FROM OPENJSON(@json)
WITH (
    ProductID INT '$.id',
    ProductName NVARCHAR(100) '$.name',
    Price DECIMAL(10,2) '$.price'
);

مجموعة النتائج ستكون:

ProductID   ProductName   Price
---------   -----------   ------
1           Widget        29.99
2           Gadget        49.99
3           Gizmo         19.99

تقوم الجملة WITH بتعيين خصائص JSON إلى الأعمدة المصنفة. هذا النهج يمنحك أنواع بيانات مناسبة للحسابات والمقارنات، ويتيح لك اختيار الخصائص التي تحتاجها فقط.

اجمع OPENJSON مع بيانات الجدول باستخدام CROSS APPLY:

-- Assuming Orders table has a JSON column called OrderDetails
SELECT 
    o.OrderID,
    o.CustomerID,
    items.ProductName,
    items.Quantity,
    items.UnitPrice
FROM Orders AS o
CROSS APPLY OPENJSON(o.OrderDetails)
WITH (
    ProductName NVARCHAR(100) '$.product',
    Quantity INT '$.qty',
    UnitPrice DECIMAL(10,2) '$.price'
) AS items;

‏‫ملاحظة‬

عند استخدام OPENJSON مع CROSS APPLY، لا تظهر الصفوف من الجدول الرئيسي التي تحتوي NULL على قيم JSON فارغة في النتائج. استخدمها OUTER APPLY إذا كنت بحاجة لتضمين صفوف بدون بيانات JSON.

بناء JSON مع `JSON_OBJECT` و `JSON_ARRAY`

قدم JSON_OBJECT SQL Server 2022 وظائف JSON_ARRAY لبناء JSON بديهي:

JSON_OBJECT() ينشئ كائن JSON من أزواج مفتاح-قيمة، والمثال التالي يوضح كيفية بناء كائن JSON لمنتج:

SELECT JSON_OBJECT(
    'id': ProductID,
    'name': Name,
    'price': ListPrice,
    'available': CASE WHEN SellEndDate IS NULL THEN 'true' ELSE 'false' END
) AS ProductJson
FROM SalesLT.Product
WHERE ProductID = 680;

والنتيجة ستكون:

ProductJson
---------------------------------------------------------------------------
{"id":680,"name":"HL Road Frame - Black, 58","price":1431.50,"available":"true"}

تتعامل الدالة تلقائيا مع تحويل أنواع البيانات والهروب الصحيح من JSON لأحرف خاصة في قيم السلاسل النصية.

JSON_ARRAY() ينشئ مصفوفة JSON من القيم، والمثال التالي يبني مصفوفة JSON:

SELECT JSON_ARRAY(
    'SQL Server',
    'Azure SQL Database',
    'SQL Database in Fabric'
) AS Platforms;

والنتيجة ستكون:

Platforms
---------------------------------------------------------
["SQL Server","Azure SQL Database","SQL Database in Fabric"]

يمكنك تمرير قيم الأعمدة أو المتغيرات أو القيم الحرفية إلى JSON_ARRAY(). تنشئ الدالة مصفوفة JSON منسقة بشكل صحيح بغض النظر عن أنواع الإدخالات.

ثم، اجمع هذه الدوال لبناء هياكل JSON متداخلة. المثال التالي يبني كائن JSON للطلب الكامل مع معلومات العميل والإجماليات:

SELECT JSON_OBJECT(
    'orderId': soh.SalesOrderID,
    'orderDate': soh.OrderDate,
    'customer': JSON_OBJECT(
        'id': c.CustomerID,
        'name': c.CompanyName
    ),
    'totals': JSON_OBJECT(
        'subtotal': soh.SubTotal,
        'tax': soh.TaxAmt,
        'total': soh.TotalDue
    )
) AS OrderJson
FROM SalesLT.SalesOrderHeader AS soh
INNER JOIN SalesLT.Customer AS c
    ON soh.CustomerID = c.CustomerID
WHERE soh.SalesOrderID = 71774;

والنتيجة ستكون:

OrderJson
--------------------------------------------------------------------------------
{"orderId":71774,"orderDate":"2008-06-01","customer":{"id":29825,"name":"Contoso"},"totals":{"subtotal":880.35,"tax":70.43,"total":972.79}}

التعريش JSON_OBJECT في المكالمات يخلق هياكل هرمية تتناسب مع التنسيق المتوقع لتطبيقك. هذا النهج أنظف من دمج السلاسل ويضمن مخرجات JSON صالحة.

جمع البيانات مع `JSON_ARRAYAGG`

JSON_ARRAYAGG يجمع القيم من عدة صفوف في مصفوفة JSON واحدة. هذه الدالة مفيدة لإنشاء مخرجات JSON غير طبيعية من بيانات علائقية مطبعة:

SELECT 
    c.CustomerID,
    c.CompanyName,
    JSON_ARRAYAGG(soh.SalesOrderID) AS OrderIds
FROM SalesLT.Customer AS c
INNER JOIN SalesLT.SalesOrderHeader AS soh
    ON c.CustomerID = soh.CustomerID
GROUP BY c.CustomerID, c.CompanyName;

والنتيجة ستكون:

CustomerID   CompanyName           OrderIds
----------   -------------------   ------------------
29825        Contoso Retail        [71774,71776,71780]
29847        Adventure Works       [71782,71784]

تجمع الدالة جميع القيم المتطابقة من الصفوف المجمعة وتجمعها في مصفوفة JSON واحدة. هذا مفيد لإنشاء استجابات API غير طبيعية من جداول قواعد البيانات المصحبة.

يمكنك دمجها JSON_ARRAYAGG لإنشاء JSON_OBJECT مصفوفات من الكائنات المعقدة:

SELECT 
    pc.Name AS Category,
    JSON_ARRAYAGG(
        JSON_OBJECT(
            'id': p.ProductID,
            'name': p.Name,
            'price': p.ListPrice
        )
    ) AS Products
FROM SalesLT.ProductCategory AS pc
INNER JOIN SalesLT.Product AS p
    ON pc.ProductCategoryID = p.ProductCategoryID
GROUP BY pc.ProductCategoryID, pc.Name;

النتيجة التالية ستكون:

Category        Products
--------------  --------------------------------------------------------------------------
Road Bikes      [{"id":749,"name":"Road-150 Red, 62","price":3578.27},{"id":750,"name":"Road-150 Red, 44","price":3578.27}]
Mountain Bikes  [{"id":771,"name":"Mountain-100 Silver, 38","price":3399.99},{"id":772,"name":"Mountain-100 Black, 38","price":3374.99}]

مهم

JSON_ARRAYAGG والوظائف JSON_OBJECT/JSON_ARRAY متوفرة في SQL Server 2022 وما بعده، وقاعدة بيانات Azure SQL، وقواعد بيانات SQL في Microsoft Fabric. في الإصدارات السابقة، استخدم FOR JSON PATH ذلك لوظائف مشابهة.

تحقق من صحة وفحص JSON باستخدام `JSON_CONTAINS`

يمكن أن تكون بيانات JSON من مصادر خارجية مشوهة، أو تفتقد خصائص متوقعة، أو تحتوي على قيم غير متوقعة. محاولة استخراج القيم من JSON غير صالح أو المسارات المفقودة قد تسبب فشل في الاستعلامات أو إرجاع نتائج مضللة NULL تخفي مشاكل البيانات.

تتطلب معالجة JSON القوية ترميزا دفاعيا: تحقق من أن JSON متكون بشكل جيد قبل تحليله، وتحقق من وجود المسارات المتوقعة قبل استخراج القيم، وتحقق من أن القيم تطابق توقعاتك قبل استخدامها في منطق الأعمال. يوفر SQL Server عدة وظائف لمساعدتك في التحقق من صحة محتوى JSON في كل مرحلة من مراحل المعالجة.

افهم أوضاع المسار المرن مقابل أوضاع المسار الصارمة

يمكنك استخدام تعبيرات مسار JSON في وضعين يتحكمان في التعامل مع الأخطاء:

DECLARE @json NVARCHAR(MAX) = N'{"name": "Widget", "price": 29.99}';

-- Lax mode (default): Returns NULL for missing paths
SELECT JSON_VALUE(@json, 'lax $.description') AS LaxResult;

-- Strict mode: Raises an error for missing paths
SELECT JSON_VALUE(@json, 'strict $.description') AS StrictResult;

والنتيجة ستكون:

LaxResult
---------
NULL

-- Strict mode raises: Property cannot be found on the specified JSON path.

استخدم lax الوضع (الافتراضي) عندما يتوقع فقدان الخصائص ويجب أن يعود NULL. وضع الاستخدام strict عند فقدان الخصائص يشير إلى وجود مشكلة بيانات يجب أن تظهر خطأ.

ISJSON تتحقق مما إذا كانت السلسلة تحتوي على JSON صالح. يوضح المثال التالي كيفية استخدام ISJSON:

SELECT 
    ISJSON('{"name": "test"}') AS ValidJson,      -- Returns 1
    ISJSON('not valid json') AS InvalidJson,       -- Returns 0
    ISJSON(NULL) AS NullJson;                      -- Returns NULL

والنتيجة ستكون:

ValidJson   InvalidJson   NullJson
---------   -----------   --------
1           0             NULL

استخدم ISJSON في الجمل لتصفية WHERE الصفوف التي تحتوي على JSON صالح، أو في CASE التعبيرات للتعامل مع البيانات غير الصالحة بشكل أنيق.

JSON_PATH_EXISTS يتحقق مما إذا كان هناك مسار معين موجود في مستند JSON، مثل المثال التالي:

DECLARE @json NVARCHAR(MAX) = N'{"customer": {"name": "Contoso", "tier": "Gold"}}';

SELECT 
    JSON_PATH_EXISTS(@json, '$.customer.name') AS HasName,
    JSON_PATH_EXISTS(@json, '$.customer.email') AS HasEmail;

والنتيجة ستكون:

HasName   HasEmail
-------   --------
1         0

تعطي هذه الدالة 1 إذا كان المسار موجودا، و0 إذا لم يكن موجودا. استخدمه قبل الاستدعاء JSON_VALUE في وضع صارم، أو لمعالجة JSON بشكل مشروط مع هياكل مختلفة.

استخدم JSON_CONTAINS للتحقق مما إذا كان مستند JSON يحتوي على قيمة أو كائن معين، مثل المثال التالي:

DECLARE @json NVARCHAR(MAX) = N'{"tags": ["sql", "database", "azure"]}';

SELECT 
    JSON_CONTAINS(@json, '"sql"', '$.tags') AS HasSqlTag,
    JSON_CONTAINS(@json, '"python"', '$.tags') AS HasPythonTag;

والنتيجة ستكون:

HasSqlTag   HasPythonTag
---------   ------------
1           0

تحسين استعلامات JSON باستخدام الأعمدة المحسوبة

عندما تستفسر بشكل متكرر عن خصائص JSON محددة، يجب على محرك قاعدة البيانات تحليل مستند JSON لكل صف في كل استعلام. بالنسبة للجداول التي تحتوي على آلاف أو ملايين الصفوف، فإن هذا التحليل المتكرر يخلق عبئا كبيرا في السوق. الأعمدة المحسوبة تتيح لك استخراج قيم JSON مرة واحدة وتخزينها في صيغة قابلة للاستعلام تدعم الفهرسة.

لماذا يؤثر تحليل JSON على الأداء

تخيل جدولا يحتوي على 100,000 سجل منتج حيث يحتوي كل صف على مستند JSON يحتوي على سمات المنتج. يجب أن يكون تصفية الاستعلام حسب الفئة التالية:

اقرأ كل صف من الجدول
قم بتحليل وثيقة JSON للعثور على خاصية الفئة
استخلاص ومقارنة القيمة

بدون تحسين، حتى الفلاتر البسيطة تتطلب مسح جداول كاملة مع تحليل JSON في كل صف.

إنشاء أعمدة محسوبة لخصائص JSON

العمود المحسوب يستخرج تلقائيا خاصية JSON ويجعلها متاحة كعمود عادي، مثل المثال التالي:

-- Add a computed column that extracts a JSON property
ALTER TABLE Products
ADD ProductCategory AS JSON_VALUE(ProductData, '$.category');

-- The column is now available in queries
SELECT ProductID, ProductName, ProductCategory
FROM Products
WHERE ProductCategory = 'Electronics';

والنتيجة ستكون:

ProductID   ProductName           ProductCategory
---------   -------------------   ---------------
101         Wireless Mouse        Electronics
102         USB Keyboard          Electronics
103         HD Monitor            Electronics

افتراضيا، الأعمدة المحسوبة افتراضية. تحسب قاعدة البيانات القيمة عند وقت الاستعلام لكنها تستطيع تحسين استخراج JSON. للحصول على أداء أفضل، يمكنك الحفاظ على العمود المحسوب كما في المثال التالي:

-- Persisted computed column stores the extracted value physically
ALTER TABLE Products
ADD ProductCategory AS JSON_VALUE(ProductData, '$.category') PERSISTED;

تخزن الأعمدة المستخرجة القيمة المستخرجة على القرص، لذا يتم تحليل JSON فقط أثناء INSERT العمليات وليس UPDATE أثناء SELECT الاستعلامات.

إضافة الفهارس لتسريع التصفية

يأتي المكاسب الحقيقية في الأداء من فهرسة الأعمدة المحسوبة:

-- Create an index on the computed column
CREATE INDEX IX_Products_Category ON Products(ProductCategory);

-- Now this query uses an index seek instead of a table scan
SELECT ProductID, ProductName
FROM Products
WHERE ProductCategory = 'Electronics';

بدون الفهرس، يقوم الاستعلام بمسح جميع الصفوف ال 100,000. مع الفهرس، يقوم محرك الاستعلام بإجراء بحث للفهرس ويستعيد فقط الصفوف المتطابقة. يمكن أن يقلل هذا من وقت الاستعلام من ثوان إلى أجزاء من الثانية.

فهرسة خصائص JSON متعددة

بالنسبة للاستعلامات التي تصفية على خصائص JSON متعددة، أنشئ أعمدة محسوبة وفهرسا مركبا:

-- Extract multiple properties
ALTER TABLE Products
ADD ProductCategory AS JSON_VALUE(ProductData, '$.category') PERSISTED,
    ProductBrand AS JSON_VALUE(ProductData, '$.brand') PERSISTED,
    ProductPrice AS CAST(JSON_VALUE(ProductData, '$.price') AS DECIMAL(10,2)) PERSISTED;

-- Create a composite index for common query patterns
CREATE INDEX IX_Products_Category_Brand ON Products(ProductCategory, ProductBrand);

-- Create an index for price range queries
CREATE INDEX IX_Products_Price ON Products(ProductPrice);

الآن يمكن للاستفسارات التي تصفية حسب الفئة والعلامة التجارية، أو الفرز حسب السعر، استخدام هذه المؤشرات بكفاءة.

نصيحة

بالنسبة لخصائص JSON التي يتم الوصول إليها كثيرا، يمكن للأعمدة المحسوبة مع الفهارس تحسين أداء الاستعلام مقارنة بتحليل JSON في وقت الاستعلام. راقب أنماط الاستعلام الخاصة بك وأنشئ أعمدة محسوبة للخصائص المستخدمة في WHERE، JOIN، أو ORDER BY الجمل.

تحويل البيانات العلائقية إلى JSON باستخدام FOR JSON

للحصول على إخراج JSON شامل من الاستعلامات، استخدم FOR JSON PATH أو FOR JSON AUTO:

SELECT 
    p.ProductID,
    p.Name,
    p.ListPrice,
    pc.Name AS CategoryName
FROM SalesLT.Product AS p
INNER JOIN SalesLT.ProductCategory AS pc
    ON p.ProductCategoryID = pc.ProductCategoryID
WHERE p.ListPrice > 1000
FOR JSON PATH, ROOT('products');

والنتيجة ستكون:

{"products":[{"ProductID":749,"Name":"Road-150 Red, 62","ListPrice":3578.27,"CategoryName":"Road Bikes"},{"ProductID":750,"Name":"Road-150 Red, 44","ListPrice":3578.27,"CategoryName":"Road Bikes"}]}

FOR JSON PATH يمنحك التحكم في بنية JSON من خلال أسماء مستعارة للأعمدة. استخدم تدوين النقاط في الأسماء المستعارة لإنشاء كائنات متداخلة:

SELECT 
    p.ProductID AS 'product.id',
    p.Name AS 'product.name',
    pc.Name AS 'product.category'
FROM SalesLT.Product AS p
INNER JOIN SalesLT.ProductCategory AS pc
    ON p.ProductCategoryID = pc.ProductCategoryID
WHERE p.ProductID = 680
FOR JSON PATH;

والنتيجة ستكون:

[{"product":{"id":680,"name":"HL Road Frame - Black, 58","category":"Road Frames"}}]

ينشئ اسم 'product.id' مستعار العمود كائنا متداخلا product يحتوي على id خاصية. تتيح لك هذه التقنية تشكيل المخرجات ليتناسب مع التنسيق المتوقع من واجهة برمجة التطبيقات دون الحاجة إلى معالجة لاحقة.

لمزيد من المعلومات حول دوال JSON في SQL Server، راجع بيانات JSON في SQL Server وJSON Functions.

الملاحظات

هل كانت هذه الصفحة مفيدة؟

معالجة بيانات JSON مع الوظائف المدمجة

استخلاص القيم باستخدام JSON_VALUE و JSON_QUERY

تحليل مصفوفات JSON مع OPENJSON

بناء JSON مع JSON_OBJECT و JSON_ARRAY

جمع البيانات مع JSON_ARRAYAGG

تحقق من صحة وفحص JSON باستخدام JSON_CONTAINS

افهم أوضاع المسار المرن مقابل أوضاع المسار الصارمة

تحسين استعلامات JSON باستخدام الأعمدة المحسوبة

لماذا يؤثر تحليل JSON على الأداء

إنشاء أعمدة محسوبة لخصائص JSON

إضافة الفهارس لتسريع التصفية

فهرسة خصائص JSON متعددة

تحويل البيانات العلائقية إلى JSON باستخدام FOR JSON

الملاحظات

تحليل مصفوفات JSON مع `OPENJSON`

بناء JSON مع `JSON_OBJECT` و `JSON_ARRAY`

جمع البيانات مع `JSON_ARRAYAGG`

تحقق من صحة وفحص JSON باستخدام `JSON_CONTAINS`