العمل مع كائنات JSON في SQL Server

الاستخدام الأكثر شيوعًا هو التكامل مع الخدمات الخارجية. لا يمكن استدعاء واجهات برمجة التطبيقات (APIs) مباشرة أو توليد بروتوكولات HTTP من SQL. بدلاً من ذلك، يتم عادةً تحقيق التكامل مع واجهات برمجة التطبيقات باستخدام تطبيقات أو خدمات أو أدوات تكامل خارجية تتواصل مع الواجهات ثم تعالج البيانات في قواعد بيانات SQL.

في حالتي، قمت بتطوير غلاف (wrapper) يعمل كجسر بين إجراء مخزّن (stored procedure) وواجهة برمجة تطبيقات. يسهّل هذا الغلاف التواصل السلس، ما يتيح للإجراء المخزّن التفاعل بفعالية مع الواجهة ودمج وظيفتها مع عمليات SQL.

تلقينا استجابة بتنسيق JSON.

هذا مثال على كائن JSON تلقيناه كاستجابة من واجهة برمجة التطبيقات.

{
  "header": {
    "statusCode": 200,
    "statusMessage": "Success",
    "timestamp": "2024-08-25T12:34:56Z"
  },
  "body": {
    "firstName": "Sead",
    "lastName": "NN",
    "accountNumber": "161000",
    "amount": 5000.00,
    "limit": 10000.00,
    "restrictions": [
      {
        "type": "Soft",
        "status": 0
      },
      {
        "type": "Hard",
        "status": 1
      }
    ],
    "authorizedPersons": [
      {
        "firstName": "Mirza",
        "lastName": "AWP",
        "accountNumber": "16590890"
      },
      {
        "firstName": "Faruk",
        "lastName": "TT",
        "accountNumber": "1638768"
      }
    ]
  }
}

من الممارسات الجيدة إدراج استجابة الواجهة في جدول مؤقت يمكن حذفه لاحقًا لقراءة الاستجابة.

CREATE TABLE #Api_response (
  id INT IDENTITY PRIMARY KEY,
  api_response NVARCHAR(MAX)
);

INSERT INTO #Api_responses (api_response)
VALUES ('{
  "header": {
    "statusCode": 200,
    "statusMessage": "Success",
    "timestamp": "2024-08-25T12:34:56Z"
  },
  "body": {
    "firstName": "Armin",
    "lastName": "PP",
    "accountNumber": "16123323",
    "amount": 5000.00,
    "limit": 10000.00,
    "restrictions": [
      {
        "type": "Soft",
        "status": 1
      },
      {
        "type": "Hard",
        "status": 0
      }
    ],
    "authorizedPersons": [
      {
        "firstName": "Mirza",
        "lastName": "LL",
        "accountNumber": "16134535"
      },
      {
        "firstName": "Sead",
        "lastName": "OO",
        "accountNumber": "161897979"
      }
    ]
  }
}');

يمكننا استخدام بعض الدوال للحصول على المعلومات التي نريدها.

من بين هذه الدوال: JSONQUERY وJSONVALUE وOPENJSON وJSON_MODIFY وFOR JSON وISJSON.

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

SELECT
  JSON_QUERY(api_response, '$.body') AS Body
FROM api_response;

SELECT
  JSON_QUERY(api_response, '$.body.restrictions') AS Restriction
FROM api_response;

الاستجابة:

[
  {
    "type": "Soft",
    "status": 1
  },
  {
    "type": "Hard",
    "status": 0
  }
]

SELECT
  JSON_QUERY(api_response, '$.body.limit') AS Limit
FROM api_response;

الاستجابة:

NULL

لاستخراج القيم البسيطة (scalar)، نستخدم دالة JSON_VALUE.

SELECT
  JSON_VALUE(api_response, '$.body.limit') AS Limit
FROM api_response;

الاستجابة:

1000.00

SELECT
  JSON_VALUE(api_response, '$.body.restrictions[0].type') AS Type
FROM api_response;

الاستجابة:

Soft

تحوّل OPENJSON مصفوفة JSON إلى تنسيق علائقي (relational) وتتيح العمل مع مصفوفات JSON.

SELECT
  AuthPerson.value('$.firstName', 'NVARCHAR(50)') AS first_Name,
  AuthPerson.value('$.lastName', 'NVARCHAR(50)') AS last_Name,
  AuthPerson.value('$.accountNumber', 'NVARCHAR(50)') AS account_Number
FROM Api_response
CROSS APPLY OPENJSON(api_response, '$.body.authorizedPersons') AS AuthPerson;

تتيح CROSS APPLY استخدام كل صف من Api_response كمدخل لـOPENJSON، ما يولّد صفوفًا متعددة لكل عنصر في مصفوفة JSON authorizedPersons. وهي تعيد فقط الصفوف من الجدول الرئيسي التي تُرجع الدالة نتائج لها.

أما OUTER APPLY فتعيد جميع صفوف الجدول الرئيسي، بما في ذلك تلك التي لا تُرجع الدالة نتائج لها.

يستخدم AuthPerson.value('$.firstName', 'NVARCHAR(50)') الاسم المستعار AuthPerson للوصول إلى كل عنصر في مصفوفة JSON واستخراج القيم من الكائنات داخل المصفوفة. في هذه الحالة، يستخرج firstName وlastName وaccount_Number لكل شخص.

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

تغيّر JSON_MODIFY القيم داخل كائن JSON.

UPDATE Api_response
SET api_response = JSON_MODIFY(api_response, '$.body.firstName', 'Alen')
WHERE id = 1;

على غرار تحديث الجداول، نحدد المعرّف (id) الذي نريد إجراء التغييرات عليه ونضبط القيمة باستخدام دالة JSON_MODIFY، حيث نوفر المسار إلى المعامل الذي نريد تحديثه.

تنسّق FOR JSON استعلامات SQL بتنسيق JSON.

SELECT
  id,
  api_response
FROM Api_response
FOR JSON AUTO, ROOT('ApiResponse');

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

سيقوم خادم SQL بتنسيق النتيجة في بنية JSON متداخلة بناءً على تسلسل الأعمدة الهرمي. في هذه الحالة، سيكون id وapi_response حقلين ضمن كل كائن JSON في المصفوفة الناتجة.

يقوم ROOT('ApiResponses') بتغليف نتيجة JSON بالكامل في عنصر جذر باسم ApiResponse. يساعد ذلك في توفير عنصر جذر واحد يشمل جميع كائنات JSON في مجموعة النتائج.

{
  "ApiResponse": [
    {
      "id": 1,
      "api_response": "{...}"
    }
  ]
}

إذا لم نرغب في استخدام ROOT، فسنحصل على استجابة دون عنصر الجذر المغلّف.

[
  {
    "id": 1,
    "api_response": "{...}"
  }
]

تتحقق ISJSON مما إذا كانت السلسلة النصية عبارة عن JSON صالح.

SELECT
  id,
  api_response,
  CASE
    WHEN ISJSON(api_response) = 1 THEN 'Valid JSON'
    ELSE 'Invalid JSON'
  END AS JSON_Validity
FROM Api_response;

يتحقق ISJSON(apiresponse) مما إذا كان محتوى عمود apiresponse عبارة عن JSON صالح. إذا كانت JSON صالحة، تُعيد الدالة القيمة 1؛ وإلا، تُعيد القيمة 0.

شكرًا لمتابعتكم هذا المقال. كالعادة، لا تترددوا في التواصل أو متابعتي لأي أسئلة أو تعليقات أو اقتراحات، سواء هنا على Medium أو عبر LinkedIn.