Working with JSON in MS SQL Server – A Quick Guide for Developers

JSON (JavaScript object notation) is an unstructured format for storing and retrieving data within files and databases. It offers the flexibility of an unstructured data format like XML without the rigid schema binding required typically when using XML datatype. JSON is being increasingly adopted in a number of technology stacks for this exact reason. With the variety of data that we capture nowadays it is not always possible to create a well-defined normalized database structure to store this information. At least initially there is a need to work with the data in its unstructured format while enough examples are available so that standardization may occur. It is in this context that I have found myself using JSON. The requirement when it reaches us is too vague and doesn’t have enough details for us to design and normalize database. However, it is also not practical to wait until all the requirements are fleshed out to create a proof of concept for the project. In such cases JSON works as a decent compromise that can marry normalized database structure with the unstructured input making both available for querying. In this post we explore some basic examples of using JSON and querying it using TSQL code.

Check if a text is valid JSON

One of the first things we want to do when working with JSON strings is to check if it is valid. The below code shows how to TSQL function isJSON() to achieve this.

-- Use NVARCHAR for Unicode support and larger JSON strings
DECLARE @json NVARCHAR(MAX) = N'{"name":"Jayanth"}';

-- Use TRY_CAST for robust validation if needed (optional)
SELECT 
    ISJSON(@json) AS IsJson;

Notice I am using GitHub Copilot in this example, the goal here is to show you that working with JSON need not be difficult.

Extract the name from JSON String

The next thing we want to do most of the time is extract a simple element from the JSON. This would be like reading a column from within a single table. To achieve this we need to use the JSON_VALUE field as shown below.

-- Use NVARCHAR for Unicode support and larger JSON strings
DECLARE @json NVARCHAR(MAX) = N'{"name":"Jayanth"}';

-- Check if the JSON is valid using ISJSON
SELECT ISJSON(@json) AS IsJson;

-- Safely extract the 'name' value if JSON is valid
IF ISJSON(@json) = 1
BEGIN
    SELECT JSON_VALUE(@json, '$.name') AS Name;
END
ELSE
BEGIN
    RAISERROR('Invalid JSON format.', 16, 1);
END

Extract Multiple fields from JSON string

As an extension of the above we explore how to extract multiple fields from within the same JSON String as shown below, once again we simply use the JSON_VALUE function as needed to query each column individually.

DECLARE @json NVARCHAR(200) = N'{"name":"Jayanth", "age": 25, "city":"Bangalore"}';

IF ISJSON(@json) = 1
BEGIN
    SELECT
        JSON_VALUE(@json, '$.name') AS name,
        JSON_VALUE(@json, '$.age') AS age,
        JSON_VALUE(@json, '$.city') AS city;
END
ELSE
BEGIN
    RAISERROR('Invalid JSON format.', 16, 1);
END

Extracting data from nested object in JSON

Occasionally the data is stored in a slightly complex format such as a nested object within the JSON. A nested object can be thought of as a join between a PK table and the FK table where the contents of the FK table are stored in the nested object and repeated for each PK attribute. For example, in the below JSON file a single customer can have multiple Phone numbers or an address line can have multiple elements. Notice that the phone numbers are stored in an array using [ ] while the address is saved as a object using { }. An array can have more than one object.

DECLARE @json NVARCHAR(1000) = N'{
    "firstName": "John",
    "lastName": "Smith",
    "age": 25,
    "address": {
        "streetAddress": "21 2nd Street",
        "city": "New York",
        "state": "NY",
        "postalCode": "10021"
    },
    "phoneNumber": [
        {
            "type": "home",
            "number": "212 555-1234"
        },
        {
            "type": "fax",
            "number": "646 555-4567"
        }
    ]
}';

IF ISJSON(@json) = 1
BEGIN
    SELECT 
        JSON_VALUE(@json, '$.firstName') AS FirstName,
        JSON_VALUE(@json, '$.address.streetAddress') AS StreetAddress,
        JSON_VALUE(@json, '$.address.city') AS City,
        JSON_VALUE(@json, '$.address.state') AS State,
        JSON_VALUE(@json, '$.address.postalCode') AS PostalCode;
END
ELSE
BEGIN
    RAISERROR('Invalid JSON format.', 16, 1);
END

Extract data from Array within JSON object

After extracting address as shown above, we are faced with a slightly more difficult scenario of extracting objects from within the array. In the below example we explore how to extract an object when the position of the object within the array is known. This is done by passing the ordinal position of the object [0] (first object) to the JSON_VALUE function.

SELECT 
    JSON_VALUE(@json, '$.firstName') AS FirstName,
    JSON_VALUE(@json, '$.lastName') AS LastName,
    JSON_VALUE(@json, '$.age') AS Age,
    JSON_VALUE(
        (SELECT value 
         FROM OPENJSON(@json, '$.phoneNumber') 
         WHERE JSON_VALUE(value, '$.type') = 'home'
        ), 
        '$.number'
    ) AS HomePhoneNumber;

-- Use NVARCHAR for Unicode support and increase size for larger JSON
DECLARE @json NVARCHAR(MAX) = N'
{
    "firstName": "John",
    "lastName": "Smith",
    "age": 25,
    "address": {
        "streetAddress": "21 2nd Street",
        "city": "New York",
        "state": "NY",
        "postalCode": "10021"
    },
    "phoneNumber": [
        {
            "type": "home",
            "number": "212 555-1234"
        },
        {
            "type": "fax",
            "number": "646 555-4567"
        }
    ]
}';

-- Use OPENJSON to extract the home phone number dynamically
SELECT
    JSON_VALUE(@json, '$.firstName') AS FirstName,
    JSON_VALUE(@json, '$.lastName') AS LastName,
    JSON_VALUE(@json, '$.age') AS Age,
    pn.[number] AS HomePhoneNumber
FROM OPENJSON(@json, '$.phoneNumber')
     WITH (
         [type] NVARCHAR(20),
         [number] NVARCHAR(50)
     ) pn
WHERE pn.[type] = 'home';

Extract all the attributes within the array even when the number of attributes is uncertain

Sometimes it is not possible to know how many objects are present in a certain array and therefore we can’t hardcode the ordinal position as shown in the above query. In such cases we need to be a little more creative with the use of the JSON_QUERY function to extract the data we need out of the original JSON string before it can be queried. Notice how we extract the array using OPENJSON function.

-- Use NVARCHAR for Unicode support and larger JSON strings
DECLARE @json NVARCHAR(MAX) = N'{
    "firstName": "John",
    "lastName": "Smith",
    "age": 25,
    "address": {
        "streetAddress": "21 2nd Street",
        "city": "New York",
        "state": "NY",
        "postalCode": "10021"
    },
    "phoneNumber": [
        {
            "type": "home",
            "number": "212 555-1234"
        },
        {
            "type": "fax",
            "number": "646 555-4567"
        }
    ]
}';

-- Extract phone numbers of all types
SELECT 
    pn.[type],
    pn.[number]
FROM OPENJSON(@json, '$.phoneNumber') 
WITH (
    [type] NVARCHAR(20) '$.type',
    [number] NVARCHAR(20) '$.number'
) AS pn;

Some More examples

The unstructured nature of JSON allows us to store data in a number of different formats. In the below example we look at a different way to extract JSON from an Array.

-- Use proper casing and consistent naming conventions
CREATE TABLE #SampleJson (
    Zipcode VARCHAR(100),
    Products NVARCHAR(4000) -- Use NVARCHAR for JSON data
);

-- Use explicit column lists and VALUES for clarity
INSERT INTO #SampleJson (Zipcode, Products)
VALUES
    ('1', N'{"Available":[1,2,3,4,5],"UnAvailable":[6,7,8]}'),
    ('2', N'{"Available":[1,2],"UnAvailable":[10]}'),
    ('3', NULL);

-- Use aliases and check for NULLs before parsing JSON
SELECT 
    sj.Zipcode,
    sj.Products,
    aj.value AS AvailableProduct
FROM 
    #SampleJson sj
OUTER APPLY (
    SELECT value
    FROM OPENJSON(sj.Products, '$.Available')
) aj;

-- Clean up temp table
DROP TABLE #SampleJson;