JSON (JavaScript Object Notation) is a popular open file format for exchanging data between applications. It is widely used for data exchange and storage due to its simplicity and lightweight nature. In this article, we will explore the basics of JSON and how to work with JSON data in SQL Server using T-SQL.

Overview of JSON

JSON is a text-based data format that consists of key-value pairs. Each key represents a column in a SQL Server table, and the corresponding value represents the data for that column. JSON supports various data types such as strings, numbers, booleans, arrays, and objects.

Unlike XML, JSON does not require tags, making it a simpler and more lightweight data storage format. It is widely preferred by database professionals for representing and exchanging data.

Working with JSON in SQL Server

In SQL Server, you can use the openjson function to parse JSON formatted text into columns and rows for display within a SQL query. This function is available in SQL Server instances with a compatibility level of 130 or higher.

Here is an example of using the openjson function to display JSON formatted data:

DECLARE @json NVARCHAR(MAX) = N'{
   "String_value": "Json key name and type values",
   "Number_value": 12,
   "Boolean_value": true,
   "Null_value": null,
   "Array_value": ["r","m","t","g","a"],
   "Object_value": {"obj":"ect"}
}';

SELECT *
FROM OPENJSON(@json);

This query will display the JSON data as if it were a table in SQL Server, with each key-value pair represented as a column.

Customizing the Schema

You can also customize the schema when working with JSON data in SQL Server. By specifying a custom schema using the with clause, you can associate SQL Server data types with the JSON data types and define column names for each key-value pair.

Here is an example of using a custom schema with the openjson function:

DECLARE @json NVARCHAR(MAX) = N'{
   "String_value": "Json key name and type values",
   "Number_value": 12,
   "Boolean_value": true,
   "Null_value": null,
   "Array_value": ["r","m","t","g","a"],
   "Object_value": {"obj":"ect"}
}';

SELECT *
FROM OPENJSON(@json)
WITH (
   StringColumn NVARCHAR(50) '$.String_value',
   NumberColumn INT '$.Number_value',
   BooleanColumn BIT '$.Boolean_value',
   NullColumn NVARCHAR(50) '$.Null_value',
   ArrayColumn NVARCHAR(MAX) '$.Array_value' AS JSON,
   ObjectColumn NVARCHAR(MAX) '$.Object_value' AS JSON
);

In this query, we have specified custom column names and data types for each key-value pair in the JSON data. This allows us to display the JSON data in a more structured and meaningful way.

Manipulating JSON Data

In addition to displaying JSON data, you can also manipulate and store JSON data in SQL Server tables. You can use the openjson function to extract values from JSON and insert them into SQL Server tables.

Here is an example of inserting JSON data into a SQL Server table:

DECLARE @json NVARCHAR(MAX) = N'{
   "String_value": "Json key name and type values",
   "Number_value": 12,
   "Boolean_value": true,
   "Null_value": null,
   "Array_value": ["r","m","t","g","a"],
   "Object_value": {"obj":"ect"}
}';

INSERT INTO YourTable (StringColumn, NumberColumn, BooleanColumn, NullColumn, ArrayColumn, ObjectColumn)
SELECT StringColumn, NumberColumn, BooleanColumn, NullColumn, ArrayColumn, ObjectColumn
FROM OPENJSON(@json)
WITH (
   StringColumn NVARCHAR(50) '$.String_value',
   NumberColumn INT '$.Number_value',
   BooleanColumn BIT '$.Boolean_value',
   NullColumn NVARCHAR(50) '$.Null_value',
   ArrayColumn NVARCHAR(MAX) '$.Array_value' AS JSON,
   ObjectColumn NVARCHAR(MAX) '$.Object_value' AS JSON
);

This query will insert the values from the JSON data into the specified columns of the YourTable table.

Conclusion

JSON is a widely used data format for exchanging and storing data between applications. In SQL Server, you can use the openjson function to work with JSON data, parse it into columns and rows, and manipulate it as needed. By understanding the basics of JSON and its integration with SQL Server, you can effectively handle JSON data in your database applications.