Querying and Manipulating JSON data

S.No.	State	Capital	RTO Code
1	Telangana	Hyderabad	TG
2	Karnataka	Bengaluru	KA
3	Kerala	Thiruvananthapuram	KR
4	Madhya Pradesh	Bhopal	MP
5	Maharashtra	Mumbai	MH
6	Gujarat	Gandhi Nagar	GJ

Getting results of queries in JSON

Adding the below line at the end of the query will return the result in JSON format. FOR JSON PATH

We can also have a root element by adding ROOT('root_name') after FOR JSON PATH

SELECT * FROM CapitalOfIndianStates
FOR JSON PATH, ROOT('StateCapitals')

Using this SQL statement will help us directly returning JSON data at the database level without the need of serializing the data at the application level.

Reading JSON in tabular form

OPENJSON()

This function is used to parse the entire JSON object in tabular form. Let's understand with an example.

Consider we have a JSON object which we have saved into a variable.

DECLARE @Info NVARCHAR(MAX);
SET @Info = N'[
    {
        "Id": 1,
        "FirstName": "John", 
        "LastName": "Doe",
        "Gender": 'M',
        "Age": 22        
    },
    {
        "Id": 2,
        "FirstName": "Jane", 
        "LastName": "Doe",
        "Gender": 'F',
        "Age": 22        
    },
]';

To display these JSON object in tabular form (rows/columns) we pass this variable as an argument to OPENJSON(). After that, we need to tell the function what datatype will go with each property. We do this by specifying each column name with its corresponding data type inside theWITHclause.

SELECT * FROM OPENJSON(@Info)
WITH (
    Id INT 'strict S.Id'
    FirstName VARCHAR(50) '$.FirstName',
    LastName VARCHAR(50) '$.LastName',
    Gender CHAR(2) '$.Gender',
    Age INT '$.Age'
);

Note: strict keyword we used in our code is to speecify that Id must exist in the JSON object.

T-SQL JSON: Query and Manipulate JSON Data with T-SQL | Pluralsight