Working with JSON Objects in SQL Server

JSON (JavaScript Object Notation) is developed due to the need for a simple, lightweight, and readable format for data exchange, primarily between web clients and servers.
Before JSON, XML was the dominant format for data exchange between systems, especially on the web. Although XML is powerful and flexible, it is often too complex and bulky for simple data exchange tasks, which can lead to increased network traffic and more complicated parsing code.

                  
<person>
  <name>Armin</name>
  <age>30</age>
  <isStudent>false</isStudent>
  <address>
      <street>Stupska br9</street>
      <city>Sarajevo</city>
  </address>
</person>
                  
                

                  
{
  "name": "Armin",
  "age": 30,
  "isStudent": false,
  "address": {
    "street": "Stupska br9",
    "city": "Sarajevo"
  }
}
                  
                

XML: Uses tags (<tag> and </tag>) to structure data.
JSON Uses key-value pairs in the form of objects { } and arrays [ ].

JSON is much simpler and more readable than XML, both for humans and computers.

JSON objects are more compact, which reduces the size of the data transmitted between the client and server.

When is JSON used in SQL?

The most common use is integration with external services. Directly calling APIs and generating HTTP protocols is not possible from SQL. Instead, integration with APIs is typically achieved using external applications, services, or integration tools that communicate with the APIs and then process the data in SQL databases. In my case, I have developed a wrapper that serves as a bridge between a stored procedure and an API. This wrapper facilitates seamless communication, enabling the stored procedure to interact effectively with the API and integrate its functionality with the SQL processes.

---

We received a response in JSON format; :))

This is an example of a JSON object we received as a response from the API.

              

{
  "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"
      }
    ]
  }
}

                

It is good practice to insert the API response into a temporary table that can be dropped later to read the response.

                  

  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"
      }
    ]
  }
}');


                

We can use some of the functions to get the information we want.

Some of them are:

JSON_VALUE

Extracts complex JSON structures, such as objects and arrays

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

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

Response (Return array): 

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

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

Response (Return Decimal value): 
NULL


                

For extracting scalar values, we use function JSON_VALUE

JSON_VALUE

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

Response: 
1000.00


                

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

Response: 
Soft


                

OPENJSON

Converts a JSON array into a relational format and allows for working with JSON arrays.

                  
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: Allows each row from Api_response to be used as input for OPENJSON, generating multiple rows for each element in the JSON array authorizedPersons. Returns only those rows from the main table for which the function returns results.

OUTER APPLY: Returns all rows from the main table, including those for which the function does not return results.

person.value('$.firstName', 'NVARCHAR(50)'): This value function uses the alias AuthPerson to access each element of the JSON array and extracts values from the objects in the array. In this case, it extracts first_Name, last_Name, and account_Number for each person.

Resonse:

Important!!
If you are using SQL Server 2016 or earlier, you won’t have access to OPENJSON and other JSON functions, and you might need to use alternative methods or consider upgrading to a newer version to leverage these features.

JSON_MODIFY

Changes values in a JSON object.

                  

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


                

Similar to updating tables, we specify the id for which we want to make changes and set the value using the JSON_MODIFY function, where we provide the path to the parameter we want to update.

FOR JSON

Formats SQL queries in JSON format.

                  

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


                

• JSON AUTO: This option tells SQL Server to automatically generate JSON format based on the structure of the query result. Each row of the result set is turned into a JSON object, and the overall result is a JSON array of these objects.




• SQL Server will format the result into a nested JSON structure based on the hierarchy of columns. In this case, id and api_response will be fields within each JSON object in the resulting array.




• ROOT('ApiResponses'): This option wraps the entire JSON result in a root element named ApiResponse. It helps to provide a single root element that encompasses all the JSON objects in the result set.

                  
{"ApiResponse":[{"id":1,"api_response":"{\n  \"header\": .......


                

If we don’t want to use ROOT, we will get a response

                  
[{"id":1,"api_response":"{\n  \"header\": ........


                

ISJSON

Checks whether a string is a valid 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(api_response): This function checks whether the content of the api_response column is a valid JSON. If the JSON is valid, the function returns 1; otherwise, it returns 0.

---

Thank you for following this post. As always, feel free to contact or follow me for questions, comments, or suggestions either here on Medium or via LinkedIn

•