# 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`
```sql
SELECT * FROM CapitalOfIndianStates
FOR JSON PATH, ROOT('StateCapitals')
```
{% hint style="info" %}
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.
{% endhint %}
### 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.
```sql
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 the`WITH`clause.
```sql
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.
{% embed url="" %}