How to deal with JSON in SQL Server? – Part 1

As we might know, JSON (JavaScript Object Notation) is a simple and lightweight key-value pair message exchanging format. Its a very popular format and used in most modern services. SQL Server is also support to work with JSON format.

So, in this article , let us see how to deal with JSON data in SQL Server.

SQL Server provides the capabilities to provide built-in JSON support.

So, in this part, let us see how to extract JSON data and use them in queries.

The following built-in functions help us to achieve parse and manipulate the JSON Data

ISJSON

Checks whether the string contains data in a valid JSON format.

Basically, it returns in int data type. It returns either 0 or 1.

0 – False. Not a valid JSON format.

1 – True. Is a valid JSON format.

For an example,

DECLARE @JSONData AS NVARCHAR(4000)

SET @JSONData = N'{

    "Customer":{

        "FirstName":"Kumar",

        "LastName":"Thamilarasan",

        "Code":"ABC123",

        "Addresses":[

            { "Address":"XXXX", "City":"Oxford", "Country":"UK"},

            { "Address":"YYYY", "City":"London", "State":"UK"}

        ]

    }

}'

Select ISJSON(@JSONData) as 'Is valid JSON';

JSON_VALUE

It extracts the scalar value from JSON string.

JSON_VALUE(<json_string>, <path>);

First parameter is string in JSON format. Second parameter is a path and it expects in a specific format. For example,

• $ – Refers to a whole JSON object
• $.Property1 – Refers to a specific field in an object
• $.ArrayProperty[1] – Refers to a second element in the array property
• $.RootProperty.NestedProperty.ArrayProperty[2] – Refer to a nested property in a JSON object.

Based on the above example,

SELECT JSON_VALUE(@JSONData,'$.Customer.FirstName') as 'First Name';

To extract from an array

SELECT JSON_VALUE(@JSONData,'$.Customer.Addresses[0].Address') as 'Address';

If the path is not valid, it returns NULL.

SELECT JSON_VALUE(@JSONData,'$.Customer.Addresses[0].Invalid') as 'Address';

If we want to throw an error, if the path is not valid. Use strict keyword.

SELECT JSON_VALUE(@JSONData,' strict $.Customer.Addresses[0].Invalid') as 'Address';

JSON_QUERY

It extracts an object or array of objects from the JSON string.

JSON_QUERY ( expression [ , path ] )

• expression – Name of the variable or column that contains JSON text.
• path – Specify the path to the object or array to extract.

For example,

To return an array of objects

SELECT JSON_QUERY(@JSONData,'$.Customer.Addresses');

To return an object

SELECT JSON_QUERY(@JSONData,'$.Customer.Addresses[1]');

A note to consider,

If there is a duplicate property at the same level on the JSON data, both JSON_Value and JSON_Query always returns the first one.

For example

DECLARE @JSONData AS NVARCHAR(4000);

SET @JSONData = N'{

    "Customer":{

        "FirstName":"Kumar",

        "LastName":"Thamilarasan",

        "FirstName":"Kumaraguru",

        "Code":"CCEEDD

    }

}'

SELECT JSON_VALUE(@JSONData,'$.Customer.FirstName') as 'First Name';

Now, let us see how we can extract JSON string and manipulate it.

JSON_MODIFY

It returns an updated JSON string in NVARCHAR type.

JSON_MODIFY(<json_string>, <path>, <new_value>)

• <json_string> – Name of the variable or a table column.
• <path> – Path of field in a JSON string.
  • <new_value> – Insert/updates with a new value in the specified path.

Updates an existing value

Let us replace the first name of the customer.

SET @JSONData = JSON_MODIFY(@JSONData,'$.Customer.Addresses[0].Address', 'Updated XXXX');

Insert a new value

In the JSON string, if the path is exist, then it will update the attribute specified in the path. If the attribute is not exist, it will insert the new attribute at the end of the string.

In the example, We will insert the new field called MiddleName .

SET @JSONData = JSON_MODIFY(@JSONData,'$.Customer.MiddleName', 'G');

Append a value

In the JSON string, we can append to the existing field using append . In an array field, it will append an item to the end of array items.

SET @JSONData = JSON_MODIFY(@JSONData,'append $.Customer.Addresses', JSON_QUERY('{"Address":"ZZZZ", "City":"Birmingham", "Country":"UK"}','$'));

Update multiple fields

In the JSON string, we can also update multiple fields at once.

For example

Let us modify FirstName and LastName fields.

SET @JSONData = JSON_MODIFY(JSON_MODIFY(@JSONData,'$.Customer.FirstName', 'Cholan'),'$.Customer.LastName','Kumaran');

Delete existing field

In the JSON string, specify the field value to NULL to remove it.

For example

Let us remove the LastName field.

SET @JSONData = JSON_MODIFY(@JSONData,'$.Customer.LastName', NULL);

Rename a field

In the JSON string, rename a field.

For example, let rename the FirstName field to ForeName.

SET @JSONData = JSON_MODIFY(JSON_MODIFY(@JSONData,'$.Customer.ForeName',
   JSON_VALUE(@JSONData,'$.Customer.FirstName')),'$.Customer.FirstName', NULL);

Hope, it makes sense on how to extract and manipulate the JSON string.

Happy SQL`ing 🙂